Tsql – How to read xml in t-sql

sql-server-2008tsql

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

Here is the 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>

Best Answer

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)

This gives me an output of:

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

The approach is the following:

  • 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

  • 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

Related Topic