How to read xml in t-sql?

Question

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>

marc_s · Accepted Answer · 2012-01-25 08:47:16Z

8

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

answered Jan 25, 2012 at 8:47

marc_s

760k186 gold badges1.4k silver badges1.5k bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

John Over a year ago

Cool That's what I've been looking for. I thought that somehow I will have to enumerate twice from the specific leaf but hasn't known I can use 'Item' from the first nodes. Thanks again!

Collectives™ on Stack Overflow

How to read xml in t-sql?

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related