Web Development

Ask something
Discussion Locked

Simplifying hierarchical data

0 helpful
6 replies

by debate

Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.
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.

6 replies

by kylebanashek

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?

by phil_perkins_1

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"
conn.Open

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.

by nlcapit

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.?

by jamie

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
conn.Open
Set cmd.ActiveConnection = conn
cmd.CommandText = "select aid,catid,status,title from tpdcarticle FOR XML AUTO, ELEMENTS"
cmd.Properties("Output Stream") = response
response.write ""
cmd.execute ,,adExecuteStream
response.write ""
%>

References
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/adosql/adoprg03_6coj.asp

by phil_perkins_1

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.

This discussion is closed to new replies.