********************* 导入 xml 文件 DECLARE @idoc int
DECLARE @doc varchar(1000)
--sample XML document
SET @doc =’
<root>
<Customer cid= "C1" name="Janine" city="Issaquah">
<Order oid="O1" date="1/20/1996" amount="3.5" />
<Order oid="O2" date="4/30/1997" amount="13.4">Customer was very satisfied
</Order>
</Customer>
<Customer cid="C2" name="Ursula" city="Oelde" >
<Order oid="O3" date="7/14/1999" amount="100" note="Wrap it blue
white red">
<Urgency>Important</Urgency>
Happy Customer.
</Order>
<Order oid="O4" date="1/20/1996" amount="10000"/>
</Customer>
</root>
’
-- Create an internal representation of the XML document.
EXEC sp_xml_preparedocument @idoc OUTPUT, @doc
DECLARE @doc varchar(1000)
--sample XML document
SET @doc =’
<root>
<Customer cid= "C1" name="Janine" city="Issaquah">
<Order oid="O1" date="1/20/1996" amount="3.5" />
<Order oid="O2" date="4/30/1997" amount="13.4">Customer was very satisfied
</Order>
</Customer>
<Customer cid="C2" name="Ursula" city="Oelde" >
<Order oid="O3" date="7/14/1999" amount="100" note="Wrap it blue
white red">
<Urgency>Important</Urgency>
Happy Customer.
</Order>
<Order oid="O4" date="1/20/1996" amount="10000"/>
</Customer>
</root>
’
-- Create an internal representation of the XML document.
EXEC sp_xml_preparedocument @idoc OUTPUT, @doc
DECLARE @doc varchar(1000)
--sample XML document
SET @doc =’
<root>
<Customer cid= "C1" name="Janine" city="Issaquah">
<Order oid="O1" date="1/20/1996" amount="3.5" />
<Order oid="O2" date="4/30/1997" amount="13.4">Customer was very satisfied
</Order>
</Customer>
<Customer cid="C2" name="Ursula" city="Oelde" >
<Order oid="O3" date="7/14/1999" amount="100" note="Wrap it blue
white red">
<Urgency>Important</Urgency>
Happy Customer.
</Order>
<Order oid="O4" date="1/20/1996" amount="10000"/>
</Customer>
</root>
’
-- Create an internal representation of the XML document.
EXEC sp_xml_preparedocument @idoc OUTPUT, @doc -- Execute a SELECT statement using OPENXML rowset provider.
SELECT *
FROM OPENXML (@idoc, ’/root/Customer/Order’, 1)
WITH (oid char(5),
amount float,
comment ntext ’text()’)
EXEC sp_xml_removedocument @idoc
execute('CREATE PROCEDURE [dbo].[u_xmlfile_preparedocument] As Begin Return End')
GOALTER PROCEDURE dbo.u_xmlfile_preparedocument @hdoc Integer Output,
@xmlfile varchar(1000) = NULL
AS
--
-- Description:
--
-- Utility procedure to invoke sp_xml_preparedocument from a file rather than text
--
-- Credits:
--
-- Joseph Gama (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsqlpro03/html/sp03g8.asp)
-- Umachandar Jayachandran (http://www.sqlxml.org/faqs.aspx?faq=42)
--
-- Example invocation:
--
-- exec dbo.u_xmlfile_preparedocument @hdoc OUTPUT, 'C:\test.xml'
--
-- Change History
--
-- Date Who Change
-- ----------- ---------------- -------------------------------------------------
-- 17-Oct-2003 Murray Crosswell CR841: Initial version
-- 24-Nov-2003 Murray Crosswell CR841: Move to another level of code generation abstraction to support larger files.
--
-- $Id: dbo.u_xmlfile_preparedocument.PRC,v 1.2 2003/10/22 00:12:38 mcrosswell Exp $
--
BeginSET NOCOUNT ON Declare @i int,
@size int,
@blocks int,
@varcnt int,
@qm char(1),
@bperv integer,
@cmdsrc varchar(8000),
@cmdsrc2 varchar(8000),
@cmdsrc3 varchar(8000),
@CRLF Varchar(2)Set @CRLF = Char(13) + Char(10)create table #filedetails
(altname varchar(30),size int,createdate varchar(32),
createtime varchar(32),lastwrittendt varchar(30),
lastwrittentime varchar(32),lastaccessdt varchar(30),
lastaccesstime varchar(32),attributes int)insert into #filedetails exec master..xp_getfiledetails @xmlfileset @size=(select size from #filedetails)drop table #filedetailsSet @bperv = 165
Set @qm = char(39)
Set @blocks=@size/4000+1
Set @varcnt=(@blocks-1)/@bperv+1Set @cmdsrc = 'Declare @cmdDS varchar(8000), '
Set @cmdsrc = @cmdsrc + '@cmdES varchar(8000), '
Set @i=1
While @i<=@varcnt
Begin
Set @cmdsrc = @cmdsrc + '@cmdD' + Convert(Varchar(9),@i) + ' Varchar(8000), '
Set @cmdsrc = @cmdsrc + '@cmdR' + Convert(Varchar(9),@i) + ' Varchar(8000), '
Set @cmdsrc = @cmdsrc + '@cmdF' + Convert(Varchar(9),@i) + ' Varchar(8000), '
Set @cmdsrc = @cmdsrc + '@cmdE' + Convert(Varchar(9),@i) + ' Varchar(8000), '
Set @i=@i+1
End
Set @cmdsrc = @cmdsrc + '@cmdDF varchar(8000), '
Set @cmdsrc = @cmdsrc + '@cmdEF varchar(8000), '
Set @cmdsrc = @cmdsrc + '@i int, @blocks int'Set @cmdsrc = @cmdsrc + ' SET @blocks=' + Convert(Varchar(9),@blocks)
Set @cmdsrc = @cmdsrc + ' SET @cmdDS='+@qm+'DECLARE @fso int, @fl int, @hr int, @qm char(1), @vp varchar(8000), @vs varchar(8000), '+@qmSet @cmdsrc = @cmdsrc + ' SET @cmdDF='+@qm+' Set @qm = char(39)'+@qm
Set @cmdsrc = @cmdsrc + ' SET @cmdDF=@cmdDF+'+@qm+' Set @vp = '+@qm+@qm+'DECLARE @Handle int EXEC sp_xml_preparedocument @Handle OUT, '+@qm+@qm+'+@qm'+@qm
Set @cmdsrc = @cmdsrc + ' SET @cmdDF=@cmdDF+'+@qm+' Set @vs = @qm+'+@qm+@qm+' DECLARE he_cur CURSOR GLOBAL FOR SELECT @Handle'+@qm+@qm+@qm
Set @cmdsrc = @cmdsrc + ' SET @cmdDF=@cmdDF+'+@qm+' EXEC @hr = sp_OACreate '+@qm+@qm+'Scripting.FileSystemObject'+@qm+@qm+', @fso OUT '+@qm
Set @cmdsrc = @cmdsrc + ' SET @cmdDF=@cmdDF+'+@qm+'exec sp_oamethod @fso, '+@qm+@qm+'opentextfile'+@qm+@qm+', @fl out, '+@qm+@qm+@xmlfile+@qm+@qm+', 1 '+@qm
Set @i=1
While @i<=@varcnt
Begin
Set @cmdsrc = @cmdsrc + ' SET @cmdD' + Convert(Varchar(9),@i) + '='+@qm+@qm
Set @cmdsrc = @cmdsrc + ' SET @cmdR' + Convert(Varchar(9),@i) + '='+@qm+@qm
Set @cmdsrc = @cmdsrc + ' SET @cmdF' + Convert(Varchar(9),@i) + '='+@qm+@qm
Set @cmdsrc = @cmdsrc + ' SET @cmdE' + Convert(Varchar(9),@i) + '='+@qm+@qm
Set @i=@i+1
End
Set @cmdsrc = @cmdsrc + ' SET @i=1'
Set @cmdsrc = @cmdsrc + ' WHILE @i<=@blocks'
Set @cmdsrc = @cmdsrc + ' BEGIN'
Set @cmdsrc2 = ''
Set @i=1
While @i<=@varcnt
Begin
If @i <> 1 Set @cmdsrc2 = @cmdsrc2 + ' ELSE'
If @i <> @varcnt Set @cmdsrc2 = @cmdsrc2 + ' IF @i < ' + Convert(Varchar(9),@i*@bperv+1)
If @varcnt > 1 Set @cmdsrc2 = @cmdsrc2 + ' BEGIN'
Set @cmdsrc2 = @cmdsrc2 + ' SET @cmdD' + Convert(Varchar(9),@i) + '=@cmdD' + Convert(Varchar(9),@i) + '+'+@qm+'@v'+@qm+'+CONVERT(VARCHAR(9),@i)+'+@qm+' varchar(8000), '+@qm
Set @cmdsrc2 = @cmdsrc2 + ' SET @cmdR' + Convert(Varchar(9),@i) + '=@cmdR' + Convert(Varchar(9),@i) + '+'+@qm+'exec @hr=sp_oamethod @fl,'+@qm+@qm+'read'+@qm+@qm+','+@qm+'+'+@qm+'@v'+@qm+'+CONVERT(VARCHAR(9),@i)+'+@qm+' out,4000 '+@qm
Set @cmdsrc2 = @cmdsrc2 + ' SET @cmdF' + Convert(Varchar(9),@i) + '=@cmdF' + Convert(Varchar(9),@i) + '+'+@qm+'Set @v'+@qm+'+CONVERT(VARCHAR(9),@i)+'+@qm+'=Replace(@v'+@qm+'+CONVERT(VARCHAR(9),@i)+'+@qm+',@qm,@qm+@qm) '+@qm
Set @cmdsrc2 = @cmdsrc2 + ' SET @cmdE' + Convert(Varchar(9),@i) + '=@cmdE' + Convert(Varchar(9),@i) + '+'+@qm+'@v'+@qm+'+CONVERT(VARCHAR(9),@i)+'+@qm+'+'+@qm
If @varcnt > 1 Set @cmdsrc2 = @cmdsrc2 + ' END'
Set @i=@i+1
End
Set @cmdsrc3 = ' SET @i=@i+1'
Set @cmdsrc3 = @cmdsrc3 + ' END'Set @cmdsrc3 = @cmdsrc3 + ' SET @cmdD' + Convert(Varchar(9),@varcnt) + '=LEFT(@cmdD' + Convert(Varchar(9),@varcnt) + ',len(@cmdD' + Convert(Varchar(9),@varcnt) + ')-1)'Set @cmdsrc3 = @cmdsrc3 + ' SET @cmdES='+@qm+'exec(@vp+'+@qm
Set @cmdsrc3 = @cmdsrc3 + ' SET @cmdEF='+@qm+'@vs) EXEC @hr = sp_OADestroy @fl EXEC @hr = sp_OADestroy @fso'+@qm
Set @cmdsrc3 = @cmdsrc3 + ' exec( @cmdDS+'
Set @i=1
While @i<=@varcnt
Begin
Set @cmdsrc3 = @cmdsrc3 + '@cmdD' + Convert(Varchar(9),@i) + '+'
Set @i=@i+1
End
Set @cmdsrc3 = @cmdsrc3 + '@cmdDF+'
Set @i=1
While @i<=@varcnt
Begin
Set @cmdsrc3 = @cmdsrc3 + '@cmdR' + Convert(Varchar(9),@i) + '+'
Set @i=@i+1
End
Set @i=1
While @i<=@varcnt
Begin
Set @cmdsrc3 = @cmdsrc3 + '@cmdF' + Convert(Varchar(9),@i) + '+'
Set @i=@i+1
End
Set @cmdsrc3 = @cmdsrc3 + '@cmdES+'
Set @i=1
While @i<=@varcnt
Begin
Set @cmdsrc3 = @cmdsrc3 + '@cmdE' + Convert(Varchar(9),@i) + '+'
Set @i=@i+1
End
Set @cmdsrc3 = @cmdsrc3 + '@cmdEF )'Execute (@cmdsrc+@cmdsrc2+@cmdsrc3)OPEN GLOBAL he_cur
FETCH he_cur INTO @hdoc
DEALLOCATE GLOBAL he_curIF @hdoc IS NULL
RAISERROR( 'Invalid Handle!', 16, 1 )End
create proc pmod
@xml text
as
DECLARE @idoc int
EXEC sp_xml_preparedocument @idoc OUTPUT, @xmlSELECT * FROM OPENXML (@idoc, '/ModiTable/Row/Col',1)
with(
emp varchar(10) '../@emp',
ele int '../@ele',
[index] varchar(10) '@index',
value decimal(20,2) '.'
)EXEC sp_xml_removedocument @idoc
go--调用存储过程
exec pmod '
<ModiTable>
<Row emp = "chjl" ele ="4">
<Col index="1">222.00</Col>
<Col index="2">3.00</Col>
<Col index="3">0.00</Col>
<Col index="4">0.00</Col>
<Col index="5">0.00</Col>
</Row>
<Row emp = "chzg" ele ="4">
<Col index="1">2.66</Col>
<Col index="2">234.00</Col>
<Col index="3">5.00</Col>
<Col index="4">6.00</Col>
<Col index="5">4.00</Col>
</Row>
</ModiTable>
'
go--删除测试
drop proc pmod/*--测试结果emp ele index value
---------- ----------- ---------- ----------------------
chjl 4 1 222.00
chjl 4 2 3.00
chjl 4 3 .00
chjl 4 4 .00
chjl 4 5 .00
chzg 4 1 2.66
chzg 4 2 234.00
chzg 4 3 5.00
chzg 4 4 6.00
chzg 4 5 4.00(所影响的行数为 10 行)--*/
执行时出错