[转载]SQL2005的XML学习笔记

[转载]SQL2005的XML学习笔记 – flyfaraway – 博客园.

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会报错的。
赞(0) 打赏
分享到: 更多 (0)

觉得文章有用就打赏一下文章作者

支付宝扫一扫打赏

微信扫一扫打赏