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
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:
cast (
as xml ) as 'items'
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>
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:
for xml path('item'), TYPE
with much desired output:
<items_list attribute1="123" attribute2="345" timestamp="456" currentDate="2010-01-29T19:55:37.973">
Hope description of this small 'gotcha' was useful.
Until next time.
-Yuriy