Querying XML in a Tabular Format

So, SQL Server 2005 provides some remarkable access to XML documents stored within XML column types of a table. The most interesting that I've found so far is that you can take the XML document stored in a column and run a query against that that will return it in tabular format. You can then use that and join it to other tables in order to make your updates.

To do this, you need to figure out the XQuery to get a rowset of nodes that you want to query against. Pretending that we have XML document:


<Projects>
<Project>
<Text ID="116" TypeID="2" Request="DoNotChange" Response="">
<en_us>Alpha</en_us>
</Text>
<Text ID="117" TypeID="2" Request="DoNotChange" Response="">
<en_us>Beta</en_us>
</Text>
</Projects>


A possible XQuery to get all the Text nodes might be '/Projects/Project/Text'. You then need to cross your original SQL query that will get the xml document in order to get the list of all Text. So:


SELECT * FROM XmlImport (NOLOCK) CROSS APPLY ImportDocument.nodes('/Project/Project/Text') as tab(col) WHERE XmlImportId = 1


I've bolded the applicable part of this query. You are applying the results of this query to your result set as column 'col' - fully delineated I believed its tab.col.

The above SQL statement will not work, however. If you run it you will get an error 'The column 'col' that was returned from the nodes() method cannot be used directly. It can only be used with one of the four xml data type methods, exist(), nodes(), query(), and value(), or in IS NULL and IS NOT NULL checks.' This because SQL needs to know what you want it to return; it has no way of representing xml data in a tabular format otherwise. In order to do that, you need to include XQueries in your select statement to declare what you want to get back. To whit:


SELECT col.value('en_us[1]') as 'Text' FROM XmlImport (NOLOCK) CROSS APPLY ImportDocument.nodes('/Project/Project/Text') as tab(col) WHERE XmlImportId = 1


This will return the following, based on our XML document from above:

Text
Alpha
Beta

You can, of course, join that statement with other SQL statements to produce views, updates, or whatever.