Designing a Stored procedure to create XML tree

Question

I need to write a Stored procedure in SQL server whose data returned will be used to generate a XML file.

My XML file to be in structure of

<root>
<ANode></ANode>
<BNode></BNode>
<CNode>
  <C1Node>
    <C11Node></C11Node>
    <C12Node></C12Node>
  </C1Node>
  <C2Node>
    <C21Node></C21Node>
    <C22Node></C22Node>
  </C2Node>
  <C3Node>
    <C31Node></C31Node>  
    <C32Node></C32Node>  
  </C3Node>  
</CNode>
</root>

My question is, in the stored procedure we can select values for ANode and BNode as a simple SELECT statement like

Select ANodeVal,BNodeVal from Table

But how to design the stored procedure to get records for the CNode which is a subtree which has 3 or more(dynamic) separate nodes in it for each record in addition to the normal ANode and BNode

Please help me in this desperate problem.

Answer 1

I wouldn't recommend doing this in a stored proc. If created in language such as C#/Python or Java will make the code unit testable and more maintainable.

Answer 2

If you are able to modify the database design, consider keeping each node as a record, instead of as a column (as the sample select statement would indicate).

For example, each row might include the following fields:

• RowId
• ParentRowId
• Name
• RowData

I'm assuming that you are passing the data to an application befcause you indicated that the returned data will be used to generate the XML. In which case the Stored Procedure would simply be a SELECT statement, leaving the formatting to the application.

Most implementations of XML engines should allow you to add child nodes to existing parent nodes. The XML is built in memory and then "exported" by whatever method necessary to get the desired final result.

Answer 3

See

    Nesting XML-returning scalar valued functions

Once you get the hang of the nesting, and are willing to write the number of scalar-valued functions necessary to construct the node segments from the bottom up (I wouldn't want lots of these laying around), then it's not so hard.