Database: SQL Query XML Datatype

It turns out to be possible to write views which query the XML datatype fields in SQL and returning multiple rows. The trick seems to be using the CROSS APPLY keyword. It seems to act like a join.

If this is your XML stored on a table named XMLTable in a field called XMLField.

<Services>

<Service>

<name>Mark</name>

<serviceID>45</ServiceID>

</Service>

<Service>

<name>Nancy</name>

<serviceID>46</ServiceID>

</Service>

<Services>

Here's the best SQL query I found for returning the multiple Service records.

SELECT
i.value('data((name)[1])', 'varchar(50)') as XMLName,

i.value('data((serviceID)[1])', 'varchar(50)') as XMLServiceID

FROM XMLTable

CROSS APPLY XMLField.nodes('/Services/Service') S(i)

The query should return two records, one for Mark and one for Nancy, from a single database record.

This site from Microsoft seemed to help the most on this newest learning adventure.