Sql Server FOR XML EXPLICIT with inner queries

Question

I have the following query which produces XML output:

select Top 10 1 as tag,
    null as parent, 
    property_name as [Property!1!PropertyName!cdata], 
        (select 2 as tag, 
                1 as parent, 
                 null as [Subdivision!2!SubdivisionName!cdata] 
            from subdivision s 
           where s.subdivision_id=p.fk_subdivision_id
        FOR XML EXPLICIT) 
from property p
FOR XML EXPLICIT,root('Properties')

The result that I expected is:

<Properties>
   <Property>
       <PropertyName><![CDATA[Test Property]]></PropertyName>
       <Subdivision>
          <SubdivisionName><![CDATA[Test Subdivision]]</SubdivisionName>
       </Subdivision>
   </Property>     
</Properties>

I need the data to be enclosed in a cdata tag. So I used FOR XML EXPLICIT.

When I run the query, it gives this error:

FOR XML EXPLICIT query contains the invalid column name ''. Use the TAGNAME!TAGID!ATTRIBUTENAME[!..] format where TAGID is a positive integer.

I have gone through many forums, but they didn't help me. Please anyone help me out in framing the query or provide me any links that will help.

Thanks in advance

Answer 1

To use FOR XML EXPLICIT, you need to specify the hierarchy of nodes with UNIONed queries, not nested queries. The result of all the UNIONs is a table representing the tree structure of the XML, starting at the parentless root. Reference: http://msdn.microsoft.com/en-us/library/ms189068%28v=sql.100%29.aspx

SELECT
    1 AS Tag,
    NULL AS Parent, 
    p.property_name AS [Property!1!PropertyName!cdata],
    NULL AS [Subdivision!2!SubdivisionName!cdata]
FROM
    property p
UNION ALL
SELECT
    2 AS Tag,
    1 AS Parent,
    NULL AS [Property!1!PropertyName!cdata],
    s.subdivision_name AS [Subdivision!2!SubdivisionName!cdata]
FROM
    property p
INNER JOIN
    subdivision s
ON
    s.subdivision_id = p.fk_subdivision_id
FOR XML EXPLICIT, ROOT('Properties')