Saturday, February 25, 2012

Newbie - generate XML tree from parent-child hierarchy

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.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|||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

No comments:

Post a Comment