Click here to Skip to main content
15,029,188 members
Articles / Web Development / ASP.NET
Posted 8 Apr 2003


85 bookmarked

Dynamically Populating a Microsoft Internet Explorer TreeView Web Control from a Database using SQL Server 2000 & XML Explicit

Rate me:
Please Sign up or sign in to vote.
4.68/5 (32 votes)
20 May 2003CPOL5 min read
How to build an Internet Exporer TreeView Web Controls node list dynamically using SQLXML and the Explicit mode


Microsoft has made available the Internet Explorer Web controls which are not included in the framework by default. The assembly includes several controls one of which is the TreeView control which is discussed in this article. I will not be discussing how to use the control other than dynamically loading it with nodes using SQL Server's “Explicit mode”, and having each of the nodes' NavigateUrl attribute set to call a JavaScript function. I shall also not be going into any depth of discussion on SQLXML, as it is way beyond the scope of the article. I do however provide links to some further information on the topic. I shall however discuss SQLXML in a bit more depth in a future article “DataBinding with SQLXML”. See the Microsoft documentation for further programming of the TreeView control.

Where to Find the IE Web Controls Download

Whilst it can be very handy to bind an XML document to the TreeView control, I often face the requirement to do this on the fly, to accommodate dynamic changes to the TreeView's underlying data structure as required by the most current business model. One way to achieve this dynamic output is to use SQL Server's built in XML support in combination with the TreeNodeSrc attribute of the TreeView Control. For example, recently I needed to provide a known and commonly used hierarchical diagnosis path for help desk operators to select whilst servicing a customer. The output of this looked something like this:


Writing the Query to Return XML

SQL Server 2000 provides excellent XML support, providing XML output from SQL Queries using ‘Modes’ such as explicit, Auto and Raw. Our example requires the use of the explicit mode to produce a format which MS calls the universal table, which will give us the output we require. For more on returning XML from TSQL queries see: SQL Server Books on Line index, keyword XML, EXPLICIT, or:

I chose to represent the data recursively in the database using a single reflexive table to store the hierarchies. Each row fldDiagnosisParentId references another row fldDiagnosisId except for those rows where fldTierNumber is equal to a value of 1. Run the attached download SQLXML file to produce this table and fill it with data.

Sample screenshot

Run the attached download SQLXML file to produce this table and fill it with data.

Sample screenshot

Next, we need the query to produce the output desired.

create procedure spReflexiveNodesText
                1 as Tag,
                0 as Parent,
                null as [TREENODES!1!text],
                null as [treenode!2!text],
                null as [treenode!2!NavigateUrl],
                null as [treenode!3!text],
                null as [treenode!3!NavigateUrl],
                null as [treenode!4!text],
                null as [treenode!4!NavigateUrl]
                2 as Tag,
                1 as Parent,
                fldDiagnosisLabel as [treenode!2!text],
                'Javascript:FillBox("' + 
                     cast(fldDiagnosisId as varchar(10)) + '")'
                     as [treenode!2!NavigateUrl],
                null as [treenode!3!text],
                null as [treenode!3!NavigateUrl],
                null as [treenode!4!text],
                null as [treenode!4!NavigateUrl]
                fldTierNumber = 1
                3 as Tag,
                2 as Parent,
'Javascript:FillBox("' + cast(t1.fldDiagnosisId as varchar(10)) + '")',
                t2.fldDiagnosisLabel as [treenode!3!text],
                'Javascript:FillBox("' + 
                cast(t2.fldDiagnosisId as varchar(10)) + '")'
                as [treenode!3!NavigateUrl],
                tblDiagnosis as t1
inner join
                tblDiagnosis as t2
                t1.fldDiagnosisId = t2.fldDiagnosisParentId
                t1.fldTierNumber = 1
                t2.fldTierNumber = 2
                4 as Tag,
                3 as Parent,
'Javascript:FillBox("' + cast(t1.fldDiagnosisId as varchar(10)) + '")',
'Javascript:FillBox("' + cast(t2.fldDiagnosisId as varchar(10)) + '")',
                t3.fldDiagnosisLabel as [treenode!4!text],
                'Javascript:FillBox("' + 
                         cast(t3.fldDiagnosisId as varchar(10)) + '")'
                         as [treenode!4!NavigateUrl]
                tblDiagnosis as t1
