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:

1_SQL_Server_How_to_Generate_XML_Output_Using_FOR_XML_PART 1

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

SELECT E_Id, E_Name
FROM Emp
FOR XML AUTO

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;

SELECT E_Id, E_Name
FROM Emp
FOR XML AUTO, ROOT

The results come like:

3_SQL_Server_How_to_Generate_XML_Output_Using_FOR_XML_PART 1

While passing the parameter on it;

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

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

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

 

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

Data Platform Virtual Summit 2020

Subscribe to SQLServerGeeks YouTube channel. If you want more learning content in your inbox, subscribe to SQLServerGeeks Bulletin.

SQLServerGeeks YouTube | SQLServerGeeks Bulletin | SQLServerGeeks Twitter

Avatar

About Piyush Bajaj

I am very passionate about SQL Server. I also did certification on MCSA – SQL Server 2012, Querying and Administering; MCTS – SQL Server 2008, Database Development; and MCTS – SQL Server 2005, Implementation & Maintenance, which helped me to get more knowledge and interest on this field.Please feel free to drop me any question online or offline, I will try to give you the best possible answer from my side.Right now I am working as a SQL Server developer in TCS. I have an experience of just 2.6 years, well I can only say that “If you have an interest and passion, experience might become a very small thing”.

View all posts by Piyush Bajaj →

One Comment on “SQL Server – How to Generate XML Output Using FOR XML? – PART 1”

Leave a Reply

Your email address will not be published. Required fields are marked *