SQL Server > XML Data > FOR XML
You can retrieve results of a SQL query as XML by specifying the FOR XML clause in the query.
Examples
You can retrieve results of a SQL query as XML by specifying the FOR XML clause in the query.
Examples
Example
create table customer
(id int,
name nvarchar(max)
)
go
create table [order]
(
id int,
Date date,
amount int,
customer_id int
)
set nocount on
go
insert into
customer (id, name)
values
(1,'Customer 1')
insert into
customer (id, name)
values
(2,'Customer 2')
insert into
[order] (id, Date, amount, customer_id)
values
(1,GETDATE(),100, 1)
insert into
[order] (id, Date, amount, customer_id)
values
(2,GETDATE(),100, 1)
insert into
[order] (id, Date, amount, customer_id)
values
(3,GETDATE(),200, 1)
insert into
[order] (id, Date, amount, customer_id)
values
(4,GETDATE(),300, 2)
--query returns results as a rowset
select
customer.id,customer.name,
ord.id order_id,
ord.Date,
ord.amount
from
customer
join [order] ord on customer.id = ord.customer_id
Result:
id name order_id Date amount
1 Customer 1 1 2013-03-04 1001 Customer 1 2 2013-03-04 100
1 Customer 1 3 2013-03-04 200
2 Customer 2 4 2013-03-04 300
--query returns results as XML
AUTO mode returns query results as nested XML elements
select
customer.id,customer.name,
ord.id order_id,
ord.Date,
ord.amount
from
customer
join [order] ord on customer.id = ord.customer_id
FOR XML auto
Result:
<customer id="1" name="Customer 1">
<ord order_id="1" Date="2013-03-04" amount="100" /><ord order_id="2" Date="2013-03-04" amount="100" />
<ord order_id="3" Date="2013-03-04" amount="200" />
</customer>
<customer id="2" name="Customer 2">
<ord order_id="4" Date="2013-03-04" amount="300" />
</customer>
RAW mode transforms each row in the query result set into an XML element that has the generic identifier , or the optionally provided element name.
select
customer.id,customer.name,
ord.id order_id,
ord.Date,
ord.amount
from
customer
join [order] ord on customer.id = ord.customer_id
FOR XML raw
Result:
<row id="1" name="Customer 1" order_id="1" Date="2013-03-04" amount="100" />
<row id="1" name="Customer 1" order_id="2" Date="2013-03-04" amount="100" /><row id="1" name="Customer 1" order_id="3" Date="2013-03-04" amount="200" />
<row id="2" name="Customer 2" order_id="4" Date="2013-03-04" amount="300" />
PATH mode provides a simpler way to mix elements and attributes.
select
customer.id,customer.name,
ord.id order_id,
ord.Date,
ord.amount
from
customer
join [order] ord on customer.id = ord.customer_id
FOR XML path
<id>1</id>
<name>Customer 1</name>
<order_id>1</order_id>
<Date>2013-03-04</Date>
<amount>100</amount>
</row>
<row>
<id>1</id>
<name>Customer 1</name>
<order_id>2</order_id>
<Date>2013-03-04</Date>
<amount>100</amount>
</row>
<row>
<id>1</id>
<name>Customer 1</name>
<order_id>3</order_id>
<Date>2013-03-04</Date>
<amount>200</amount>
</row>
<row>
<id>2</id>
<name>Customer 2</name>
<order_id>4</order_id>
<Date>2013-03-04</Date>
<amount>300</amount>
</row>
drop table [order]
drop table customer