Details of "FOR XML" sql clause in SQL-Server

SQL Server allows developers to query SQL Server and receive data in XML format via a special clause: FOR XML. This clause provides 4 different modes for the XML format returned: RAW, AUTO, EXPLICIT and PATH (new feature of SQL Server 2005)

 

RAW:

It generates single elements, which are named row, for each row returned.

Sample:

select id, name from customer where id = 3 for xml raw

< row id = "3"name="Customer         3" />

 

AUTO:

            Looks quite like “RAW”, the difference is the element is named as the table name, instead of “row”.

            Sample:

            select id, name from customer where id = 3 for xml auto

      < customer id = "3"name="Customer         3" />

 

Question here:

            How to get the record set in xml which contains more than one record? In other words, how to add a root node?

            Sample:

            select id, name from customer where id in(3,4) for xml auto

            < customer id = "3"name="Customer         3" />

< customer id = "4"name="Customer         4" />

            It seems we have to use EXPLICIT or PATH to meet the requirement.

           

EXPLICIT:

            Very powerful, could generate xml data with several levels of nodes.

            The sql sentence must have tag, parent, and <nodeName>!<tagValue>[!<attributeName>] in the select clause. If the ‘!<attribute>’ is included in the column name, then this column will be shown as attribute with the <attributeName>; otherwise, it will be shown as element

---------------- add root node ---------------

select 1 as tag, null as parent, null as [root!1], null as [data!2!id], null as [data!2!name]

union

select 2, 1, null,id, name from customer where id in(3,4)

for xml explicit

            < root >

 < data id = "3"name="Customer         3" />

 < data id = "4"name="Customer         4" />

</ root >

 

---------------- 3 levels xml ---------------

SELECT    1                  as Tag,

         NULL               as Parent,

         NULL               as [root!1],

         NULL               as [C!2!customerid],

         NULL               as [C!2!customerName],

         NULL               as [O!3!orderid],

         NULL               as [O!3!orderName]

UNION all

SELECT 2, 1, NULL, id, name, NULL, NULL

FROM Customer as C where c.id in(3,4)

UNION all

SELECT 3, 2, NULL, C.id, C.name, O.id, O.name

    FROM Customer C, Orders O

    WHERE (C.id = O.uid) and c.id in(3,4)

order by [C!2!customerid],[O!3!orderid]

FOR XML EXPLICIT

 

< root >

 < C customerid = "3"customerName="Customer         3">

    < O orderid = "3"orderName="Order         3" />

    < O orderid = "33"orderName="Order        33" />   

 </ C >

 < C customerid = "4"customerName="Customer         4">

    < O orderid = "4"orderName="Order         4" />

    < O orderid = "34"orderName="Order        34" />

 </ C >

</ root >

PATH (2005 new feature):

            Much easier to use than EXPLICIT.

---------------- add root node ---------------

select id , name from customer where id in(3,4)

for xml path, root('root')

< root >

 < row >

    < id > 3</id>

    < name > Customer         3</name>

 </ row >

 < row >

    < id > 4</id>

    < name > Customer         4</name>

 </ row >

</ root >

 

select id as '@id', name as '@name' from customer where id in(3,4)

for xml path('data'), root('root')

< root >

 < data id = "3"name="Customer         3" />

 < data id = "4"name="Customer         4" />

</ root >

---------------- 3 levels xml ---------------

SELECT id as "@CustomerID",

           (SELECT id as "@OrderID"

            FROM Orders

            WHERE Orders.uid = Customer.id

            FOR XML PATH('Orders'), TYPE)

FROM Customer where id in(3,4)

FOR XML PATH('Customer'), root('myRoot')

< myRoot >

 < Customer CustomerID = "3">

    < Orders OrderID = "3" />

    < Orders OrderID = "33" />   

 </ Customer >

 < Customer CustomerID = "4">

    < Orders OrderID = "4" />

    < Orders OrderID = "34" />   

 </ Customer >

</ myRoot >

 

Elements or Attributes:

1)      For RAW, AUTO and EXPLICIT, fields are shown as attributes by default, unless use the Elements options.

select id, name from customer where id = 3 for xml auto

      < customer id = "3"name="Customer         3" />

 

select * from customer where id = 3 for xml auto, elements

< customer >

 < id > 3</id>

 < name > Customer         3</name>

</ customer >

2)      For PATH, fields are shown as elements by default, unless use fieldname as ‘@attributeName’ in the select clause.

See the topic PATH for examples.