SQL to XML

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!

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s