假设您在 T-SQL 变量中有此 XML - 那么您可以使用以下代码片段:

Assuming you have this XML in a T-SQL variable - then you could use this snippet of code:

DECLARE @input XML = '...(your XML here).....'

SELECT
    Key1 = Item.value('(key)[1]', 'int'),
    Key2 = Item2.value('(key)[1]', 'int'),
    ItemValue = Item2.value('(value)[1]', 'varchar(50)')
FROM 
    @input.nodes('/root/item') AS T(Item)
CROSS APPLY
    item.nodes('value/params/item') AS T2(Item2)

这给了我一个输出:

Key1  Key2  ItemValue
 1     1     value
 1     2     value2
 1     3     value3
 2     4     value4
 2     5     value5
 2     6     value6  

方法如下:

• 获取 下的 节点列表作为带有第一个 的第一个XML 节点列表".nodes() XQuery 方法，并将该 XML 片段中 XML 元素的值提取到 Key1

• grab the list of <item> nodes under <root> as your first "list of XML nodes" with the first .nodes() XQuery method, and extract the value of the <key> XML element in that XML fragment into Key1

使用 value/params/item XPath 获取该 XML 片段内的 XML 节点的嵌套"列表，以获取子行 - 并从 中提取值<key> 和 从那些嵌套的子 XML 片段到 Key2 和 ItemValue

grab the "nested" list of XML nodes inside that XML fragment, using the value/params/item XPath, to get the child rows - and extract the values from <key> and <value> from those nested child XML fragments into Key2 and ItemValue