convert sql tables with parent-child keys of a nested structure into a nested xml file

[Hazz]

I have 5 tables in SQL Server. Each with the following design and a sample
chain of the relationships from the root (WRL - World)
UUS is the 'Code' of the first table and it is the 'Parent' value of the
second table, etc.

Parent varchar 3
Name varchar 60
Code varchar 3

WRL United States UUS <- UUS California UCA <- UCA North Coast UNC <- UNC
Sonoma County USO <- USO Russian River Valley URR

If I have 6 tables with these relationships from root to these vineyard
appellation locations, how could I convert this nested, hiearchical data
from SQL Server tables into a nested XML file? I will be using the XML
file as input for a Winforms Treeview display of this data.
Thank you! -greg

ps. I just saw this newsgroup after posting to SQL Server XML newsgroup.

[Hazz]

From what I have read since I posted this about FOR XML EXPLICIT, I think it
is time for me to step into the comfort of SQL Server 2005 and give it a
test ride. Until then what I did was this. I built the Treeview after
creating one table as per
http://www.wwwcoder.com/main/parenti...8/default.aspx
then converted that recursively produced treeview node structure into the
necessary nested xml per Derek Harmon's excellent C# methods which I
massaged into vb.net for this app.
-Greg Hazzard

Here is Derek's code since I can't send a link to it;

using System;
using System.IO;
using System.Windows.Forms;
using System.Xml;

// . . .

private void SaveNode( XmlElement eNodes, TreeNodeCollection tnc)
{
foreach( TreeNode n in tnc)
this.SaveNode( eNodes, n);
}

private void SaveNode( XmlElement eNodes, TreeNode n )
{
XmlDocument nodeFactory = eNodes.OwnerDocument;
XmlElement child = nodeFactory.CreateElement( "Node");
XmlAttribute text = nodeFactory.CreateAttribute( "Text");
text.Value = n.Text;
child.Attributes.Append( text);
if ( null != n.Nodes && n.Nodes.Count > 0 )
{
XmlElement grandchildren = nodeFactory.CreateElement( "Nodes");
child.AppendChild( grandchildren);
this.SaveNode( grandchildren, n.Nodes);
}
eNodes.AppendChild( child);
}

private void btnSave_Click(object sender, System.EventArgs e)
{
XmlDocument doc = new XmlDocument( );
XmlElement eDoc = doc.CreateElement( "TreeView");
if ( null != this.treeView1.Nodes && this.treeView1.Nodes.Count > 0 )
{
XmlElement eNodes = doc.CreateElement( "Nodes");
this.SaveNode( eNodes, this.treeView1.Nodes);
eDoc.AppendChild( eNodes);
}
doc.AppendChild( eDoc);
XmlTextWriter sink = new XmlTextWriter( "../../tree.xml",
System.Text.Encoding.UTF8);
try
{
doc.WriteTo( sink);
sink.Flush( );
}
finally
{
sink.Close( );
}
}