Creating XMl out of relational tables with deep hierarchy

Question

New to SQL Serer 2005.
I have an XSD out of which I created a database using open source utility. Now the tables generated are bit redundant, so some tables have been consolidated. The tables have a relational integrity and the hierarchy is bit deep.

My task is to create an XML out of the tables. Now If I pass the parent record id to the SQL server, What I would like to have is the whole tree under the parent record. Ideally this should match the XSD I originally used to create the tables in the first place.

My plan is to use VB.net with minimal coding on the client side. please let me know.

Answer 1

can you show us the sample of your xsd so we can see the structure.

---

Best Regards,
Melissa Suciadi

If you have found this post helpful, please click the 'Vote as Helpful' link (the green triangle and number on the top-left).

If this post answers your question, click the 'Mark As Answered' link below. It helps others who experience the same issue in future to find the solution.

Answer 2

I have used nested select for xml statements to return an xml document that conforms to the desired schema.

For more information about specifying both attribute-centric and element-centric XML with nested FOR XML queries, see FOR XML Query Compared to Nested FOR XML Query and Shaping XML with Nested FOR XML Queries .

---

/bac

Answer 3

Hope this article will help you: http://xml.sys-con.com/node/46178

--------------
StarWind Software developer ( http://www.starwindsoftware.com )