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, ELEMENTSThis 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 ordersNormally 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.