FOR XML EXPLICIT

Question

FOR XML EXPLICIT

Kevin Terry

SSC Eights!

Points: 806
More actions
May 25, 2004 at 12:04 pm

#161437

I need to create an xml document from relational data. Using FOR XML EXPLICIT I can get it to two levels, but can't seem to get the third child to work correctly. The three tables are:
tblcompanylist, tblgroup, and tblselections.
The xml doc should look like this:
<Company Name="AAA Company">
<Group Name="1" State="First Set of Exit Options "/>
<Selection Name="Selection That Saves"/>
<Selection Name="Data Report"/>
<Selection Name="Guidance Report"/>
<Selection Name="URL"/>
<Group Name="1" State="Groupitty Duppitty"/>
<Selection Name="Informational Panel"/>
<Selection Name="Some selection name"/>
<Group Name="1" State="Third Group">
<Selection Name="Log Report"/>
<Selection Name="Save Selection"/>
<Selection Name="Selection 1"/>
<Selection Name="Selection 2"/>
</Group>
</Company>
But I can only get it to look like this:
<Company Name="AAA Company">
<Group Name="1" State="First Set of Exit Options "/>
<Group Name="1" State="Groupitty Duppitty"/>
<Group Name="1" State="Third Group">
<Selection Name="Selection That Saves"/>
<Selection Name="Data Report"/>
<Selection Name="Guidance Report"/>
<Selection Name="URL"/>
<Selection Name="Informational Panel"/>
<Selection Name="Text for the selection"/>
<Selection Name="Guidance Report"/>
<Selection Name="Save Selection"/>
<Selection Name="Selection 1"/>
<Selection Name="Selection 2"/>
</Group>
</Company>
The selection child node should be a child of the group (as group is a child of Company),
but it seems to only be getting listed as a child of Company. Can anyone tell what I'm doing wrong?
The query I'm using is:
SELECT 1 as Tag, NULL as Parent,
A.fldcompanyname as [Company!1!Name],
NULL  as [Group!2!Name],
NULL  as [Group!2!State],
NULL  as [Selection!3!Name]
FROM tblcompanylist as A
UNION ALL
Select 2, 1,
A.fldcompanyname,
B.fldGroupState,
B.fldgrouptext,
NULL
from tblcompanylist as A, tblgroup as B
where A.fldcompanyid = B.fldcompanyid
UNION ALL
Select 3, 2,
A.fldcompanyname,
--NULL,
--NULL,
B.fldGroupState,
B.fldgrouptext,
c.fldSelectionText
from tblcompanylist as A, tblgroup as B, tblselections as C
where A.fldcompanyid = B.fldcompanyid and B.fldgroupid = C.fldgroupid
ORDER BY [Company!1!Name]
FOR XML EXPLICIT

Thanks for any advice offered!

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

Eddie Wuerch SSChampion Points: 12535 More actions · Answer 1

So near, but yet so far away

The only thing you need to do to get your statement working is to extend the ORDER BY to include the fields that separate the node levels:

Change:

ORDER BY [Company!1!Name]

To:

ORDER BY [Company!1!Name] , [Group!2!Name], [Group!2!State]

-Eddie

Eddie Wuerch
MCM: SQL

Kevin Terry SSC Eights! Points: 806 More actions · Answer 2

Thanks, that got it to work exactly! (although I did have to replace the last order by element with [Selection!3!Name] instead of [Group!2!State] ) but that was obvious from what you had said.

Again, thanks.