ansaurus

Question

I am having trouble getting the correct results using XML EXPLICIT in T-SQL (SQL Server 2008)

Answer 1

+1 A:

With SQL Server 2005 and up, forget about FOR XML EXPLICIT - use FOR XML PATH instead - it's much easier to use, more expressive, more intuitive.

Try this:

SELECT
    p.ID AS '@id',
    p.title AS '@title',
    p.sequence as '@sequence',
    (SELECT 
         c.ID AS '@id', 
         c.Title AS '@title',
         c.sequence as '@sequence'
     FROM @children c 
     WHERE p.connection = c.connection
     ORDER BY c.sequence
     FOR XML PATH('c'), TYPE
    )
FROM    
    @parents p
ORDER BY    
    p.sequence
FOR XML PATH('p')

Output will be this: (order by p.sequence on the outer scope, and c.sequence in the inner scope)

<p id="6" title="6" sequence="1">
  <c id="6" title="6a" sequence="1" />
  <c id="12" title="6b" sequence="2" />
</p>
<p id="2" title="2" sequence="2">
  <c id="8" title="2b" sequence="1" />
  <c id="2" title="2a" sequence="2" />
</p>
<p id="4" title="4" sequence="3">
  <c id="4" title="4a" sequence="1" />
  <c id="10" title="4b" sequence="2" />
</p>
<p id="3" title="3" sequence="4">
  <c id="9" title="3b" sequence="1" />
  <c id="3" title="3a" sequence="2" />
</p>
<p id="5" title="5" sequence="5">
  <c id="11" title="5b" sequence="1" />
  <c id="5" title="5a" sequence="2" />
</p>
<p id="1" title="1" sequence="6">
  <c id="7" title="1b" sequence="1" />
  <c id="1" title="1a" sequence="2" />
</p>

See the MSDN documentation on what's new in SQL Server 2005 for more hints and tips how to use the FOR XML PATH to generate XML from your database contents...

marc_s 2010-08-02 18:57:20

Thank you for your response. I can do this several ways but, I need to use Explicit mode because I need to ensure that the children are in a certain order (I did not include the "sequence" column in my example) and the only way to do that (that I am aware of) is using Explicit Mode. Does that sound right? Thanks.

rogdawg 2010-08-02 19:00:06

@rogdawg: no way - you can specify an ORDER BY in this query and use FOR XML PATH. You might need to be a bit more specific for your question - but I'm 95% sure you can do this with FOR XML PATH much easier than with FOR XML EXPLICIT

marc_s 2010-08-02 19:02:28

I was using the "for xml path" while trying to use an inner join to connect the children to the parent, and that was giving me bizarre sorting results. I didn't think about using a sub query to connect the children to the parent. That is fantastic solution, and certainly more elegant than "for xml explicit".Thanks very much for sticking with this until it was solved!

rogdawg 2010-08-02 19:40:17

ansaurus

tags:

views:

answers:

I am having trouble getting the correct results using XML EXPLICIT in T-SQL (SQL Server 2008)

related questions