SQL Server – How to Generate XML Output Using FOR XML? – PART 1

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
• EXPLICIT

Will discuss one by one in detail…

FOR XML AUTO

• 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:

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

SELECT E_Id, E_Name
FROM Emp
FOR XML AUTO

The results will come as:

• 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;

SELECT E_Id, E_Name
FROM Emp
FOR XML AUTO, ROOT

The results come like:

While passing the parameter on it;

SELECT E_Id, E_Name
FROM Emp
FOR XML AUTO, ROOT('TCS')

The result can be seen as:

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

SELECT [Emp].E_Id, [Emp].E_Name, [New_Employees].id_num, [New_Employees].fname
FROM Emp INNER JOIN New_Employees 
ON E_Id = id_num
FOR XML AUTO

The result can be seen as:

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

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!!

Regards

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