inner join
                tblDiagnosis as t2
                t1.fldDiagnosisId = t2.fldDiagnosisParentId
inner join
                tblDiagnosis as t3
                t2.fldDiagnosisId = t3.fldDiagnosisParentId
                t1.fldTierNumber = 1
                t2.fldTierNumber = 2
                t3.fldTierNumber = 3
order by
                xml explicit

If you run this query in query analyzer and remove the for xml explicit directive, you will get what MS calls a universal table output (which can be handy for debugging). If you run the query with the for xml explicit directive, the output will be a long XML string like that listed below. The output listed highlights the fact that our query in this instance is producing the required XML structure required by the TreeView control and has taken advantage of the treenode elements NavigateUrl attribute which will call a JavaScript function in the page containing the TreeView.

<treenode text="Modem" NavigateUrl="Javascript:FillBox("3")">
<treenode text="Cable" NavigateUrl="Javascript:FillBox("7")">
<treenode text="Broken" NavigateUrl="Javascript:FillBox("11")"/>
<treenode text="Monitor" NavigateUrl="Javascript:FillBox("4")">
<treenode text="Fuzzy" NavigateUrl="Javascript:FillBox("8")">
<treenode text="Driver" NavigateUrl="Javascript:FillBox("12")"/>
<treenode text="PC" NavigateUrl="Javascript:FillBox("1")">
<treenode text="Ram" NavigateUrl="Javascript:FillBox("5")">
<treenode text="Faulty" NavigateUrl="Javascript:FillBox("9")"/>
<treenode text="Printer" NavigateUrl="Javascript:FillBox("2")">
<treenode text="Cartridges" NavigateUrl="Javascript:FillBox("6")">
<treenode text="Empty" NavigateUrl="Javascript:FillBox("10")"/>

Note: There are as many self joins required as there are tier levels, which is not exactly dynamic, meaning that if you add a tier to the data in the table, then you need to change the query. This can be overcome by first discovering the max() tier value found in the table and building a SQL statement up in a string and then executing the statement contained therein using the sp_executesql system stored procedure. The first select statement in the Union query is required to give us the root node “TREENODES” which is required by the TreeView Control. The text attributes of each treenode element is what provides each of the TreeView node's individual display labels and the NaviateUrl is commonly used to navigate to another HTTP location or in this instance passing control to a JavaScript function to produce some further processing.

For more information on XML modes available in SQL Server 2000, check out this link. This link provides a sample chapter of the book “Programming Microsoft® SQL Server™ 2000 with XML” by Graeme Malcolm and published by the MS press.

Setting Up SQL Server for Access through a URL

The next step in making the query work for us is setting up a SQL Server 2000 virtual directory that will allow us to call the stored procedure using a URL string. When SQL Server is setup, it should configure a menu item in its start menu folder labeled “Configure SQL XML support in IIS”.

IIS Virtual Directory

Once this is complete, you will be able to access the stored procedure from Internet Explorer using the URL: http://localhost/TreeSqlXml?sql=execute+spReflexiveNodesText.

For more information on how to set up the virtual directory, see: SQL Server Books or try MSDN website.

For more on how to set up SQL Server for querying a database with a URL string see: Books on Line and use index keyword ‘XML, executing stored procedures at URL’ or try MSDN website.

The ASPX page's HTML code.

<!DOCTYPEHTMLPUBLIC"-//W3C//DTD HTML 4.0 Transitional//EN">
<metaname="GENERATOR"Content="Microsoft Visual Studio 7.0">
<metaname="vs_defaultClientScript" content="JavaScript">
/* The Function specified in the output
   of the stored procedure 
   SEE the NavigateUrl attribute
   Of each treenode element  */
function FillBox(val)
       <formid="ReflexiveTreeNodes" method="post" runat="server">
       <!--Note the URL string used as the value of the TreeNodeSrc -->
       style="Z-INDEX: 101; LEFT: 6px; POSITION: absolute; TOP: 8px"

NOTE: Remember to reference the Microsoft.Web.UI.WebControls.dll in your project and include the URL to the stored procedure as the value for your TreeView tag's TreeNodeSrc attribute.


Choose the most appropriate caching policy for your page based on the expected frequency of change to underlying database table and whether or not any parameters are passed from the application to the stored procedure. Bear in mind that the stored procedure will fetch data from the database or cache, dependant on whether the request for data is already cached.


