Click here to Skip to main content
Click here to Skip to main content

Hierarchical XML Data Builder

, 17 Oct 2007
Rate this:
Please Sign up or sign in to vote.
Data builder that builds XML from a SQL query in hierarchical format.

Introduction

Binding hierarchical data for menus and trees is a very common task these days. Binding sub nodes, when needed, makes a server round trip, or binding data with recursive binding degrades performance. We can achieve these in an easier way with this builder class.

Using the code

The HierarchicalXMLDataBuilder class builds the required XML data. Consider the scenario of ghe Employee table, which has a ManagerId that refers back to the EmployeeId of the same table. This builder will help in such scenarios. The method BuildMap returns the XML string as output. The parameters for the method are:

  1. DataSet
  2. Identity column
  3. Referring column
  4. Key value

The data has to be fetched once from the database and it has to be passed as a parameter for the method. Identity column and referring column are the columns which have the relations. Key value is the initial value which is used to generate the XML string.

Imports Microsoft.VisualBasic
Imports System.Xml
Imports System.Data

Public Class HierarchicalXMLDataBuilder

    Private ParentColumn As String
    Private ChildColumn As String
    Private NoOfColumns As Integer
    Private Data As DataSet
    Private vuData As DataView

    Public Function BuildMap(ByVal data As DataSet, _
           ByVal IdentityColumn As String, ByVal ReferenceColumn As String, _
           ByVal KeyValue As String) As String
        Me.ParentColumn = IdentityColumn
        Me.ChildColumn = ReferenceColumn
        Me.Data = data
        vuData = New DataView(data.Tables(0))
        NoOfColumns = data.Tables(0).Columns.Count
        Dim xml As String
        xml = BuildChildNodes(vuData, ParentColumn & " = " & KeyValue)
        Return xml
    End Function

    Private Function BuildChildNodes(ByVal vu As DataView, _
                     ByVal filter As String) As String
        Dim nodeBuilder As New StringBuilder(String.Empty)
        vu.RowFilter = filter
        Dim RowCounter As Integer = 0
        For RowCounter = 0 To vu.Count - 1
            nodeBuilder.Append("<Node")
            Dim ColCounter As Integer = 0
            For ColCounter = 0 To NoOfColumns - 1
                nodeBuilder.Append(" ")
                nodeBuilder.Append(Data.Tables(0).Columns(ColCounter).ColumnName)
                nodeBuilder.Append(" = """)
                nodeBuilder.Append(vu.Item(RowCounter)(ColCounter).ToString())
                nodeBuilder.Append(""" ")
            Next
            nodeBuilder.Append(" >")
            Dim vuData2 As New DataView(data.Tables(0))
            If vu.Count > RowCounter Then
                nodeBuilder.Append(BuildChildNodes(vuData2, _
                                   ChildColumn & " = " & _
                                   vu.Item(RowCounter)(ParentColumn)))
            End If
            nodeBuilder.Append("</Node>")
        Next
        Return nodeBuilder.ToString()
    End Function
End Class

We can use the builder as shown below. This creates the XML file and saves it in the application directory. We can then bind the XML to a treeview or a menu.

Dim XmlData As New HierarchicalXMLDataBuilder
Dim XmlString As String = XmlData.BuildMap(ds, "RowId", "ParentID", 1)
Dim doc As New System.Xml.XmlDocument()
doc.LoadXml(XmlString)
doc.Save(Server.MapPath("xmlfile.xml"))

The HTML:

<asp:treeview id="tv" runat="server" DataSourceID="XmlDataSource1" >
    <DataBindings>
        <asp:TreeNodeBinding DataMember="Node" 
            TextField="Data" ToolTipField="Data" />
    </DataBindings>
</asp:treeview>

<asp:XmlDataSource ID="XmlDataSource1" runat="server" 
    DataFile="xmlfile.xml"></asp:XmlDataSource>

The output:

ROWID       DATA              PARENTID
----------- ---------------------------------
1           MainMenuItem      NULL
2           MenuItem1         1
3           MenuItem2         1
4           MenuItem3         1
5           MenuItem4         1
6           MenuItem11        2
7           MenuItem12        2
8           MenuItem13        2
9           MenuItem21        3
10          MenuItem22        3
11          MenuItem41        5
12          MenuItem42        5
13          MenuItem43        5
14          MenuItem121       7
15          MenuItem122       7
16          MenuItem123       7
17          MenuItem131       8
18          MenuItem132       8
19          MenuItem221       10
20          MenuItem222       10
21          MenuItem2221      20
22          MenuItem2222      20
23          MenuItem22221     22
24          MenuItem222211    23
25          MenuItem222212    23

If the data is looks like this, the XML file generated will be:

<Node RowId="1" Data="MainMenuItem" ParentId="">
  <Node RowId="2" Data="MenuItem1" ParentId="1">
    <Node RowId="6" Data="MenuItem11" ParentId="2">
    </Node>
    <Node RowId="7" Data="MenuItem12" ParentId="2">
      <Node RowId="14" Data="MenuItem121" ParentId="7">
      </Node>
      <Node RowId="15" Data="MenuItem122" ParentId="7">
      </Node>
      <Node RowId="16" Data="MenuItem123" ParentId="7">
      </Node>
    </Node>
    <Node RowId="8" Data="MenuItem13" ParentId="2">
      <Node RowId="17" Data="MenuItem131" ParentId="8">
      </Node>
      <Node RowId="18" Data="MenuItem132" ParentId="8">
      </Node>
    </Node>
  </Node>
  <Node RowId="3" Data="MenuItem2" ParentId="1">
    <Node RowId="9" Data="MenuItem21" ParentId="3">
    </Node>
    <Node RowId="10" Data="MenuItem22" ParentId="3">
      <Node RowId="19" Data="MenuItem221" ParentId="10">
      </Node>
      <Node RowId="20" Data="MenuItem222" ParentId="10">
        <Node RowId="21" Data="MenuItem2221" ParentId="20">
        </Node>
        <Node RowId="22" Data="MenuItem2222" ParentId="20">
          <Node RowId="23" Data="MenuItem22221" ParentId="22">
            <Node RowId="24" Data="MenuItem222211" ParentId="23">
            </Node>
            <Node RowId="25" Data="MenuItem222212" ParentId="23">
            </Node>
          </Node>
        </Node>
      </Node>
    </Node>
  </Node>
  <Node RowId="4" Data="MenuItem3" ParentId="1">
  </Node>
  <Node RowId="5" Data="MenuItem4" ParentId="1">
    <Node RowId="11" Data="MenuItem41" ParentId="5">
    </Node>
    <Node RowId="12" Data="MenuItem42" ParentId="5">
    </Node>
    <Node RowId="13" Data="MenuItem43" ParentId="5">
    </Node>
  </Node>
</Node>

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

asithangae
Web Developer
India India
Hai, I am a .Net professional just started my career in this field. I like to code more on asp.net. In my spare time, l like to write article and write blogs. i love music and intrested in watching movies.
 
you can view my blog here.

Comments and Discussions

 
GeneralMy vote of 5 Pinmemberhassan_muet12-Feb-13 20:48 
Generali have error PinmemberMember 366830830-May-09 0:27 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Mobile
Web04 | 2.8.140821.2 | Last Updated 17 Oct 2007
Article Copyright 2007 by asithangae
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid