XML 节点中包含 SQL 变量的路径 [英] SQL Variable Containing Path in XML Node

问题描述

我需要关于通过变量将 XML 路径传递给 nodes() 方法的帮助.我查看了几个不同的帖子，发现可以使用 local-name 和 sql:variable 传递节点.下面的示例按预期工作:

I need help with passing an XML path through a variable to the nodes() method. I have looked at several different posts and found that a node can be passed by using local-name and sql:variable. The example below works as expected:

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)

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

However, it does not work when I try to pass a path instead of a single node:

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)

我需要能够在路径的另一部分之前传递始终不变的路径.因此，在我的示例中，测试"部分将始终相同，但其上方的路径会有所不同.

I need to be able to pass a path preceding another part of the path that is always constant. So, in my example, the "test" part would always be the same but the path above it would vary.

我怀疑 local-name 不是这样做的方法.但是有没有其他方法可以做到这一点?

I suspect that local-name is not the way to do this. But is there a different way I can do this?

任何帮助将不胜感激.

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

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

I believe I was unclear about what I am trying to do. I need to be able to pass different XML paths into a function that uses them to shred 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)

<小时>

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

---

I want to be able to pass the path to the function so it can do this and the return it as a table:

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)

但我不知道如何使用路径参数.大多数时候路径会有所不同.

But I don't know how to go about using a parameter for the path. The path will be different most times.

推荐答案

您不想更改整个函数，只需更改 XQuery 中的路径.这是 - 如你所知 - 不可能.

You do not want to change the whole function, just the path in XQuery. This is - as you know - not possible.

但是:您似乎在不同的结构中找到相同的数据.至少您似乎知道，您会在节点 SomeNode 下方找到名为 ID、Name 和 Value 的节点...

But: It seems that you are finding the same data in differing structures. At least you seem to know, that you will find nodes named ID, Name and Value below a node SomeNode...

所以这种方法可能会以完全不同的方式解决您的问题...它有效 - 至少 - 与您给出的两个示例...

So this approach might solve your issue in a completely different way... It works - at least - with your two given examples...

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 作为变量...

In this solution your first working example shows, how you might use SomeNode as variable...

这篇关于XML 节点中包含 SQL 变量的路径的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！