Web Development

General discussion


Simplifying hierarchical data

By debate ·
In this week's Web Development Zone newsletter, do you find Phillip's example about simplifying hierarchical data with SQL 2000 and XML helpful? Share your comments with your peers.

This conversation is currently closed to new comments.

Thread display: Collapse - | Expand +

All Comments

Collapse -

Outputing the built XML?

by kylebanashek In reply to Simplifying hierarchical ...

I'm assuming that this SQL statement will be performed at the SQL 2000 tier in a sproc, so what type of output parameter can I use to pass the XML doc out to my server in order to access it?

Collapse -


by phil_perkins_1 In reply to Outputing the built XML?

The way that I've used this is to receive this data through a ADO Stream object. When you call the stored procedure (say, from VB or ASP), set the return as a stream object. The stream object has a ReadText method that will allow you to take the returned stream and put it into a String variable. This, however, requires that you use MDAC 2.6+.

Dim s As String
Dim conn As ADODB.Connection
Dim cmd As ADODB.Command
Dim strm As ADODB.Stream

Set conn = New ADODB.Connection
Set cmd = New ADODB.Command

conn.ConnectionString = "some connection string"

Set cmd.ActiveConnection = conn
cmd.CommandText = "SELECT * FROM Table FOR XML AUTO, ELEMENTS"
Set strm = cmd.Execute()

s = strm.ReadText(-1)


I just did this off the top of my head, so I hope it's all right.

Collapse -

Client side access

by tonylan In reply to Simplifying hierarchical ...

How would you bind the returned data to ie on the client side?

Collapse -

More pieces of the puzzle

by nlcapit In reply to Simplifying hierarchical ...

I found your article very intriguing, having had to deal with this problem of filtering repeating data using traditional ASP programming methods. However, the information would be MUCH more useful if you carried the process out from beginning to end. You introduce the possibilities and then become vague on the specifics. Could you explain fully the ENTIRE process of using ASP to execute this query and store the results in some kind of ADO object and then display it in a web page, including the XML and XSLT parts? Perhaps that could be Part II.?

Collapse -

How to get Data Islands into HTML

by jamie In reply to Simplifying hierarchical ...

I enjoyed this article and have had fun researching how to actually use the XML. This is what I found for posting the XML to a web page.
Dim conn, cmd
Set conn = createobject("ADODB.Connection")
Set cmd = createobject("ADODB.Command")
conn.ConnectionString = strConn
Set cmd.ActiveConnection = conn
cmd.CommandText = "select aid,catid,status,title from tpdcarticle FOR XML AUTO, ELEMENTS"
cmd.Properties("Output Stream") = response
response.write "<XML ID='MyDataIsle'>"
cmd.execute ,,adExecuteStream
response.write "</XML>"

Collapse -

Follow-up article

by phil_perkins_1 In reply to Simplifying hierarchical ...

Since it seems that there is a bit of interest in this arena with more explanation, I'll be writing an article on how to simply grab XML data from SQL 2000, send it through an XSL transformation, insert it into ASP, and bind the data tables and textboxes in IE (5.0+). It should be out August 20th. If you have any questions before that time, just send me an email.

Related Discussions

Related Forums