Embeding XML into XML using FOR XML option
Hello, everyone!
Today I am going to chat briefly about the question a co-worker asked me about.
The problem was with embedding one XML snippet generated by using FOR XML option of the query into another XML also generated using FOR XML option.
Here is cleaned up version of the query :
declare @t table(c1 int, c2 int, ts binary(8))
insert into @t(c1,c2)
select 1, 2
union all
select 3, 4
union all
select 5, 6
declare @message xml
select @message =
( select
123 as '@attribute1',
345 as '@attribute2',
456 as '@timestamp',
getdate() as '@currentDate',
(select c1 as '@c1',
c2 as '@c2'
from @t
for xml path('item')
) as 'items'
for xml path ('items_list'), binary base64
)
select @message
If you execute this query directly against your instance of SQL Server you will get the following result:
<items_list attribute1="123" attribute2="345" timestamp="456" currentDate="2010-01-29T19:43:36.117">
<items><item c1="1" c2="2"/><item c1="3" c2="4"/><item c1="5" c2="6"/></items>
</items_list>
Please notice < and > in the output, since '<' and '>' have specific meaning in XML as tags. This is surely not what my coworker expected and intended.
The reason for this is that the result of the inner FOR XML query is returned as nvarchar(max) by default. The straightforward way around it is to cast the result to xml type using CAST as follows:
declare @t table(c1 int, c2 int, ts binary(8))
insert into @t(c1,c2)
select 1, 2
union all
select 3, 4
union all
select 5, 6
declare @message xml
select @message =
( select
123 as '@attribute1',
345 as '@attribute2',
456 as '@timestamp',
getdate() as '@currentDate',
cast (
(select c1 as '@c1',
c2 as '@c2'
from @t
for xml path('item')
)
as xml ) as 'items'
for xml path ('items_list'), binary base64
)
select @message
The output of this is what was intended:
<items_list attribute1="123" attribute2="345" timestamp="456" currentDate="2010-01-29T19:51:06.940">
<items>
<item c1="1" c2="2" />
<item c1="3" c2="4" />
<item c1="5" c2="6" />
</items>
</items_list>
However, in SQL Server FOR XML clause allows for TYPE option to achieve exactly this so there is no need to cast the result to XML type:
declare @t table(c1 int, c2 int, ts binary(8))
insert into @t(c1,c2)
select 1, 2
union all
select 3, 4
union all
select 5, 6
declare @message xml
select @message =
( select
123 as '@attribute1',
345 as '@attribute2',
456 as '@timestamp',
getdate() as '@currentDate',
(select c1 as '@c1',
c2 as '@c2'
from @t
for xml path('item'), TYPE
) as 'items'
for xml path ('items_list'), binary base64
)
select @message
with much desired output:
<items_list attribute1="123" attribute2="345" timestamp="456" currentDate="2010-01-29T19:55:37.973">
<items>
<item c1="1" c2="2" />
<item c1="3" c2="4" />
<item c1="5" c2="6" />
</items>
</items_list>
Hope description of this small 'gotcha' was useful.
Until next time.
-Yuriy