Migrating relational data into a SQL Server XML datatype column

Recently I needed to take the data from a SQL Server table, convert it into XML and insert it into an xml datatype column of a related table. In the past I would have used a scripting language such as Perl to achieve this, but since SQL Server 05/08 has native XML support I decided to give that a go.  I did this because I thought it would be more efficient, and less error prone, to do everything at the database level. It turns out that it was easier than I thought.

I was already familiar with SELECT .. FOR XML syntax from previous experiments with SQL Server's XML functionality, so was able to convert the existing table data to XML as follows:

SELECT part_id, part_name, part_description
FROM parts FOR XML AUTO, ELEMENTS

This generated the following XML:

<parts>
<part_id>4</part_id> 
  <part_name>Widget</part_name>
  <part_description>Widget to do something</part_description>
</parts>

Note: The XML generated by FOR XML AUTO was good enough for my purposes. If you need more control over the XML output you can use FOR XML EXPLICIT instead.

The next step was to insert this into the appropriate row of a related table. I achieved this using a sub-query on an UPDATE.  The resulting SQL was as follows:

UPDATE orders SET bio_sample_aliquot_info = ( 
SELECT part_id, part_name, part_description FROM parts 
WHERE order.id = part.order_id FOR XML AUTO, ELEMENTS)
FROM orders

Normally I don't like using sub-queries as they can be inefficient, but this ended up been fast enough for my needs.

After running the above query I had the XML I wanted in the appropriate XML datatype rows of the related table.

Note: I've changed all of the names of the tables and columns in the example above.  Also I tested this on SQL Server 08, but not 05.