如何在 t-sql 中读取 xml? [英] How to read xml in t-sql?

问题描述

谁能告诉我如何从以下 xml 中选择值:我想获得如下行:Col1:键，Col2:键，Col3:值

Could anyone tell me how I could select values from the following xml: I would like to get rows as follows: Col1: key, Col2: key, Col3: value

1 1 value
1 2 value2
1 3 value3

2 4 value4
2 5 value5
2 6 value6

这里是xml:

<root>
  <item>
    <key>1</key>
    <value>
      <params>
        <item>
          <key>1</key>
          <value>value</value>      
        </item>
        <item>
          <key>2</key>
          <value>value2</value>     
        </item>     
        <item>
          <key>3</key>
          <value>value3</value>     
        </item>     
      </params>
    </value>
  </item>
  <item>
    <key>2</key>
    <value>
      <params>
        <item>
          <key>4</key>
          <value>value4</value>     
        </item>
        <item>
          <key>5</key>
          <value>value5</value>     
        </item>     
        <item>
          <key>6</key>
          <value>value6</value>     
        </item>     
      </params>
    </value>
  </item>  
</root>

推荐答案

假设您在 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

这篇关于如何在 t-sql 中读取 xml?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！