Recently I had to build some XML files for importing into a Desire2Learn instance. They were nice enough to provide an XSD and a little bit of sample XML, but I didn’t want to actually build another console application to generate an XML file. I wanted to just export data straight from my SQL server. Enter FOR XML. There is a lot of documentation available for this feature, but a lot of it assumed that your column names matched very well with the shape of the XML you wanted to make. Thankfully FOR XML is actually quite powerful in letting you designate what shape your XML looks like.
For example, an XML file for importing might look like this:
<enterprise>
<group recstatus="2">
<sourcedid>
<id>MATH_101</id>
</sourcedid>
<grouptype>
<typevalue level="4">Course Template</typevalue>
</grouptype>
<description>
<short>MATH_101</short>
<long>Basic Math</long>
</description>
<relationship relation="1">
<sourcedid>
<id>MATH</id>
</sourcedid>
<label>A parent of MATH_101 is the Math Department</label>
</relationship>
<extension>
<path>MATH/templates/MATH_101/</path>
</extension>
</group>
</enterprise>
You might have the data in your SQL database that could be queried simply like this:
SELECT Template, CourseDescription, Department FROM Courses
To get from the “Normal” SQL to the XML output, you use FOR XML, and aliasing that looks like XPATH notation. Prefixing the last token in the alias turns that value into an attribute of the previously named tag.
SELECT '2' AS '@recstatus',
Template AS 'sourcedid/id',
'4' AS 'grouptype/typevalue/@level',
'Course Template' AS 'grouptype/typevalue',
Template AS 'description/short',
CourseDescription AS 'description/long',
1 AS 'relationship/@relation',
Department AS 'relationship/sourcedid/id',
'A parent of ' + Template + ' is the ' + Department + ' Department' AS 'relationship/label',
Department + '/templates/' + Template + '/' AS 'extension/path'
FROM Courses
FOR XML PATH('group'), ROOT('enterprise')
Easy!
Will Web for Food 