SQLDataSource binding to treeview control when using "FOR XML" on select

[Stan Spotts]

One of our associates wants to pull data in from SQL Server 2000 using a
"FOR XML" statement on his select that represents hierarchical data. He
thought it should be easy to bind a TreeView control to this using a
SqlDataSource, but said that it didn't work. Now he's wondering if he needs
some combination of a SqlDataSource and an XmlDataSource.

He's not newsgroup savvy, so I offered to post for him. I don't have his
source code, but if needed I'll ask him for it.

Is there a limitation that won't let him do this, or a trick that will?

Thanks,
--Stan

 

[Steven Cheng[MSFT]]

Hello Stan,

Thank you for posting in the MSDN newsgroup.

From your description, I understand one of your associates will use the
ASP.NET 2.0 TreeView control to populate some data from database, and the
data is of xml format which is retrieved from SQL Server database through
FOR XML clause. Currently you're wondering how to utlize the ASP.NET 2.0's
datasource control binding to associate the data from database to the
TreeView, correct?

Based on my understanding, since the TreeView control's XmlDocument
databinding is rely on the XmlDataSource, so we still have to use
XmlDataSource rather than SqlDataSource(SqlDatasource is focus on supplying
relational database data rather than XML based data). However,
XmlDataSource by default require us to supply a static xml data file, and
in your scenario, the xml data is retrieved from SQL Database through FOR
XML query, I think we need some code to programmatically get the xmlstream
from database and assign to to the XmlDataSource control. For example:

======code behind==============
Page_Load(object sender, EventArgs e)
{
XmlDataSource1.Data = GetXmlDoc().OuterXml;
}


protected XmlDocument GetXmlDoc()
{
SqlConnection conn = new SqlConnection(

WebConfigurationManager.ConnectionStrings["AdventureWorksConnectionString"].
ConnectionString
);

conn.Open();

string sql = "select CultureID, Name from Production.Culture FOR
XML AUTO, ROOT('root')";

SqlCommand comm = new SqlCommand(sql, conn);


XmlReader reader = comm.ExecuteXmlReader();


XmlDocument doc = new XmlDocument();
doc.Load(reader);

reader.Close();
conn.Close();


return doc;
}
====================

so it is the "Data" property of the XmlDatasource control we use to
dynamically populate the XML data. And in the aspx page, we still bind the
TreeView control with the XmlDataSource(XmlDataSource1):

=======================
<form id="form1" runat="server">
<div>
<asp:XmlDataSource ID="XmlDataSource1" runat="server" >
</asp:XmlDataSource>

</div>
<asp:TreeView ID="TreeView1" runat="server"
DataSourceID="XmlDataSource1" >
<DataBindings >
<asp:TreeNodeBinding DataMember="Production.Culture"
TextField="Name" ValueField="CultureID" />
</DataBindings>
</asp:TreeView>

</form>
===================

Just some of my consideration. Also, you can also programmatically use
System.Xml namespace's classes to query XmlNode List and directly bind to
the TreeView(without any DataSource control).

Please feel free to post here if you have any other ideas or concerns.

Regards,

Steven Cheng
Microsoft MSDN Online Support Lead


==================================================

When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.

==================================================


This posting is provided "AS IS" with no warranties, and confers no rights.



Get Secure! www.microsoft.com/security
(This posting is provided "AS IS", with no warranties, and confers no
rights.)

 

[Stan Spotts]

Perfect response, Steven - that helped him out.

Thanks,
--Stan

Hello Stan,

Thank you for posting in the MSDN newsgroup.

From your description, I understand one of your associates will use the
ASP.NET 2.0 TreeView control to populate some data from database, and the
data is of xml format which is retrieved from SQL Server database through
FOR XML clause. Currently you're wondering how to utlize the ASP.NET 2.0's
datasource control binding to associate the data from database to the
TreeView, correct?

Based on my understanding, since the TreeView control's XmlDocument
databinding is rely on the XmlDataSource, so we still have to use
XmlDataSource rather than SqlDataSource(SqlDatasource is focus on
supplying
relational database data rather than XML based data). However,
XmlDataSource by default require us to supply a static xml data file, and
in your scenario, the xml data is retrieved from SQL Database through FOR
XML query, I think we need some code to programmatically get the xmlstream
from database and assign to to the XmlDataSource control. For example:

======code behind==============
Page_Load(object sender, EventArgs e)
{
XmlDataSource1.Data = GetXmlDoc().OuterXml;
}


protected XmlDocument GetXmlDoc()
{
SqlConnection conn = new SqlConnection(

WebConfigurationManager.ConnectionStrings["AdventureWorksConnectionString"].
ConnectionString
);

conn.Open();

string sql = "select CultureID, Name from Production.Culture FOR
XML AUTO, ROOT('root')";

SqlCommand comm = new SqlCommand(sql, conn);


XmlReader reader = comm.ExecuteXmlReader();


XmlDocument doc = new XmlDocument();
doc.Load(reader);

reader.Close();
conn.Close();


return doc;
}
====================

