SQL2005的XML学习笔记(转)

lyfaraway笔记之 -------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会报错的。
posted @ 2011-02-25 11:09  董雨  阅读(182)  评论(0编辑  收藏  举报