Designing a Stored Procedure to Create an XML Tree
I need to write a stored procedure in a SQL server whose data will be used to create an XML file.
My XML file will be in structure
<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 that in a stored procedure, we can select values for ANode and BNode as a simple SELECT statement like
Select ANodeVal,BNodeVal from Table
But how to create a stored procedure to get records for CNode which is a subtree that has 3 or more (dynamic) separate nodes in it for each record in addition to the normal ANode and BNode.
source to share
Cm
Nested scalar functions returning XML
Once you get the nesting hang and are ready to write the number of scalar-valued functions needed to plot node segments from the bottom up (I wouldn't want many of them lying around), then it's not that hard.
source to share
If you can change the design of the database, consider storing each node as a record rather than as a column (as the pattern select statement points out).
For example, each line might include the following fields:
- RowId
- ParentRowId
- Name
- ROWDATA
I am assuming that you are passing data to the application befcause specifying that the returned data will be used to generate the XML. In this case, the stored procedure will simply be a statement SELECT
, leaving the formatting in the application.
Most XML engine implementations should allow you to add child nodes to existing parent nodes. XML is built in memory and then "exported" in any way necessary to get the desired end result.
source to share