Q) How do I add a root node and return columns as elements with FOR XML?

Question

How do I add a root node and return columns as elements with FOR XML?

Answer

FOR XML is a TSQL option which can be added to the SELECT statement to return data in XML format.   A simple example of this is:

SELECT * FROM INFORMATION_SCHEMA.Tables FOR XML AUTO

When combined with JOINs FOR XML can be used to return structured XML hierarchies, however we won’t go into those details here.  Instead we will focus on the question.  To add a root node simply add the ROOT(rootnodename) modifier to the FOR XML clause.  For example:

SELECT * FROM INFORMATION_SCHEMA.Tables FOR XML AUTO,ROOT('Tables')

To change the Element tag name for each row you can use a table alias or change to using RAW or PATH mode.  For example:

SELECT * FROM INFORMATION_SCHEMA.Tables as [Table] FOR XML AUTO, ROOT('Tables')

OR

SELECT * FROM INFORMATION_SCHEMA.Tables FOR XML RAW ('Table'), ROOT('Tables')

And finally, if you want each column to be translated into as an element instead of being an attribute you add the ELEMENTS modifier.  This is useful, for example, if you are outputting XML to be loaded into a DataSet via the DataSets ReadXML method.  For example:

SELECT * FROM INFORMATION_SCHEMA.Tables as [Table] FOR XML AUTO, ELEMENTS, ROOT('Tables')

OR

SELECT * FROM INFORMATION_SCHEMA.Tables FOR XML RAW ('Table'), ELEMENTS, ROOT('Tables')

Hope this helps.  All Answers provided are subject to our standard Answers Disclaimer.