How can I get Sql Server 2K8 R2 to return this xml without resorting to compatability mode

Question

We are in the process of upgrading our sql server to 2K8 R2 and the output of a FOR XML AUTO query has changed.

The query outputs columns from three tables

The resultset returns three rows which each column is identical bar the last two columns from the third table. the results used to show as below

<element1 myval="Test">
    <element2 myotherval="atest">
         <element3 a="a"/>
         <element3 a="b"/>
         <element3 a="c" />
    </element2>
</element1>

it not shows

    <element1 myval="Test">
        <element2 myotherval="atest">
             <element3 a="a"/>
         </element2>
    </element1>
    <element1 myval="Test">
        <element2 myotherval="atest">
             <element3 a="B"/>
         </element2>
    </element1>
    <element1 myval="Test">
        <element2 myotherval="atest">
             <element3 a="C"/>
         </element2>
    </element1>

I have been trying to use For XML Path but it still returns 3 separate instances of element1 rather than grouping the data.

Answer 1

If you want a subtree using FOR XML PATH, you'll have to write subqueries for each subtree. So in your case you have a parent select statement for element1, and one of the columns is a subquery that gets whatever needs to be in element2 (which in turn can also be subqueries). If you use subqueries and you want XML returned from those, use

FOR XML PATH('elementN'), TYPE

or it'll escape the XML code.