More on XML and SQL

As mentioned previously

Select * from table for XML auto

creates one element for each row, with all the values as attributes

Select * from table for XML auto, elements

creates an XML fragment with a collection of nested elements for each row returned, with the table name as the root element for each of the row collections.  If we want something other than the table name as the ‘root’, we can do this:

Select * from table for XML path(‘rootname’)

We get the same output as auto, elements, without a true root element, but each collection will have the the top element as ‘rootname’

If we want one or more of these returned fields to be an attribute rather than an element, we have to do two things: we need to name the fields we want returned rather than using *, and we need to give one or more of the fields an alias of “as ‘@name'”

Select field_one, field_two as ‘@name’, field_three from table for xml path(‘rootname’)

Finally, if perchance, one of our fields is already xml, it gets returned nested inside the fieldname

Select field_one, field_two as ‘@name’, field_three, field_xml  from table for xml path(‘rootname’)

The returned XML element will have a declared XML namespace but not specifically appended to each of the returned XML elements.

Examples of this can be found in the article by  in the RedGate SimpleTalk newsletter.

This entry was posted in sql and tagged . Bookmark the permalink.