Parent-Child Tree path to xml

From a stored procedure on our SQL Server 2005 database, I currently get results for parent-child relationships like this:

11 > 7 > 2
11 > 7 > 8 > 5
11 > 7 > 8 > 6 > 2
etc...
(just to be clear: this is a single column output, so in other words: varchar fields)

Now, in my VB.NET application, I need to convert these hierarchical tree paths to xml format:

<node id="11">
  <node id="7">
    <node id="2" />
    <node id="8">
      <node id="5" />
      <node id="6">
        <node id="2" />
      </node>
    </node>
  </node>
</node>

I think you get the picture

What would be a quick and efficient way to accomplish this?

Any help with code (VB.NET or C#) would be greatly appreciated!

Thanks

There's two things you can do. You can create a custom object tree and then serialize that to XML in one go, or create an XML DOM object and populate that. Which you want depend on what else you're going to do with the data. I'm going to assume the XML DOM approach here.

The basic approach is to split the path string, iterate over the components, check if a node already exists and if not, add it.

Private Sub AddPath(ByVal doc As XmlDocument, ByVal path As String)
   Dim components() As String = path.Split(">"c)
   Dim elt As XmlElement = doc.DocumentElement

   For Each component As String In components
      Dim newElt As XmlElement = CType(elt.SelectSingleNode("node[@id='" & component.Trim() & "']"), XmlElement)
      If newElt Is Nothing Then
         newElt = doc.CreateElement("node")
         newElt.SetAttribute("id", component.Trim())
         elt.AppendChild(newElt)
      End If
      elt = newElt
   Next
End Sub
I'm sure there's a ton of ways to make that faster but this should get you started at least. You can then use it like this:
Dim doc As New XmlDocument
' Create XML declaration
doc.AppendChild(doc.CreateXmlDeclaration("1.0", "utf-8", Nothing))
' Create document element
doc.AppendChild(doc.CreateElement("nodes"))
AddPath(doc, "11 > 7 > 2")
AddPath(doc, "11 > 7 > 8 > 5")
AddPath(doc, "11 > 7 > 8 > 6 > 2")
Console.WriteLine(doc.OuterXml)

If you want to write it to a file but have it nicely indented and stuff, you can do this:

Dim settings As New XmlWriterSettings
settings.Indent = True
Using writer As XmlWriter = XmlWriter.Create("foo.xml", settings)
   doc.Save(writer)
End Using

Sven,

That is fantastic! Thank you so much, you really saved my day

I just implemented your solution in my application, and it works flawlessly!

I was messing aroung with strings and a custom generic tree class.
Needless to say, using XmlDocument with SelectSingleNode is so much neater, I guess I really have to learn XPath some day (still haven't got the time to delve into that stuff).

In VS2008 you also have the new XDocument / XElement / etc... classes, would these give me any specific benefits?

dotnetjunkie wrote:

In VS2008 you also have the new XDocument / XElement / etc... classes, would these give me any specific benefits?

XML literals and LINQ in VB9 make this code a heck of lot easier to read (and write too). Here's the quick example I cooked up:


Some things to note:

• Option Explicit is on. All the Dim's without types are still strongly typed thanks to type inference.
• The SingleOrDefault() LINQ extension method for IEnumerable(Of T) returns the first element of the list, null if there is none, or throws if it has more than one element (which can never happen in this example).
• Notice how easy it was to create the node and set its id attribute (using a syntax that ASP/ASP.NET developers should already be familiar with). It's much easier to see at a glance what this code is doing than all the method calls in the VB8 sample.
• XDocument.Save uses indenting by default, so this gives pretty output without the need for an XmlWriter.
• No need to learn XPath.
• XML literals are VB only. Take that, C#!

EDIT: Made AddPath an extension method for no good reason.