SQL XML Explicit problems with creating xml tree (parent-child)

Question

I'm facing some problems with xml explicit in sql server where it doesn't output xml according to the relationship I specified in sql query. The query is done on pubs database and though xml path is easier to use my trainer needs it done on xml explicit.

    SELECT  1               AS Tag,
        NULL            AS Parent,
        NULL            AS [TitleTypes!1],
        NULL            AS [TitleType!2!Type],
        NULL            AS [TitleType!2!AveragePrice],
        NULL            AS [Title!3!title_id],
        NULL            AS [Title!3!price]

UNION ALL
SELECT  2,
        1,
        NULL            ,
        type            AS [TitleType!2!Type],
        AVG(price)      AS [TitleType!2!AveragePrice],
        NULL            AS [Title!3!title_id],
        NULL            AS [Title!3!price]
from titles 
GROUP BY type   
UNION ALL
SELECT  3,
        2,
        NULL            ,
        type            AS [TitleType!2!Type],
        NULL            AS [TitleType!2!AveragePrice],
        title_id        AS [Title!3!title_id],
        price           AS [Title!3!price]

from titles     

FOR XML EXPLICIT;

The output it produces:

    <TitleTypes>
  <TitleType Type="business    " AveragePrice="13.7300" />
  <TitleType Type="mod_cook    " AveragePrice="11.4900" />
  <TitleType Type="popular_comp" AveragePrice="21.4750" />
  <TitleType Type="psychology  " AveragePrice="13.5040" />
  <TitleType Type="trad_cook   " AveragePrice="15.9633" />
  <TitleType Type="UNDECIDED   ">
    <Title title_id="BU1032" price="19.9900" />
    <Title title_id="BU1111" price="11.9500" />
    <Title title_id="BU2075" price="2.9900" />
    <Title title_id="BU7832" price="19.9900" />
    <Title title_id="MC2222" price="19.9900" />
    <Title title_id="MC3021" price="2.9900" />
    <Title title_id="MC3026" />
    <Title title_id="PC1035" price="22.9500" />
    <Title title_id="PC8888" price="20.0000" />
    <Title title_id="PC9999" />
    <Title title_id="PS1372" price="21.5900" />
    <Title title_id="PS2091" price="10.9500" />
    <Title title_id="PS2106" price="7.0000" />
    <Title title_id="PS3333" price="19.9900" />
    <Title title_id="PS7777" price="7.9900" />
    <Title title_id="TC3218" price="20.9500" />
    <Title title_id="TC4203" price="11.9500" />
    <Title title_id="TC7777" price="14.9900" />
  </TitleType>
</TitleTypes>

The output I want:

   <TitleTypes>
    <TitleType Type="business" AveragePrice="11.22">
        <Title title_id="BU1111" Price="11.34"/>
        <Title title_id="TC7777" Price="14.2"/>
    </TitleType>
    <TitleType Type="popular_comp" AveragePrice="13.99">
        <Title title_id="BU1111" Price="15.9"/>
        <Title title_id="TC7777" Price="16.22"/>
    </TitleType>
</TitleTypes>

SQL XML Explicit is notoriously difficult. Almost every expert I've talked to recommends any other method (path, auto, raw) over explicit, if it's at all possible. — RBarryYoung, Nov 17 '13 at 05:13
yes I agree with that. Everywhere I go I see everyone preferring others over xml explicit. But *sigh* tell that to my trainer. — Faiaz Halim, Nov 17 '13 at 06:06
Problem with formatting is solved. Didn't think xml-explicit would heavily depend on ORDER BY clause. After writing ORDER BY [TitleTypes!1], [TitleType!2!Type], [Title!3!title_id] ,Tag solved my problem. Also if anyone having this kind of problem in future http://www.sqlxml.org/faqs.aspx?faq=28 has useful information. Thanks everyone — Faiaz Halim, Nov 17 '13 at 07:22

Roman Pekar · Accepted Answer · 2013-11-17T05:22:59.193

Usually, you don't need explicit mode at all. You can generate almost every xml you want with for xml path:

select
    t1.type as [@Type],
    avg(t1.price) as [@AveragePrice],
    (
        select
            t2.title_id as [@title_id],
            t2.price as [@price]
        from titles as t2
        where t2.type = t1.type
        for xml path('Title'), type
    )
from titles as t1
group by t1.type
for xml path('TitleType'), root('TitleTypes')

But since your xml is attribute-centric, it's even easier for you to use for xml raw:

select
    t1.type as [Type],
    avg(t1.price) as AveragePrice,
    (
        select
            t2.title_id
            t2.price
        from titles as t2
        where t2.type = t1.type
        for xml raw('Title'), type
    )
from titles as t1
group by t1.type
for xml raw('TitleType')

sql fiddle demo

Your answer is useful but for some reputation things couldn't upvote it. I could achieve some sort of tree-like structure with 'order by' clause but it requires the column I'm ordering by to be preset in every 'select-union' part. — Faiaz Halim, Nov 17 '13 at 06:09

score 0 · Answer 2 · answered Nov 19 '13 at 04:40

Here is the solution that worked for me. Look in the ORDER BY clause. If you're using sql server management studio in ORDER BY clause it'll show you possible column name by which you can format your output xml as you desire.

SELECT  1               AS Tag,
            NULL            AS Parent,
            NULL            AS [TitleTypes!1],
            NULL            AS [TitleType!2!Type],
            NULL            AS [TitleType!2!AveragePrice],
            NULL            AS [Title!3!title_id],
            NULL            AS [Title!3!price]

    UNION ALL
    SELECT  2,
            1,
            NULL            ,
            type            AS [TitleType!2!Type],
            AVG(price)      AS [TitleType!2!AveragePrice],
            NULL            AS [Title!3!title_id],
            NULL            AS [Title!3!price]
    from titles 
    GROUP BY type   
    UNION ALL
    SELECT  3,
            2,
            NULL            ,
            type            AS [TitleType!2!Type],
            NULL            AS [TitleType!2!AveragePrice],
            title_id        AS [Title!3!title_id],
            price           AS [Title!3!price]

    from titles     

    ORDER BY [TitleTypes!1], [TitleType!2!Type], [Title!3!title_id] ,Tag

    FOR XML EXPLICIT;

P.S. FOR XML EXPLICIT sucks. But if you're developing/upgrading a really really old system you have to know it before replacing with newer techniques.

SQL XML Explicit problems with creating xml tree (parent-child)

2 Answers2