Generate XML Using FOR XML

A SELECT query returns result as a Table. In SQL, we can retrieve the SELECT query result as XML using FOR XML

FOR XML is used only in SELECT if it is not a Sub Query. In Sub-query, it can be used for INSERT, UPDATE, DELETE as well as in assignment statements.

In a FOR XML clause you can specify these modes.

1. AUTO
2. RAW
3. EXPLICIT
4. PATH

The AUTO mode with FOR XML generates nesting in the resulting XML on the way the SELECT statement is specified. We have minimal control over the shape of the XML generated.

The RAW mode with FOR XML generates a single tuple <row> element per row in the table that is returned by the SELECT statement.

The EXPLICIT mode gives more control on the shape of the XML that we need to generate. We can get attributes and elements together in deciding the shape of the XML. It requires a specific format for the resulting rowset that is generated because of query execution. This rowset format is then mapped into XML shape.

**The power of EXPLICIT mode is to mix attributes and elements together. It create wrappers and nested complex properties, create space-separated values and mixed contents.

The PATH mode together with the nested FOR XML query capability provides the flexibility of the EXPLICIT mode in a simpler manner.

These modes are in effect only for the execution of the query for which they are set. They do not affect the results of any subsequent queries.

Here are the examples to generate different XML using FOR XML.

For all the modes, we will use the following script to generate and insert the data.

CREATE TABLE Players
(
Id INT NOT NULL IDENTITY(1,1),
FirstName NVARCHAR(50),
LastName NVARCHAR(50),
CITY NVARCHAR(30)
);

INSERT INTO Players VALUES (‘Sachin‘,’Tendulkar‘,’Mumbai‘);
INSERT INTO Players VALUES (‘Saurav‘,’Ganguli‘,’Kolkata‘);
INSERT INTO Players VALUES (‘Mahendrasinh‘,’Dhoni‘,’Chennai‘);

1. AUTO

SELECT * FROM Players FOR XML AUTO

Result:

<Players Id="1" FirstName="Sachin" LastName="Tendulkar" CITY="Mumbai" />
<Players Id="2" FirstName="Saurav" LastName="Ganguli" CITY="Kolkata" />
<Players Id="3" FirstName="Mahendrasinh" LastName="Dhoni" CITY="Chennai" />

2. RAW

SELECT * FROM Players FOR XML RAW(‘Cricketers‘)

Result:

<Cricketers Id="1" FirstName="Sachin" LastName="Tendulkar" CITY="Mumbai" />
<Cricketers Id="2" FirstName="Saurav" LastName="Ganguli" CITY="Kolkata" />
<Cricketers Id="3" FirstName="Mahendrasinh" LastName="Dhoni" CITY="Chennai" />

3. PATH :

SELECT * FROM Players FOR XML PATH(‘Player‘)

Result:

<Player>
  <Id>1</Id>
  <FirstName>Sachin</FirstName>
  <LastName>Tendulkar</LastName>
  <CITY>Mumbai</CITY>
</Player>
<Player>
  <Id>2</Id>
  <FirstName>Saurav</FirstName>
  <LastName>Ganguli</LastName>
  <CITY>Kolkata</CITY>
</Player>
<Player>
  <Id>3</Id>
  <FirstName>Mahendrasinh</FirstName>
  <LastName>Dhoni</LastName>
  <CITY>Chennai</CITY>
</Player>

4. EXPLICIT

SELECT 
        1 AS Tag,
        NULL AS Parent,
        ID AS ‘Player!1!Id‘,
        NULL AS ‘Detail!2!FirstName‘,
        NULL AS ‘Detail!2!LastName‘,
        NULL AS ‘Detail!2!City‘
FROM Players WHERE Id = 1
UNION ALL
SELECT 
        2 AS Tag,
        1 AS Parent,
        NULL,
        FirstName,
        LastName,
        CIty
FROM Players WHERE Id = 1
FOR XML EXPLICIT

Result:

<Player Id="1">
  <Detail FirstName="Sachin" LastName="Tendulkar" City="Mumbai" />
</Player>