It is also worth mentioning that the TreeView control can also be populated by passing the fully qualified path of an XML file to the TreeNodeSrc attribute.



This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


About the Author

Simon Segal
Chief Technology Officer
Australia Australia
Simon Segal resides in Melbourne Australia, is a certified MCAD, MCSD, MCDBA, MCSE, MCST BizTalk Specialist and has been working in the Software Development industry for some 10 years now. His key area of interest are distributed systems / SOA built with Microsoft technologies.

Comments and Discussions

GeneralCopying a tree view into gmail compose box Pin
rockyurock27-Feb-10 7:20
Memberrockyurock27-Feb-10 7:20 
GeneralFind me here.... Pin
Simon Segal20-Jun-08 21:40
MemberSimon Segal20-Jun-08 21:40 
GeneralOpensource PHP DBTreeView Pin
Rodolphe Cardon20-Jul-07 10:08
MemberRodolphe Cardon20-Jul-07 10:08 
QuestionWhat about n-tier relationship Pin
hp912-Oct-06 0:05
Memberhp912-Oct-06 0:05 
GeneralGot error message ! Pin
KhinLLK15-Aug-06 21:49
MemberKhinLLK15-Aug-06 21:49 
GeneralMicrosoft.Web.UI.WebControls.dll Pin
Amir K15-May-06 12:27
MemberAmir K15-May-06 12:27 
GeneralRe: Microsoft.Web.UI.WebControls.dll Pin
Simon Segal5-May-06 12:52
MemberSimon Segal5-May-06 12:52 
GeneralRe: Microsoft.Web.UI.WebControls.dll Pin
Amir K15-May-06 20:18
MemberAmir K15-May-06 20:18 
GeneralRe: Microsoft.Web.UI.WebControls.dll Pin
Simon Segal6-May-06 1:47
MemberSimon Segal6-May-06 1:47 
Questionhow to find offsetX and offsetX of treenode Pin
Anonymous12-Oct-05 6:26
MemberAnonymous12-Oct-05 6:26 
QuestionUpdating the tree back into database Pin
Member 217302422-Sep-05 18:20
MemberMember 217302422-Sep-05 18:20 
AnswerRe: Updating the tree back into database Pin
Anonymous23-Sep-05 1:32
MemberAnonymous23-Sep-05 1:32 
GeneralRe: Updating the tree back into database Pin
Member 217302423-Sep-05 14:09
MemberMember 217302423-Sep-05 14:09 
GeneralNew Download Location for Web Control Download Pin
Simon Segal8-Jun-04 21:51
MemberSimon Segal8-Jun-04 21:51 
GeneralIE TreeView Control Pin
chintan19791-Jun-04 18:00
Memberchintan19791-Jun-04 18:00 
GeneralRe: IE TreeView Control Pin
Simon Segal8-Jun-04 21:49
MemberSimon Segal8-Jun-04 21:49 
QuestionHow can I get rid of the backgroud colour of the first node? Pin
pllms13-May-04 15:48
Memberpllms13-May-04 15:48 
QuestionCan I use TreeView Web Control in Dreamweaver? Pin
pllms12-May-04 23:09
Memberpllms12-May-04 23:09 
AnswerRe: Can I use TreeView Web Control in Dreamweaver? Pin
Simon Segal12-May-04 23:17
MemberSimon Segal12-May-04 23:17 
GeneralRe: Can I use TreeView Web Control in Dreamweaver? Pin
ravijikijai1-Feb-06 3:16
Memberravijikijai1-Feb-06 3:16 
GeneralAdding data to treeview in Windows Forms Pin
sasijrao14-Apr-04 4:12
Membersasijrao14-Apr-04 4:12 
GeneralSetting a node's tag property Pin
MSDevMan25-Mar-04 7:30
MemberMSDevMan25-Mar-04 7:30 
GeneralRe: Setting a node's tag property Pin
Simon Segal25-Mar-04 12:43
MemberSimon Segal25-Mar-04 12:43 
GeneralRe: Setting a node's tag property Pin
MSDevMan26-Mar-04 9:51
MemberMSDevMan26-Mar-04 9:51 
GeneralRe: Setting a node's tag property Pin
Simon Segal30-Mar-04 23:43
MemberSimon Segal30-Mar-04 23:43 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.