alter procedure sp_ParseXML(@xmlDocument nvarchar(1000)) as
declare @dochandle int
exec sp_xml_preparedocument @dochandle output,
'<root>
<Category CategoryID="1" CategoryName="General Books" >
<Product ProductID="10248" Description="Cooking for you"></Product>
</Category>
<Category CategoryID="2" CategoryName="Videos" >
<Product ProductID="80248" Description="7 minutes Abs"></Product>
</Category>
<Category CategoryID="3" CategoryName="Computer Books" >
<Product ProductID="12345" Description="Inside SQL Server 2000"></Product>
<Product ProductID="22345" Description="Analysis Services with SQL Server 2000"></Product>
</Category>
</root>'
--@xmlDocument
select *
from openxml(@dochandle,N'/root/Category/Product')
with (ProductID int ,
CategoryID int N'../@CategoryID',
CategoryName varchar(50) '../@CategoryName',
[Description] varchar(50))
exec sp_xml_removedocument @dochandleXML是区分大小写的!!!
declare @dochandle int
exec sp_xml_preparedocument @dochandle output,
'<root>
<Category CategoryID="1" CategoryName="General Books" >
<Product ProductID="10248" Description="Cooking for you"></Product>
</Category>
<Category CategoryID="2" CategoryName="Videos" >
<Product ProductID="80248" Description="7 minutes Abs"></Product>
</Category>
<Category CategoryID="3" CategoryName="Computer Books" >
<Product ProductID="12345" Description="Inside SQL Server 2000"></Product>
<Product ProductID="22345" Description="Analysis Services with SQL Server 2000"></Product>
</Category>
</root>'
--@xmlDocument
select *
from openxml(@dochandle,N'/root/Category/Product')
with (ProductID int ,
CategoryID int N'../@CategoryID',
CategoryName varchar(50) '../@CategoryName',
[Description] varchar(50))
exec sp_xml_removedocument @dochandleXML是区分大小写的!!!
declare @dochandle int
exec sp_xml_preparedocument @dochandle output,@xmlDocument
select *
from openxml(@dochandle,N'/root/Category/Product')
with (ProductID int ,
CategoryID int N'../@CategoryID',
CategoryName varchar(50) '../@CategoryName',
[Description] varchar(50))
exec sp_xml_removedocument @dochandledeclare @doc varchar(1000)
set @doc=N'<root>
<Category CategoryID="1" CategoryName="General Books" >
<Product ProductID="10248" Description="Cooking for you"></Product>
</Category>
<Category CategoryID="2" CategoryName="Videos" >
<Product ProductID="80248" Description="7 minutes Abs"></Product>
</Category>
<Category CategoryID="3" CategoryName="Computer Books" >
<Product ProductID="12345" Description="Inside SQL Server 2000"></Product>
<Product ProductID="22345" Description="Analysis Services with SQL Server 2000"></Product>
</Category>
</root>'
exec sp_parsexml @doc
只是不知N'/root/Category/Product'前面的N是作什么用的?好象不用也可以。
是为了支持UNICODE字符