sql-server - 从 XML 元素解析多个值

Question

我在 SQL Server 2012 数据库表中有一个 XML 列，XML 中的一个特定元素存储多个值。我遇到的问题是我不知道这些子元素的最大值是多少。它可以是 2、3 或 10 等。

我需要一种方法来提取 XML 元素 SomeFieldName 中的多个值并在一行中返回它。下面是我的问题的简化示例:

IF OBJECT_ID('tempdb..#temptable') IS NOT NULL 
     DROP TABLE #temptable

CREATE TABLE #temptable ([CustomFieldsXML] xml)

INSERT INTO #temptable ([CustomFieldsXML])
VALUES
('<values>
    <SomeFieldName>
    <item>
      <Key>ph</Key>
      <Value>Philippines</Value>
    </item>
    <item>
      <Key>my</Key>
      <Value>Malaysia</Value>
    </item>
  </SomeFieldName>
</values>' )

SELECT
    c.CustomFieldsXML,
    c.CustomFieldsXML.value('(/values/SomeFieldName/item/Value)[1]', 'NVARCHAR(MAX)') AS 'Value1',
    c.CustomFieldsXML.value('(/values/SomeFieldName/item/Value)[2]', 'NVARCHAR(MAX)') AS 'Value2'
FROM 
    #temptable AS c

DROP TABLE #temptable;

您可以在我的示例中看到，我知道如何静态地将值一个一个地提取到不同的列中。有没有办法在不知道标签“SomeFieldName”中子元素的最大数量的情况下将这些值输出到一行。

我的例子只显示了两个，但我想知道如何处理未知数量的值并连接成一行，一列(以便它返回菲律宾，马来西亚)。 ....也许使用交叉应用？还是这更适合使用某种 XPATH？

Answer 1

使用nodes方法为每个item节点获取一行，然后可以获取value:

SELECT tt.CustomFieldsXML,
       SFN.i.value('(Key/text())[1]','nvarchar(2)') AS [Key], --Why use MAX here? Can the string really be more than 4,000 characters long?
       SFN.i.value('(Value/text())[1]','nvarchar(50)') AS [Value] --Don't use single quotes for aliases; it's a bad habit and can have unexpected behaviour
FROM #temptable AS tt
     CROSS APPLY tt.CustomFieldsXML.nodes('/values/SomeFieldName/item')SFN(i);