Generation of xml files via recursion (of self joined table) not yielding desired hierarchy

Question

I have the task of generating XML files from data populated in a self-joined table; all the XML files must conform to the hierarchy as defined in the self join.

I have written some recursive code to achieve this with some success but my logic never seems to display the hierarchies correctly; some parent node always blocks the display as ParentNodes and as Child nodes - without descendants.

For instance, I have the following ideal XML structure:

 <?xml version="1.0" encoding="utf-8"?>  
 <MYREPORT>  
   <HEADER>  
    <HEADER_ID></HEADER_ID>  
    <HEADER_DESC></HEADER_DESC>  
    <HEADER_CODE></HEADER_CODE>  
    <HEADER_NAME></HEADER_NAME>  
    <HEADER_DATE></HEADER_DATE>  
    <HEADER_DATE2></HEADER_DATE2>  
    <AS_AT></AS_AT>  
  </HEADER>  
  <BODY>   
    <ITEMS_INFO>  
      <ITEM_CODE></ITEM_CODE>  
      <ITEM_DESC></ITEM_DESC>  
      <AMOUNT></AMOUNT>  
    </ITEMS_INFO>  
  </BODY>  
</MYREPORT>

With data being fed from the following self-joined table (primary key: ElementId, foreign key: ParentId)

ElementId   ElementName         ParentId    ElementLevel 
1           MYREPORT            NULL            0  
2       HEADER              1           1  
3       HEADER_ID           2           2  
4       HEADER_DESC         2           2  
5       HEADER_CODE         2           2  
6       HEADER_NAME         2           2  
7       HEADER_DATE         2           2  
8       HEADER _DATE2           2           2  
9       AS_AT               2           2  
10      BODY                1           1  
12      ITEMS_INFO          10          2  
13      ITEM_CODE           12          3  
14      ITEM_DESC           12          3  
15      AMOUNT              12          3

But I keep on getting the following XML output where the <ITEMS_INFO></ITEMS_INFO> displays as a parentNode block (which is correct) and also a childNode - without descendants (which is wrong)

<?xml version="1.0" encoding="utf-8"?>
<MYREPORT>
  <HEADER>
    <HEADER_ID>8:564</HEADER_ID>
    <HEADER_DESC>9:564</HEADER_DESC>
    <HEADER_CODE>10:564</HEADER_CODE>
    <HEADER_NAME>11:564</HEADER_NAME>
    <HEADER_DATE>12:564</HEADER_DATE>
    <HEADER_DATE2>13:564</HEADER_DATE2>
    <AS_AT>14:564</AS_AT>
 </HEADER>
 <BODY>
    <ITEMS_INFO>
      <ITEM_CODE>17:737</ITEM_CODE>
      <ITEM_DESC>18:737</ITEM_DESC>
      <AMOUNT>19:737</AMOUNT>
    </ITEMS_INFO>
    <ITEMS_INFO>20:737</ITEMS_INFO>
  </BODY>
</MYREPORT>

Please review my recursion code for any logic loopholes and how I can best achieve my ideal XML hierarchy

Private xmlWriter As New XmlTextWriter(My.Settings.xmlPath & "Rep.xml", System.Text.Encoding.UTF8)
Sub Main()

    Try
        Dim ds As DataSet = SqlHelper.ExecuteDataset(My.Settings.SqlUserRoleServices, CommandType.StoredProcedure, "getTest")
        ds.Tables(1).Columns.Add("isParsed", GetType(System.Int16))
        ds.Tables(1).AcceptChanges()
        Dim dr As DataRelation = New DataRelation("ElementSelf", ds.Tables(1).Columns("ElementId"), ds.Tables(1).Columns("ParentId"), True)
        ds.Relations.Add(dr)

        xmlWriter.Formatting = Formatting.Indented
        For Each StartRow As DataRow In ds.Tables(0).Rows
            If StartRow.IsNull("ParentId") Then
                xmlWriter.WriteStartDocument()
                xmlWriter.WriteStartElement(StartRow("ElementName"))
            End If
        Next

        For Each parentRow As DataRow In ds.Tables(1).Rows
            If (Not parentRow.IsNull("ParentId")) And parentRow.IsNull("IsParsed") Then
                parentRow("IsParsed") = 1
                doRecursion(parentRow, dr, xmlWriter)
                parentRow.AcceptChanges()
            End If
        Next

        xmlWriter.WriteEndElement()
        xmlWriter.WriteEndDocument()
        xmlWriter.Flush()
        xmlWriter.Close()
    Catch ex As Exception
        Console.WriteLine(ex.Message)
        Console.ReadKey()
    End Try
End Sub

Private Sub doRecursion(ByRef parentRow As DataRow, ByRef dr As DataRelation, ByRef xmlwriter As XmlTextWriter)
    Dim children As DataRow() = parentRow.GetChildRows(dr)

    If children.Any Then
        xmlwriter.WriteStartElement(parentRow("ElementName").ToString)

        For Each brow As DataRow In children
            If brow("IsParsed") Is DBNull.Value Then
                brow("IsParsed") = 1
                brow.AcceptChanges()
                doRecursion(brow, dr, xmlwriter)
                xmlwriter.WriteElementString(brow("ElementName"), Date.Now.Second & ":" & Date.Now.Millisecond)
                System.Threading.Thread.Sleep(1000)
                brow.AcceptChanges()
                parentRow.AcceptChanges()
            Else
                Exit For
            End If
        Next
        xmlwriter.WriteEndElement()
    Else
        Return
    End If
End Sub

Thanks.

Generation of xml files via recursion (of self joined table) not yielding desired hierarchy

0 Answers0