designing a recursive function.

[rutledj]

I have the following data in this a table

ID ParentID Active Node
1 0 1 <RCExtRequest>?</RCExtRequest>
2 1 1 <Caller>?</Caller>
3 2 1 <VendorName>#OrgUserName#</VendorName>
4 2 1 <VendorPassword>#OrgPassword#</VendorPassword>
5 1 1 <RcopiaPracticeUsername>#ExternalID#</RcopiaPracticeUsername>
6 1 1 <Request>?</Request>
7 6 1 <SystemName>#ExternalPracticeID#</SystemName>
8 6 1 <Command>#Command#</Command>
9 6 1 <LastUpdateDate>#LastUpdateDate#</LastUpdateDate>



Assuming I have a sql query that pulls back all active nodes I want a function that will read a row, if the node contains a ? then it means there is a child node for that node, find the child node and insert it into the parent node.

so for node <RCExtRequest>?</RCExtRequest> it would have a child node of
<Caller>?</Caller> because the parentid of this node equals the id of the above node.


Then final outcome should look like this:

<RCExtRequest>
<Caller>
<VendorName>#OrgUserName#</VendorName>
<VendorPassword>#OrgPassword#</VendorPassword>
</Caller>
<RcopiaPracticeUsername>#ExternalID#</RcopiaPracticeUsername>
<Request>
<SystemName>#ExternalPracticeID#</SystemName>
<Command>#Command#</Command>
<LastUpdateDate>#LastUpdateDate#</LastUpdateDate>
</Request>
</RCExtRequest>

If changing the table layout would make this any easier then I could so that.

Thanks
Rut

 

[kulrom]

I have the following data in this a table

Is this XML data or relation database table?

 

[rutledj]

These are xml nodes stored in a database. The thought is to be able to store the nodes in a database so if the nodes change or more are added then vb can just build the xml request dynamically.