Create XML from Datatable with Parent, Child and Grandchild nodes

I have been asked to create a treeview on some data that I am having to pull back into a datatable from stored procedure

the issue that I have is while the data from the stored proc that I am working with is indented and likes like a treeview hierarchy it needs to be presented in an ASP.Net Webform.

The Data that gets returned looks something like this.

Object  | Type | Level | Comments | Parent | Child
Dave     | WH   | 1        | comment  | root   | null
Simon   | WH   | 1        | comment  | root   | Fortnum
Simon   | WH   | 1        | comment  | root   | Mason
Tim        | WH   | 1        | comment  | root   | null
wallace | WH   | 2        | comment  | Simon  | null
Mason  | WH   | 2        | comment  | Simon  | Mouse
Mouse  | WH   | 3        | comment  | Mason  | null

then for examples, I would want to show


as a tree \ branch with the parent, child, grand child, but in some instances there may be a great great grandchild (4th) level.

The code I have been asked to work with is VB.Net and this code gets me the object and comments as a parent and child, but I need parent, children, grandchildren and great grandchildren.

For Each row As DataRow In dt.Rows
            node = Searchnode(row.Item(4).ToString(), TreeView1)
            If node IsNot Nothing Then
                subNode = New TreeNode(row.Item(3).ToString())
                node = New TreeNode(row.Item(0).ToString())
                subNode = New TreeNode(row.Item(3).ToString())
            End If


Open in new window

What I would like help with is either turning this into XML so I can then do a simple read into the treeview from the XMLDataSource or Do this direct from the Datatable using LINQ maybe.

Ive never worked with treeviews before, or had to try and convert data to make one work So any and all help much appreciated.

I dont mind answers in C# as this is my preference but can work with both VB ad C#
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

ste5anSenior DeveloperCommented:
To create a XML, you need to sort your data by path to get that tree-like hierarchical structure. This is no difference to the piece of code you already have.

So what you need to do is to sort it by path.

When your data comes from SQL Server than you may consider using a recursive CTE to offload the sorting to the database.

Otherwise I would add an additional column to your DataTable to store the path and calculate it once when loading the data. This allows to separate the path handling from the tree loading code in a manner that your tree loading code can now be serialized and you don't need to search and move in the tree or DataTable.
SimonPrice3376Author Commented:
Can you give me an example of what you mean?
ste5anSenior DeveloperCommented:
When working with an adjacency list you need always to search an locate the next element. Either in the list or in the UI representation.

Thus some clever minds have invented the path concept. E.g. in plain T-SQL:

      ObjectName VARCHAR(255) NOT NULL
                              PRIMARY KEY ,
      TypeCode VARCHAR(255) NOT NULL ,
      CommentText VARCHAR(MAX) NULL ,
      ParentName VARCHAR(255) NULL

VALUES  ( 'Dave', 'WH', 'comment', NULL ),
        ( 'Simon', 'WH', 'comment', NULL ),
        ( 'Tim', 'WH', ' comment', NULL ),
        ( 'wallace', 'WH', 'comment', 'Simon' ),
        ( 'Mason', 'WH', 'comment', 'Simon' ),
        ( 'Mouse', 'WH', 'comment', 'Mason' );

WITH    Hierarchy
          AS ( SELECT   A.ObjectName ,
                        A.TypeCode ,
                        A.CommentText ,
                        A.ParentName ,
                        A.ObjectName AS RootObjectName ,
                        0 AS LevelNumber ,
                        CAST('\\' + A.ObjectName + '\' AS VARCHAR(MAX)) AS [PathText]
               FROM     @Sample A
               WHERE    A.ParentName IS NULL
               UNION ALL
               SELECT   C.ObjectName ,
                        C.TypeCode ,
                        C.CommentText ,
                        C.ParentName ,
                        P.RootObjectName ,
                        P.LevelNumber + 1 ,
                        P.PathText + C.ObjectName + '\'
               FROM     Hierarchy P
                        INNER JOIN @Sample C ON P.ObjectName = C.ParentName
    SELECT  H.ObjectName ,
            H.TypeCode ,
            H.CommentText ,
            H.ParentName ,
            H.RootObjectName ,
            H.LevelNumber ,
			REPLICATE(' ', H.LevelNumber * 4) + H.ObjectName
    FROM    Hierarchy H
    ORDER BY H.PathText ASC;

Open in new window

RootObjectName, LevelNumber and PathText are calculated columns. This can be done as above in T-SQL or on the DataTable.

Now we have an orderd list. Thus we can add an tree item, move to the next row in the DataTable and add another tree item dependend on the path. This allows use to create the entire tree by only reading every row in the DataTable once and without lookup of any already added tree node.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
C++ 11 Fundamentals

This course will introduce you to C++ 11 and teach you about syntax fundamentals.

SimonPrice3376Author Commented:
That SQL returns no results.
ste5anSenior DeveloperCommented:
Of course it does:

Did you run the entire batch or did you adopt it to your data?
SimonPrice3376Author Commented:
When I ran the exact sql you posted it didn't give me any results
ste5anSenior DeveloperCommented:
What SQL Server version? How do you run it?

As it is a plain T-SQL sample, you should run it in SSMS.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today

From novice to tech pro — start learning today.