Hey Folks,

What do think when did FOR XML introduced?

It was introduced long way back with the arrival of SQL Server 2000.

Well, FOR XML is a row set aggregation function which returns a result set of one row and one column, which has a NVARCHAR (MAX) data type.

FOR XML can be used with these 4 directives:

  • AUTO
  • RAW
  • PATH

Will discuss one by one in detail…


  • It is one of the simplest directives from the rest of others in terms of generating XML output from results of a SELECT query.
  • It returns XML output having nested XML elements.
  • But it doesn’t provide much control over the structure of the XML output.
  • As the name suggests, it automatically identifies the element names and also the hierarchies.
  • The following query will make you understand about it:

This is how the ‘Emp’ table looks like:

1_SQL_Server_How_to_Generate_XML_Output_Using_FOR_XML_PART 1

Now, if we want to use Select query, then:

The results will come as:

2_SQL_Server_How_to_Generate_XML_Output_Using_FOR_XML_PART 1

  • The above result resembles more like an XML fragments, rather than an XML document, i.e., a valid xml document should have only one top-level element.
  • A root element can be added to the output of a FOR XML AUTO query, by adding the ROOT directive.
  • ROOT is an optional argument. By default, if we are not passing any text in it, the top element will be “Root”, otherwise, it will be the test which has given in the parameters. See the following example, will explain you further on it-

With the default Root;

The results come like:

3_SQL_Server_How_to_Generate_XML_Output_Using_FOR_XML_PART 1

While passing the parameter on it;

The result can be seen as:

4_SQL_Server_How_to_Generate_XML_Output_Using_FOR_XML_PART 1

  • Now, if we take for inner join of two tables then, by default FOR XML AUTO generates elements for each row:

The result can be seen as:

5_SQL_Server_How_to_Generate_XML_Output_Using_FOR_XML_PART 1

6_SQL_Server_How_to_Generate_XML_Output_Using_FOR_XML_PART 1

If we want to display, a value as an attribute, then we have to put an “ELEMENTS” directive:

7_SQL_Server_How_to_Generate_XML_Output_Using_FOR_XML_PART 1

8_SQL_Server_How_to_Generate_XML_Output_Using_FOR_XML_PART 1

Well, this is about FOR XML AUTO from my side, in the next post I would like to continue with the other directives, so please be tuned!!!

And also comments on this!!



Piyush Bajaj

Like us on FaceBook Follow us on Twitter | Join the fastest growing SQL Server group on FaceBook

Follow me on Twitter  |  Follow me on FaceBook