FOR XML clause (SQL Server)

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

• Concatenate rows into lines

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         100
1             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

 Result:

<row>
  <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