时间戳

首页 > 解决方案 > XML 节点中的 SQL 变量

sql-server - XML 节点中的 SQL 变量

问题描述

重新评估此处给出的示例： XML 节点中的 SQL 变量

鉴于：

DECLARE @x TABLE(item XML)
DECLARE  @schemaname VARCHAR(100)
SET @schemaname = 'Bla'

INSERT into @x
SELECT  '
<GaleriesSchem2>
    <Hello>
        <Bla>
            <Image_1 OriginalName="Image">12.jpg</Image_1>
            <Image_2 OriginalName="Image2">45.jpg</Image_2>
        </Bla>
    </Hello>
</GaleriesSchem2>
<GaleriesSchem3>
  <Image_1 OriginalName="Image">67.jpg</Image_1>
  <Image_2 OriginalName="Image2">89.jpg</Image_2>
</GaleriesSchem3>
'
SELECT rref.value('.', 'varchar(MAX)') AS 'Value'
FROM @x
  CROSS APPLY     
    item.nodes('GaleriesSchem2/Hello/*[local-name()=sql:variable("@schemaname")]/node()') AS Results(rref)

当变量 @schemaname 只是 'Bla' 时，Select 语句返回 12.jpg 和 45.jpg 但当变量 @schemaname 是 'GaleriesSchem2/Hello/Bla' 时，它什么也不返回。使用时

SET @schemaname = 'GaleriesSchem2/Hello/Bla'
SELECT rref.value('.', 'varchar(MAX)') AS 'Value'
FROM @x
  CROSS APPLY     
    item.nodes('*[local-name()=sql:variable("@schemaname")]/node()') AS Results(rref)

这里的冲突在哪里？如何使用变量访问 xml 节点？在这种情况下，变量是@schemaname = 'GaleriesSchem2/Hello/Bla'。

更新 为了在这种情况下完成工作，我尝试了两个建议的动态 sql，这可能是解决问题的一种方法。为此，我将@x 变量设置为真实表并将XML 片段放入其中。

DECLARE  @schemaname VARCHAR(100)
Declare @sql nvarchar(500)
Create Table x(item XML)
INSERT into x
SELECT  '
<GaleriesSchem2>
    <Hello>
        <Bla>
            <Image_1 OriginalName="Image">12.jpg</Image_1>
            <Image_2 OriginalName="Image2">45.jpg</Image_2>
        </Bla>
    </Hello>
</GaleriesSchem2>
<GaleriesSchem3>
  <Image_1 OriginalName="Image">67.jpg</Image_1>
  <Image_2 OriginalName="Image2">89.jpg</Image_2>
</GaleriesSchem3>
'
SET @schemaname = 'GaleriesSchem2/Hello/Bla/node()'
Set @sql = N'SELECT rref.value(' + '''.''' + ', ' + '''varchar(MAX)''' + ') AS Value FROM x CROSS APPLY item.nodes(''' + @schemaname + ''') AS Results(rref)';

Exec sp_executesql @sql

标签： sql-serverxml

解决方案

正如 Jeroen Mostert 在评论中告诉你的那样，这是不可能的。您可以结合使用local-name()来sql:variable()测试特定元素，但不能引入 XPath 变量。

您可以动态构建整个语句并使用EXEC()orsp_executesql()来执行，或者您可以使用递归 CTE 遍历 XML 并返回每个元素及其路径。

DECLARE @x TABLE(item XML)
DECLARE  @schemaname VARCHAR(100)
SET @schemaname = 'Bla'

INSERT into @x
SELECT  
'<GaleriesSchem2>
    <Hello>
        <Bla>
            <Image_1 OriginalName="Image">12.jpg</Image_1>
            <Image_2 OriginalName="Image2">45.jpg</Image_2>
        </Bla>
    </Hello>
</GaleriesSchem2>
<GaleriesSchem3>
  <Image_1 OriginalName="Image">67.jpg</Image_1>
  <Image_2 OriginalName="Image2">89.jpg</Image_2>
</GaleriesSchem3>';

WITH recCTE AS
(
    SELECT CONCAT('/',CAST(TopLevelNode.value('local-name(.)','nvarchar(max)') AS NVARCHAR(MAX)),'/') AS NodePath
          ,TopLevelNode.query('./*') AS SubNodes
          ,TopLevelNode.value('text()[1]','nvarchar(max)') AS Content
    FROM @x 
    CROSS APPLY item.nodes('*') A(TopLevelNode)

    UNION ALL

    SELECT CONCAT(r.NodePath,CAST(TheNextLevel.value('local-name(.)','nvarchar(max)') AS NVARCHAR(MAX)),'/')
          ,TheNextLevel.query('./*')
          ,TheNextLevel.value('text()[1]','nvarchar(max)') AS Content
    FROM recCTE r
    OUTER APPLY r.SubNodes.nodes('*') A(TheNextLevel)
    WHERE r.SubNodes.exist('*')=1
)
SELECT *
FROM recCTE 
WHERE Content IS NOT NULL;

结果

NodePath                            Content
/GaleriesSchem3/Image_1/            67.jpg
/GaleriesSchem3/Image_2/            89.jpg
/GaleriesSchem2/Hello/Bla/Image_1/  12.jpg
/GaleriesSchem2/Hello/Bla/Image_2/  45.jpg

针对这个集合，您可能会以简单的方式使用您的 XPath LIKE...

但是 - 老实说 - 这是非常有限的而且相当缓慢......

推荐阅读