[转载]SQL2005的XML学习笔记

1、记录到XML变量

declare @cxml xml

set @cxml=(select * from zd_storeP for XML RAW(‘store’),ROOT(‘stores’))

select @cxml

2、XML到记录集

方法一：用OPENXML  90000条记录速度测试,22s,16s,16s

declare @cxml xml,@nxml int

set @cxml=(select * from zd_storeP for XML RAW(‘store’),ROOT(‘stores’)) –大概1s

select @cxml

exec sp_xml_preparedocument @nxml OUTPUT, @cxml  –大概3-4s

select ls.* from openxml(@nxml,’/stores/store’) with (nID INT,cName VARCHAR(20)) as ls

方法二：用XML的nodes属性  90000条记录速度测试,8s,8s,8s

declare @cxml xml

set @cxml=(select * from zd_storeP for XML RAW,ROOT) –默认ROOT为root,RAW为row

select ls.row.value(‘@nID[1]’,’INT’) as nID,ls.row.value(‘@cName[1]’,’varchar(50)’) as cName

from @cxml.nodes(‘/root/row’) as ls(row)

3、SQL2005存储过程中,传入XML参数

方法一：对应上面的一

CREATE PROCEDURE [dbo].[testxml1]

@cxml xml

AS

BEGIN

SET NOCOUNT ON;

declare @nxml int

exec sp_xml_preparedocument @nxml OUTPUT, @cxml

select ls.* from openxml(@nxml,’/VFPData/row’) with (nid INT,cname VARCHAR(50)) as ls

END

方法二：对应上面的二

create PROCEDURE [dbo].[testxml2]

@cxml xml

AS

BEGIN

SET NOCOUNT ON;

select ls.row.value(‘@nid[1]’,’INT’) as nID,ls.row.value(‘@cname[1]’,’varchar(50)’) as cName

from @cxml.nodes(‘/VFPData/row’) as ls(row)

END

注意：如果XML是VFP的CursorToXML生成的，需要注意：

（1）with (nid INT,cname VARCHAR(50)) 括号中列名必须用小写,因为CursorToXML生成的XML格式字段强制了小写；

（2）VFP中,CursorToXML(“cursor1″,”lcCxml”,3,16) 最后的参数必须用16，表示采用游标的代码页，否则乱码了。

下面的例子，阐述了另一种语法，它能够在XML变量中直接立即更新表。

1 CREATE PROCEDURE [dbo].[UpdateInventory2]

2 (

3    @x XML

4 )

5 AS

6

7 SET NOCOUNT ON

8

9 /*

10    This version of the stored procedure has a slightly enhanced version of the

11    TSQL code. This version updates the table directly from the XML variable,

12    rather than converting the XML data to a view.

13 */

14

15 UPDATE Inventory SET

16    stock = stock + x.item.value(‘@Qty[1]’,’INT’)

17 FROM Inventory inv

18 INNER JOIN @x.nodes(‘//items/item’) x(item) ON

19    (x.item.value(‘@ItemNumber[1]’,’varchar(20)’) = inv.ItemNumber)

20

21 RETURN

4、SQL2005存储过程返回XML，在VFP中XMLToCursor()

— =============================================

— Author: <Author,,Name>

— Create date: <Create Date,,>

— Description: <测试返回XML变量，然后在VFP中还原游标>

— =============================================

alter PROCEDURE testxml3

@cxml varchar(max) output

AS

BEGIN

SET NOCOUNT ON;

declare @cxml2 xml

set @cxml2=(select * from zd_storeP for XML RAW(‘row’),ROOT(‘VFPData’))

set @cxml=cast(@cxml2 as varchar(max))

return

END

注意：output参数不能为xml类型，而要为varchar类型,否则VFP的XMLToCursor会报错的。