convert sql tables with parent-child keys relating a nested structure into a nested x

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! -gregDepending on the complexity of the relationships and the XML you want, look
either into FOR XML AUTO (simple but limited) or FOR XML EXPLICIT (more
complex but powerful). If you have problems, there are people here that will
be able to help you for a good bottle :-).
Also, SQL Server 2005 will have a new FOR XML PATH mode that gives you
simplicity and power at the same time...
Best regards
Michael
"Hazz" <hazz@.nospameroosonic.net> wrote in message
news:eVYdakYMFHA.2580@.TK2MSFTNGP09.phx.gbl...
>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
>|||Michael,
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/parent...68/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.
I do like this idea of the bottle bartering process!! Thank you Michael for
validating what I was finding about SQL possibilities. -greg
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( );
}
}
****************************************
*********************
"Michael Rys [MSFT]" <mrys@.online.microsoft.com> wrote in message
news:umgl7SdMFHA.904@.tk2msftngp13.phx.gbl...
> Depending on the complexity of the relationships and the XML you want,
> look either into FOR XML AUTO (simple but limited) or FOR XML EXPLICIT
> (more complex but powerful). If you have problems, there are people here
> that will be able to help you for a good bottle :-).
> Also, SQL Server 2005 will have a new FOR XML PATH mode that gives you
> simplicity and power at the same time...
> Best regards
> Michael
> "Hazz" <hazz@.nospameroosonic.net> wrote in message
> news:eVYdakYMFHA.2580@.TK2MSFTNGP09.phx.gbl...
>