Using SQL xQuery to Select Multiple Node Values

A recent task involved us having to grab information out of an rdl whose dataset was saved as xml in a sql table (Yes, you can shudder at that statement, it's ok, I know I did). This was my first foray into xQuery in a sproc, so it was a pretty interesting experience.

There was existing code for dealing with a single value (handily, as a 'value' command), but not much on selecting multiple rows. What we wound up doing was a select from the table using a cross apply to grab the nodes we cared about, then performing a value on the returned data. Like this:

SELECT temp.dataStuff.value('.','varchar(50)')
  FROM XmlTable xt
CROSS APPLY xt.xml.nodes('(/root/nodes/data)') as temp(dataStuff)

If you do a select, you will see the nodes we return are untyped sql; the value call just says 'grab the nodes we returned and shove them into varchars'.

So, with an XmlTable entry with an xml column of this:

<root>
<nodes>
<data>ABC</data>
</nodes>
<nodes>
<data>DEF</data>
</nodes>
<nodes>
<data>GHI</data>
</nodes>
</root>

You would return this out of SQL:

ABC
DEF
GHI


Another helpful source for this is here: http://stackoverflow.com/questions/1393250/getting-multiple-records-from-xml-column-with-value-in-sql-server

In the linked example the cross apply wasn't necessary because they started with an xml string. Since we had to join in and grab multiples from different rows, we needed it here.