Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2002-10-04 : 10:02:57
|
| PETER writes "Hi, I have heirachical data that I need to expand to abitrary depths in SQL SErver 7.0. I have been successful adopting the code you have from webguru22 at http://www.sqlteam.com/item.asp?ItemID=1602 on this site. However, I would now like to get the data back as an xml recordset (Not just display in using print), that expresses the heirachy. I would like the data to look like :-<Rowset> <Row id="1" name="parent1" parentID="0"> <Row id="4" name="childofparent1" parentID="1"> <Row id="5" name="GrandChildofparent1" parentID="4"> </Row> </Row> </Row> <Row id="2" name="parent2" parentID="0"> <Row id="3" name="Childofparent2" parentID="2"> </Row> </Row></Rowset>I am having trouble getting sql server 7 to take the same recordset as one produced by the stored procedure at http://www.sqlteam.com/item.asp?ItemID=1602 to produce it in XML format. Can someone help please. Thanks in advance for the help." |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2002-10-04 : 10:07:51
|
| Unfortunately SQL 7.0 doesn't have the XML features that 2000 has. BUT, you can use ADO to grab a recordset and use the Save method to save it as XML. Unfortunately the XML it generates is a pretty crappy format, BUT you can write an XSL sheet to convert it to the format you wish.Also, the nesting of child elements can be helped by using the ADO SHAPE command. It builds hierarchical recordsets, and again you can save these to XML format and convert them. Check out these ADO sites, they have articles on SHAPE and saving ADO recordsets as XML:www.4guysfromrolla.comwww.15seconds.comwww.asp101.comwww.learnasp.comwww.aspalliance.com |
 |
|
|
|
|
|
Expanding Heirachies