title: SQL XML Explicit problems with creating xml tree (parent-child)
body: <p>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. </p>
  
  <pre><code>    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;
  </code></pre>
  
  <p>The output it produces:</p>
  
  <pre><code>    &lt;TitleTypes&gt;
    &lt;TitleType Type="business    " AveragePrice="13.7300" /&gt;
    &lt;TitleType Type="mod_cook    " AveragePrice="11.4900" /&gt;
    &lt;TitleType Type="popular_comp" AveragePrice="21.4750" /&gt;
    &lt;TitleType Type="psychology  " AveragePrice="13.5040" /&gt;
    &lt;TitleType Type="trad_cook   " AveragePrice="15.9633" /&gt;
    &lt;TitleType Type="UNDECIDED   "&gt;
      &lt;Title title_id="BU1032" price="19.9900" /&gt;
      &lt;Title title_id="BU1111" price="11.9500" /&gt;
      &lt;Title title_id="BU2075" price="2.9900" /&gt;
      &lt;Title title_id="BU7832" price="19.9900" /&gt;
      &lt;Title title_id="MC2222" price="19.9900" /&gt;
      &lt;Title title_id="MC3021" price="2.9900" /&gt;
      &lt;Title title_id="MC3026" /&gt;
      &lt;Title title_id="PC1035" price="22.9500" /&gt;
      &lt;Title title_id="PC8888" price="20.0000" /&gt;
      &lt;Title title_id="PC9999" /&gt;
      &lt;Title title_id="PS1372" price="21.5900" /&gt;
      &lt;Title title_id="PS2091" price="10.9500" /&gt;
      &lt;Title title_id="PS2106" price="7.0000" /&gt;
      &lt;Title title_id="PS3333" price="19.9900" /&gt;
      &lt;Title title_id="PS7777" price="7.9900" /&gt;
      &lt;Title title_id="TC3218" price="20.9500" /&gt;
      &lt;Title title_id="TC4203" price="11.9500" /&gt;
      &lt;Title title_id="TC7777" price="14.9900" /&gt;
    &lt;/TitleType&gt;
  &lt;/TitleTypes&gt;
  </code></pre>
  
  <p>The output I want:</p>
  
  <pre><code>   &lt;TitleTypes&gt;
      &lt;TitleType Type="business" AveragePrice="11.22"&gt;
          &lt;Title title_id="BU1111" Price="11.34"/&gt;
          &lt;Title title_id="TC7777" Price="14.2"/&gt;
      &lt;/TitleType&gt;
      &lt;TitleType Type="popular_comp" AveragePrice="13.99"&gt;
          &lt;Title title_id="BU1111" Price="15.9"/&gt;
          &lt;Title title_id="TC7777" Price="16.22"/&gt;
      &lt;/TitleType&gt;
  &lt;/TitleTypes&gt;
  </code></pre>