CODEWITHSUNDEEP
sqlxmlsql-server-2005
NSN
NSN

Reputation: 750

SQL Server 2005 Query XML Data Type related

I am selecting 2 sets of columns from the same table, as follows.

select (select a, b, c from T1 FOR XML RAW('CAT1')),
       (select d, e, f from T1 FOR XML RAW('CAT2'))
for XML PATH('Parent')

The result of the query is 

<Parent>&lt;CAT1 a="Data1" b="Data2', c="Data3" &gt;&lt;CAT2 d="Data4" e="Data5" f="Data6"&gt;</Parent>

How to avoid? I would get enclosed in a proper parent xml?

Expected Result

<Parent>
   <CAT1 a="Data1" b="Data2', c="Data3">
   <CAT2 d="Data4" e="Data5" f="Data6">
</Parent>

Upvotes: 1

Views: 127

Answers (1)

Mikael Eriksson
Mikael Eriksson

Reputation: 138960

Use TYPE to specify that the sub queries should return XML.

select (select a, b, c from T1 FOR XML RAW('CAT1'), TYPE),
       (select d, e, f from T1 FOR XML RAW('CAT2'), TYPE)
for XML PATH('Parent')

TYPE Directive in FOR XML Queries

Upvotes: 2

Related Questions