MSSQL into XML in Hierarchy?
-
I have a client that I need to generate an XML file for, since XML is very good at managing hierarchical data. I have a flat table with information like this…
ID Title Parent
1 Root 1
2 Air 1
3 Air Monitoring 1
4 Aluminum Foil 1
5 Ampules 1
6 Anemometers 1
7 Animal Equipment 1
8 Metabolic Cages 7
9 Animal Restrainers 7
10 Nalgene® Activity Assembly 7
11 Disposable Cages 7
12 Animal Supplies 7
13 Containment Systems 7
14 Nalgene® Animal Cage System 7
15 Apparel 1
16 Aprons 15
And I need to put it into something like this…
<Sections><Item ID=”1” Title=”Root” >
<Item ID=”2” Title=”Air” />
<Item ID=”3” Title=””Air Monitoring />
<Item ID=”4” Title=”Aluminum Foil” />
<Item ID=”5” Title=”Ampules” />
<Item ID=”6” Title=”Anemometers” />
<Item ID=”7” Title=”Animal Equipment” >
<Item ID=”8” Title=”Metabolic Cages” />
<Item ID=”9” Title=”Animal Restrainers” />
<Item ID=”10” Title=”Nalgene® Activity Assembly” />
<Item ID=”11” Title=”Disposable Cages” />
<Item ID=”12” Title=”Animal Supplies” />
<Item ID=”13” Title=”Containment Systems” />
<Item ID=”14” Title=”Nalgene® Animal Cage System” />
</Item>
<Item ID=”15” Title=”Apparel” >
<Item ID=”16” Title=”Aprons” />
</Item>
</Item>
</Sections>
Can this be done with only MSSQL 2005? I know I can write code that makes recursive calls back to the table, but I’m wondering I can get this data to display properly with just MSSQL? Your thoughts?
<songs>
<song name ="Crazy" band="Icehouse" file="tracks/Crazy.mp3" />
<song name ="Pictures of You" band="The Cure" file="tracks/POY.mp3" />
<song name ="The More We Live" band="Yes" file="tracks/LetGo.mp3" />
</songs>
-
CREATE FUNCTION dbo.GetSubTree(@ID int)RETURNS XML
BEGIN RETURN
(SELECT ID AS "@ID",
Title AS "@Title",
dbo.GetSubTree(ID)
FROM MyTable
WHERE Parent=@ID AND ID<>Parent
ORDER BY ID
FOR XML PATH('Item'),TYPE)
END
GO
SELECT ID AS "@ID",
Title AS "@Title",
dbo.GetSubTree(ID)
FROM MyTable
WHERE ID=Parent
ORDER BY ID
FOR XML PATH('Item'),ROOT('Sections'),TYPE
____________________________________________________
Deja View - The strange feeling that somewhere, sometime you've optimised this query before
How to get the best help on a forum
http://www.sqlservercentral.com/articles/Best+Practices/61537
-
This is great, thank you. It's steering me in the right direction, as I (being the novice I am) can understand what it's doing nicely. May I ask a small favor to see what the code would be with a few changes?
The table's name is actually productSection
The ID field actually named idProductSection
The Title field is title
The Parent field is actually named idParent
I know I've got a lot to learn. I'm getting somewhat confused with the "ID" and "@ID" sections... not sure when to use what, and how they differ.
Thank you, once again.
-
Never mind... got it... thanks a million! You've saved me hours of work!
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply