ErikL ErikL - 6 months ago 25
SQL Question

Parse XML in T-SQL

I know that there are already numerous topics about querying xml in MS T-SQL, however with all the samples, I wasn't able to get my query to work properly.

I have the following XML:

<group>
<items>
<groupitem>
<key>23137</key>
</groupitem>
<groupitem>
<key>23139</key>
</groupitem>
<groupitem>
<key>23151</key>
</groupitem>
<groupitem>
<key>23153</key>
</groupitem>
</items>
</group>


I want to get all the 'key' items, so that I can insert them into a table (so 4 rows)

I started off putting my xml into variable @xml and running this query:

SELECT doc.value('(key/text())[1]', 'nvarchar(255)') AS 'key'
FROM @xml.nodes('/group/items/groupitem/*') AS ref(doc)


That gave me 4 empty rows, and if I remove the [1], it gives me this error: "XQuery [value()]: '*value()' requires a singleton (or empty sequence), found operand of type 'xdt:untypedAtomic '"

Then I tried this:

SELECT doc.value('(/group/items/groupitem/key)[1]', 'nvarchar(255)') AS 'key'
FROM @xml.nodes('/group/items/groupitem/*') AS ref(doc)


That actually gave me some data, but unfortunately, it's 4 times the same key 23137, probably caused by the [1] in the statement. Removing it however brings me back to the same error message as before.

I know how I should do it in Xpath (/group/items/groupitem/key), but can't get my head around how I should do it in T-Sql. Any ideas?

Answer

Try this:

SELECT 
    doc.value('(key)[1]', 'int') AS 'key'
FROM 
    @xml.nodes('/group/items/groupitem') AS ref(doc)

In my case, this returns an output like:

key
-----
23137
23139
23151
23153

Is that what you're looking for?

The call to .nodes() basically gives you a "pseudo" table of XML fragments - one for each match of the XPath expression. So in your case, you get four rows of XML back, each representing the contents of the <groupitem> node. You reach into that, grab the value of the <key> element contained inside, cast it to an int - and presto, you're done!

Comments