ErikL ErikL - 5 months ago 21x
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:


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?


Try this:

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

In my case, this returns an output like:


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!