SQL Server 2008 R2：通过函数从变量查询xml节点

debugcn 发表于 Dev

阿卜杜勒·雷曼（Abdul Rehman）说

我有两个单独的具有相同子结构的xml变量。（即，只有根和第一个节点名称不同，其余结构相同。）我想创建一个函数，该函数采用xml变量和根节点的名称，并向我返回一个包含所有项目的表。

这些是我的xml定义（为简洁起见，简称）

declare @CartXml xml
Set @CartXml = ' <carts> 
<cart RefID="1" >

<Item SrNo="1" ProductCode="12" Qty="5">
</Item>

<Item SrNo="2" ProductCode="12" Qty="3">
</Item>
</cart>
</carts> '


declare @ProdXml xml
Set @ProdXml = ' <Products> 
<Product RefID="1" >

<Item SrNo="1" ProductCode="12" Qty="5">
</Item>

<Item SrNo="2" ProductCode="12" Qty="3">
</Item>
</Product>
</Products> '

我可以通过诸如以下语句来查询

 SELECT Hdr.x.value('@RefID', 'varchar(max)') CartID ,
       Det.*
 FROM   @CartXml.nodes('//carts/cart') AS Hdr(x)
 CROSS  APPLY 
          (
           SELECT 
            Det.c.value('@SrNo', 'varchar(max)') SrNo,
            Det.c.value('@ProductCode', 'varchar(max)') ProductCode,
            Det.c.value('@Qty', 'varchar(max)') Qty
            FROM   Hdr.x.nodes('Item') AS Det(c)
           ) Det

在上面的查询中，将// carts / cart替换为// Products / Product，并将@CartXml替换为@ProdXml，将得到相同的输出。

封装在函数中时，此查询不起作用。

Create function fnTempGetprodInfoFromXml( @xml xml, @rootnode varchar(50))
returns table 
as
Return (
 SELECT Hdr.x.value('@RefID', 'varchar(max)') CartID ,
       Det.*
 FROM   @xml.nodes('//*[local-name()=sql:variable("@rootnode")]') AS Hdr(x)
 CROSS  APPLY 
          (
           SELECT 
            Det.c.value('@SrNo', 'varchar(max)') SrNo,
            Det.c.value('@ProductCode', 'varchar(max)') ProductCode,
            Det.c.value('@Qty', 'varchar(max)') Qty
            FROM   Hdr.x.nodes('Item') AS Det(c)
           ) Det
)
Go


--These Dont Work
Select * from fnTempGetprodInfoFromXml(@cartxml,'carts/cart')
Select * from fnTempGetprodInfoFromXml(@ProdXml,'Products/Product')

我觉得将node变量传递给函数中的xml.nodes时存在一些问题。我希望我可以传递'// carts / cart'而不是'carts / cart'，但是函数不能编译。

这是功能所需的输出。

CartID  SrNo ProductCode    Qty
1       1     12            5
1       2     12            3

提前致谢。

加雷斯

您不能将参数传递给nodes函数，它必须是字符串文字。

您的选择是稍微修改您的函数以接受修剪后的XML：

CREATE FUNCTION fnTempGetprodInfoFromXml (@xml XML)
RETURNS TABLE
AS
RETURN
(   SELECT  Det.c.value('@SrNo', 'varchar(max)') AS SrNo,
            Det.c.value('@ProductCode', 'varchar(max)') AS ProductCode,
            Det.c.value('@Qty', 'varchar(max)') AS Qty
    FROM    @xml.nodes('*/*/Item') AS Det(c)

);

然后稍微改一下

DECLARE @Xml XML
SET @Xml = '<carts><cart RefID="1" ><Item SrNo="1" ProductCode="12" Qty="5">
            </Item><Item SrNo="2" ProductCode="12" Qty="3"></Item></cart></carts>';

 SELECT Hdr.x.value('@RefID', 'varchar(max)') CartID ,
        Det.*
 FROM   @Xml.nodes('//carts/cart') AS Hdr (x)
        CROSS APPLY fnTempGetprodInfoFromXml(x.query('*')) AS Det;

或者，如果您想更通用，则可以使函数使用通配符返回第二级的所有项目，例如

DECLARE @Xml XML
SET @Xml = '<carts><cart RefID="1" ><Item SrNo="1" ProductCode="12" Qty="5">
            </Item><Item SrNo="2" ProductCode="12" Qty="3"></Item></cart></carts>';

SELECT  Hdr.x.value('@RefID', 'varchar(max)') CartID ,
        Det.*
FROM    @xml.nodes('*/*') AS Hdr(x)
        CROSS APPLY
        (   SELECT  Det.c.value('@SrNo', 'varchar(max)') AS SrNo,
                    Det.c.value('@ProductCode', 'varchar(max)') AS ProductCode,
                    Det.c.value('@Qty', 'varchar(max)') AS Qty
            FROM    hdr.x.nodes('Item') AS Det(c)
        ) Det;

除此之外，您还必须使用动态SQL，这意味着您无法使用函数。

本文收集自互联网，转载请注明来源。

如有侵权，请联系[email protected] 删除。