Newbie - generate XML tree from parent-child hierarchy

10 May 2007 12:24 PM

Richard

Hi all,

I'm trying to do something that I know should be straightforward, but
I just cannot get it to work.

Imagine a simple Bill-Of-Materials with one table and a parent-child
relationship.
e.g.   PartNumberID, ParentPartNumberID, PartDescription,
PartCost,....
Where ParentPartNumberID references PartnumberID, except at the top
level where it is NULL.
There are an unknown number of levels to this hierarchy

What I want to get out is an XML document something like this.
<root>
   < PART ID =PartNumberID, Description = PartDescription>
       < SUBPARTS>
           < PART ID =PartNumberID, Description = PartDescription />
           < PART ID =PartNumberID, Description = PartDescription />
           < PART ID =PartNumberID, Description = PartDescription>
               <SUBPARTS>
                  < PART ID =PartNumberID, Description =
PartDescription />
             </SUBPARTS>
      </SUBPARTS>
   < PART ID =PartNumberID, Description = PartDescription/>
   < PART ID =PartNumberID, Description = PartDescription/>
...
</root>

Where structure of the tree represents the same relationships as the
parent-child hierarchy does.
I'm sure this is entirely do-able, but I am stumped. I have quite a
lot of SQL expertise, but am relatively new to XML - and I'm sure I'm
simply looking at the problem from the wrong angle somehow.

Any help or pointers towards published solutions would be much
appreciated.

Thanks in advance,

Richard.

p.s.Please forgive me if this is the wrong place to post this, I've
read both this and the .programming group and I think this is the
more appropriate one.

10 May 2007 1:02 PM

markc600

Assuming SQL Server 2005

CREATE FUNCTION dbo.GetPartsSubTree(@PartNumberID int)
RETURNS XML
BEGIN RETURN
(SELECT PartNumberID AS "@ID",
        PartDescription AS "@Description",
        dbo.GetPartsSubTree(PartNumberID)
FROM Parts
WHERE ParentPartNumberID=@PartNumberID
ORDER BY PartNumberID
FOR XML PATH('PART'),ROOT('SUBPARTS'),TYPE)
END

GO

SELECT PartNumberID AS "@ID",
        PartDescription AS "@Description",
        dbo.GetPartsSubTree(PartNumberID)
FROM Parts
WHERE ParentPartNumberID is null
ORDER BY PartNumberID
FOR XML PATH('PART'),ROOT('Root'),TYPE

11 May 2007 9:32 AM

Richard

HI Mark,

Sorted! Thank you very much indeed.

Having got the solution I knew what to look for on the wenb, and a bit
of further digging on the PATH option found the following article,
which gives the same solution type as you do, and explains what the
various new features do.

http://msdn2.microsoft.com/en-us/library/ms345137.aspx

Best Regards,

Richard