create xml file from sql server

[magmo]

Hi


I have a database that looks like this....

NodeID ParentNodeID Text NavigateUrl

1 books #
2 1 book 1 page.aspx
3 1 book 2 page.aspx
4 3 pockets page.aspx
5 4 book 3 page.aspx
6 4 book 4 page.aspx



I would like to create a xml file from this with the following layout...


<?xml version="1.0" encoding="ISO-8859-1"?>
<Nodes>
<TreeViewNode ID="4" Text="books">
<TreeViewNode ID="2" Text="book 1" NavigateUrl="page.aspx"></TreeViewNode>
<TreeViewNode ID="3" Text="book 2" NavigateUrl="page.aspx"></TreeViewNode>
</TreeViewNode>

<TreeViewNode ID="5" Text="pockets">
<TreeViewNode ID="5" Text="book 3" NavigateUrl="page.aspx"></TreeViewNode>
<TreeViewNode ID="6" Text="book 4" NavigateUrl="page.aspx"></TreeViewNode>
</TreeViewNode>
</Nodes>


I have no idea how to solve this, could anyone help me out here?


Regards

 

[jshurst]

I've never done that but I think you will need to use an XSLT transformation. Maybe this article will help (but it is super long). Maybe someone else will chime in here too...

http://support.microsoft.com/default.aspx?scid=kb;en-us;319180

 

[jbenson001]

I would post this in the SQL Server Programming Forum:

forum183

 

[bboffin]

Read the table into a DataTable using ADO.NET.

Create a New XmlDocument objectn with a single elemnt called Nodes.

You'll need to use a recursive routine to create the elements called, say, AddNode that takes one parameter of type DataRow and a second of type XmlElement.

Use the Select method of the table to get an array of datarows where NavigateURL is null

For Each Row in the array call AddNodes passing in the datarow and the document element of XmlDocument.

In AddNodes first create an XmlElement with a node name of TreeViewNode and add it as a child to the XmlElement passed in. Set its attributes.

Select all the nodes in the table with the same ParentID as this row's NodeID and call AddNode for each of these rows.

Something like this:

Dim mXmlDoc as New XmlDocument   ' module level variables
Dim mTable as New DataTable
.
.
.
.
Dim lConn as New SqlConnection
lConn.ConnectionString = ".............."
Dim lDA as new SqlDataAdapter
Dim lCmd as SqlCommand = lConn.CreateCommand()
With lCmd
    .CommandText = "SELECT * FROM MyTable"
    lDA.SelectCommand = lCmd
    lDA.Fill(mTable)
End With
mXmlDoc.LoadXml("<Nodes/>")
Dim lRows() as DataRow = mTable.Select("NavigateUrl is null")

For Each lRow as DataRow in lRows
   AddNode(lRow, mXmlDoc.DocumentElement)
Next

mXmlDoc.Save("....")
.
.
.
.
Private Sub AddNode(Row as DataRow, Elem as XmlElement)
Dim lElem as XmlElement = mXmlDoc.CreateElement("TreeViewNode")
Elem.AppendChild(lElem)
lElem.SetAttribute("ID", Row("NodeID").ToString)
lElem.SetAttribute("Text", Row("Text").ToString)
If not IsDBNull(Row("NavigateURL")) Then
    lElem.SetAttribute("NavigateURL", Row("NavigateURL").ToString)
End If
Dim lRows() As DataRow = mTable.Select("ParentID=" & Row("NodeID").ToString)
For Each lRow As DataRow In lRows
    AddNode(lRow, lElem)
Next
End Sub

This code is untested so there will be a few errors but it should give you a starting point.



Bob Boffin