Click here to Skip to main content
Click here to Skip to main content

Generate XML Using FOR XML

, 29 May 2012 CPOL
Rate this:
Please Sign up or sign in to vote.
Generate XML Using FOR XML ; Difference between different modes (AUTO, RAW, PATH, EXPLICIT) of FOR XML to generate XML in SQL Server

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>

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

Harsh Gandhi
Software Developer
India India
No Biography provided

Comments and Discussions

 
QuestionIncomplete.... Pinmemberamitgajjar30-May-12 18:59 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Mobile
Web04 | 2.8.141022.2 | Last Updated 29 May 2012
Article Copyright 2012 by Harsh Gandhi
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid