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.