XML节点中的SQL变量包含路径

Question

我需要有关通过变量将XML路径传递给nodes（）方法的帮助。我看了几篇不同的文章，发现可以使用local- name和传递节点sql:variable。下面的示例按预期工作：

DECLARE @XML_Path VARCHAR(MAX) 
      , @XML_In XML


SET @XML_Path = 'GetData'

SET @XML_In = 
'
<GetData>
  <test>234</test>
</GetData>
'

--THIS WORKS
SELECT Item_Idx = Nodes.value('(test)[1]' ,'INT')
FROM   @XML_In.nodes('/*[local-name()=sql:variable("@XML_Path")]') Results(Nodes)

但是，当我尝试传递路径而不是单个节点时，它不起作用：

SET @XML_Path = 'GetData/Hello'

SET @XML_In = 
'
<GetData>
  <Hello>
    <test>234</test>
  </Hello>
</GetData>
'

--THIS DOES NOT WORK
SELECT Item_Idx = Nodes.value('(test)[1]' ,'INT')
FROM   @XML_In.nodes('/*[local-name()=sql:variable("@XML_Path")]') Results(Nodes)

我需要能够在始终为常数的路径的另一部分之前传递路径。因此，在我的示例中，“测试”部分将始终相同，但其上方的路径将有所不同。

我怀疑这local-name不是这样做的方法。但是，有其他方法可以做到这一点吗？

任何帮助，将不胜感激。

我曾考虑过使用动态SQL，但是此代码将在返回结果表的UDF中使用。

编辑：

我相信我不清楚自己要做什么。我需要能够将不同的XML路径传递到使用它们来切碎XML的函数中。

例子1

DECLARE @XML_In XML

SELECT @XML_In = 
'
<RootNode1>
  <ExtraNode1>
    <Items>
      <ExampleItem>
        <SomeNode>100</SomeNode>
        <Attributes>
          <ID>1</ID>
          <Name>a</Name>
          <Value>123a</Value>
        </Attributes>
      </ExampleItem>
      <ExampleItem>
        <SomeNode>200</SomeNode>
        <Attributes>
          <ID>2</ID>
          <Name>b</Name>
          <Value>234</Value>
        </Attributes>
      </ExampleItem>
      <ExampleItem>
        <SomeNode>300</SomeNode>
        <Attributes>
          <ID>3</ID>
          <Name>c</Name>
          <Value>345</Value>
        </Attributes>
      </ExampleItem>
    </Items>
  </ExtraNode1>
</RootNode1>
'

    SELECT      SomeNode  = Nodes.value('(../SomeNode)[1]' ,'INT')  
              , ID        = Nodes.value('(ID)[1]'       ,'INT')         
              , Name      = Nodes.value('(Name)[1]'     ,'VARCHAR(100)')     
              , Value     = Nodes.value('(Value)[1]'    ,'NVARCHAR(MAX)')
    FROM        @XML_In.nodes('/RootNode1/ExtraNode1/Items/ExampleItem/Attributes') Results(Nodes)

例子2

DECLARE @XML_In XML

SELECT @XML_In = 
'
<Example2Root>
  <Entries>
    <Entry>
      <SomeNode>100</SomeNode>
      <Fields>
        <ID>1</ID>
        <Name>a</Name>
        <Value>123a</Value>
      </Fields>
    </Entry>
    <Entry>
      <SomeNode>200</SomeNode>
      <Fields>
        <ID>2</ID>
        <Name>b</Name>
        <Value>234</Value>
      </Fields>
    </Entry>
    <Entry>
      <SomeNode>300</SomeNode>
      <Fields>
        <ID>3</ID>
        <Name>c</Name>
        <Value>345</Value>
      </Fields>
    </Entry>
  </Entries>
</Example2Root>
'

    SELECT      SomeNode  = Nodes.value('(../SomeNode)[1]' ,'INT')  
              , ID        = Nodes.value('(ID)[1]'       ,'INT')         
              , Name      = Nodes.value('(Name)[1]'     ,'VARCHAR(100)')     
              , Value     = Nodes.value('(Value)[1]'    ,'NVARCHAR(MAX)')
    FROM        @XML_In.nodes('/Example2Root/Entries/Entry/Fields') Results(Nodes)

我希望能够将路径传递给函数，以便它可以执行此操作并将其作为表返回：

SELECT      SomeNode  = Nodes.value('(../SomeNode)[1]' ,'INT')  
          , ID        = Nodes.value('(ID)[1]'       ,'INT')         
          , Name      = Nodes.value('(Name)[1]'     ,'VARCHAR(100)')     
          , Value     = Nodes.value('(Value)[1]'    ,'NVARCHAR(MAX)')
FROM        @XML_In.nodes('SOME_PATH') Results(Nodes)

但是我不知道如何使用参数作为路径。大多数情况下，路径会有所不同。

Answer 1

您不想更改整个功能，而只需更改XQuery中的路径。如您所知，这是不可能的。

但是：您似乎在不同的结构中找到相同的数据。至少你似乎知道，你会发现一个名为节点ID，Name以及Value节点下面SomeNode…

因此，这种方法可能以完全不同的方式解决了您的问题…至少在您给出的两个示例中，它都可以起作用…

SELECT p.*
FROM
(
    SELECT Nd.value('.','int') AS SomeNode
          ,Deeper.value('local-name(.)','nvarchar(max)') AS NodeName
          ,Deeper.value('.','nvarchar(max)') AS NodeValue
    FROM @XML_In.nodes('//SomeNode') AS Sm(Nd)
    OUTER APPLY Nd.nodes('parent::*/*[local-name(.)!="SomeNode"]/*') AS TwoLevels(Deeper)
) AS tbl
PIVOT
(
    MIN(NodeValue) FOR NodeName IN(ID,Name,Value)
) AS p

在此解决方案中，您的第一个工作示例显示了如何SomeNode用作变量…