so it is the "Data" property of the XmlDatasource control we use to
dynamically populate the XML data. And in the aspx page, we still bind the
TreeView control with the XmlDataSource(XmlDataSource1):

=======================
<form id="form1" runat="server">
<div>
<asp:XmlDataSource ID="XmlDataSource1" runat="server" >
</asp:XmlDataSource>

</div>
<asp:TreeView ID="TreeView1" runat="server"
DataSourceID="XmlDataSource1" >
<DataBindings >
<asp:TreeNodeBinding DataMember="Production.Culture"
TextField="Name" ValueField="CultureID" />
</DataBindings>
</asp:TreeView>

</form>
===================

Just some of my consideration. Also, you can also programmatically use
System.Xml namespace's classes to query XmlNode List and directly bind to
the TreeView(without any DataSource control).

Please feel free to post here if you have any other ideas or concerns.

Regards,

Steven Cheng
Microsoft MSDN Online Support Lead


==================================================

When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.

==================================================


This posting is provided "AS IS" with no warranties, and confers no
rights.



Get Secure! www.microsoft.com/security
(This posting is provided "AS IS", with no warranties, and confers no
rights.)

 

[Steven Cheng[MSFT]]

You're welcome Stan,

Glad to be of assistance!

Regards,

Steven Cheng
Microsoft MSDN Online Support Lead


==================================================

When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.

==================================================


This posting is provided "AS IS" with no warranties, and confers no rights.



Get Secure! www.microsoft.com/security
(This posting is provided "AS IS", with no warranties, and confers no
rights.)

 

[Bob Lawhorn]

Steven Cheng, when I attempt to use the code you provided I receive a "This
document already has a 'DocumentElement' node." error message on the
doc.Load(reader); instruction. It appears that this is related to the fact
that I am using the 'For XML Explicit' option in my stored procedure. Can
you provide any suggestions in how to modify this code to allow it to process
correctly?
- Bob

Hello Stan,

Thank you for posting in the MSDN newsgroup.

From your description, I understand one of your associates will use the
ASP.NET 2.0 TreeView control to populate some data from database, and the
data is of xml format which is retrieved from SQL Server database through
FOR XML clause. Currently you're wondering how to utlize the ASP.NET 2.0's
datasource control binding to associate the data from database to the
TreeView, correct?

Based on my understanding, since the TreeView control's XmlDocument
databinding is rely on the XmlDataSource, so we still have to use
XmlDataSource rather than SqlDataSource(SqlDatasource is focus on supplying
relational database data rather than XML based data). However,
XmlDataSource by default require us to supply a static xml data file, and
in your scenario, the xml data is retrieved from SQL Database through FOR
XML query, I think we need some code to programmatically get the xmlstream
from database and assign to to the XmlDataSource control. For example:

======code behind==============
Page_Load(object sender, EventArgs e)
{
XmlDataSource1.Data = GetXmlDoc().OuterXml;
}


protected XmlDocument GetXmlDoc()
{
SqlConnection conn = new SqlConnection(

WebConfigurationManager.ConnectionStrings["AdventureWorksConnectionString"].
ConnectionString
);

conn.Open();

string sql = "select CultureID, Name from Production.Culture FOR
XML AUTO, ROOT('root')";

SqlCommand comm = new SqlCommand(sql, conn);


XmlReader reader = comm.ExecuteXmlReader();


XmlDocument doc = new XmlDocument();
doc.Load(reader);

reader.Close();
conn.Close();


return doc;
}
====================

so it is the "Data" property of the XmlDatasource control we use to
dynamically populate the XML data. And in the aspx page, we still bind the
TreeView control with the XmlDataSource(XmlDataSource1):

=======================
<form id="form1" runat="server">
<div>
<asp:XmlDataSource ID="XmlDataSource1" runat="server" >
</asp:XmlDataSource>

</div>
<asp:TreeView ID="TreeView1" runat="server"
DataSourceID="XmlDataSource1" >
<DataBindings >
<asp:TreeNodeBinding DataMember="Production.Culture"
TextField="Name" ValueField="CultureID" />
</DataBindings>
</asp:TreeView>

</form>
===================

Just some of my consideration. Also, you can also programmatically use
System.Xml namespace's classes to query XmlNode List and directly bind to
the TreeView(without any DataSource control).

Please feel free to post here if you have any other ideas or concerns.

Regards,

Steven Cheng
Microsoft MSDN Online Support Lead


==================================================

When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.

==================================================


This posting is provided "AS IS" with no warranties, and confers no rights.



Get Secure! www.microsoft.com/security
(This posting is provided "AS IS", with no warranties, and confers no
rights.)