FOR XML clause (SQL Server)

You can retrieve results of a SQL query as XML by specifying the FOR XML clause in the query.

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

 

Dynamic PIVOT and cross-tab query (SQL Server)

SQL Server > Operators > PIVOT > Dynamic PIVOT and cross-tab query



Sometimes we don't know values in FOR clause for pivot.
In this example we will build dynamic columns with values and use dynamic query to build pivot and cross-tab query.

Example:

create table pivot_data

(
       id           int,
       Date   date,
       amount int
)
set nocount on
go

insert into 
       pivot_data (id, Date, amount)
values
       (1,GETDATE(),100)
insert into 
       pivot_data (id, Date, amount)
values
       (1,dateadd(day,1, GETDATE()),200)
insert into 
       pivot_data (id, Date, amount)
values
       (2,dateadd(month,1, GETDATE()),300)
insert into 
       pivot_data (id, Date, amount)
values
       (2,dateadd(month,2, GETDATE()),400)     

select * from pivot_data

Result:

id            Date                    amount

1             2013-03-04         100
1             2013-03-05         200
2             2013-04-04         300
2             2013-05-04         400

DECLARE

    @cols AS NVARCHAR(MAX),

    @query  AS NVARCHAR(MAX);
SET @cols = STUFF((SELECT  ',' + QUOTENAME(c.Date )
            FROM pivot_data c
            order by Date
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)')
        ,1,1,'')
print @cols

set @query = 'SELECT id, ' + @cols + ' from

            (
                select 
                    id
                    , date
                    , amount
                from 
                    pivot_data
           ) x
            pivot
            (
                sum(amount)
                for  date in (' + @cols + ')
            ) p '

execute(@query)

Result:

id 2013-03-04    2013-03-05        2013-04-04        2013-05-04

1 100                 200                     NULL                 NULL
2 NULL              NULL                 300                     400

drop table pivot_data