Using ASP to generate XML documents from SQL

[cmt]

Greetings everyone,

I'm building an XML document based on data from a bunch of SQL
queries. The queries call data from multiple tables...not just one.

It feels as if the method I am using is cumbersome and I am wondering
if there is a better method.

Currently I am just building the XML document like this:

SQL1  'SQL query 1

Set rs1 = conn.Execute(SQL1)

If Not (rs1.EOF = True And rs1.bof = True) Then
Set inode = xmldoc.createNode("element", "fielda, "")
onode.appendChild (inode)
Set child = xmldoc.createNode("element", "fieldb", "")
child.Text = rs1.fields(0)
inode.appendChild (child)
Set child = xmldoc.createNode("element", "fieldc", "")
child.Text = rs1.fields(1)
inode.appendChild (child)


SQL2  'SQL query 2

Set rs2 = conn.Execute(SQL2)

If Not (rs2.EOF = True And rs2.bof = True) Then
Set inode = xmldoc.createNode("element", "fielda", "")
onode.appendChild (inode)
Set child = xmldoc.createNode("element", "fieldb", "")
child.Text = rs2.fields(0)
inode.appendChild (child)
Set child = xmldoc.createNode("element", "fieldc", "")
child.Text = rs2.fields(1)
inode.appendChild (child)

SQL3  'SQL query 3

Set rs3 = conn.Execute(SQL3)

If Not (rs3.EOF = True And rs3.bof = True) Then
Set inode = xmldoc.createNode("element", "fielda", "")
onode.appendChild (inode)
Set child = xmldoc.createNode("element", "fieldb", "")
child.Text = rs3.fields(0)
inode.appendChild (child)
Set child = xmldoc.createNode("element", "fieldc", "")
child.Text = rs3.fields(1)
inode.appendChild (child)

This is just an example version. The actual code is about 1000 lines
long.

Does ASP have a better way of handling the creation of XML?

Thanks!

 

[Bob Barrows [MVP]]

Greetings everyone,

Does ASP have a better way of handling the creation of XML?

Given that ASP is not a language, the answer to your literal question
has to be "no". Unfortunately, the answer to your implied question (does
vbscript hava a better way ... ) is also no.

Since it appears you are using SQL Server (if that's what you mean by
the "SQL" in your subject line) you might want to investigate the FOR
XML clause in SQL 2000 and SQL 2005 Without knowing the version of SQL
Server you are using, i can't get specific, so all I can suggest is that
you look it up in BOL.

 

[Bob Barrows [MVP]]

Does ASP have a better way of handling the creation of XML?

Oh! Forgot to post this:
look at the source code of this demo to see how I recommend processing
data retrieved from a database. There are a couple of very generic
functions that you can use without modification:
http://www.davidpenton.com/testsite/tips/xml.data.islands.asp

 

[Anthony Jones]

Greetings everyone,

I'm building an XML document based on data from a bunch of SQL
queries. The queries call data from multiple tables...not just one.

It feels as if the method I am using is cumbersome and I am wondering
if there is a better method.

Currently I am just building the XML document like this:

SQL1  'SQL query 1

Set rs1 = conn.Execute(SQL1)

If Not (rs1.EOF = True And rs1.bof = True) Then
Set inode = xmldoc.createNode("element", "fielda, "")
onode.appendChild (inode)
Set child = xmldoc.createNode("element", "fieldb", "")
child.Text = rs1.fields(0)
inode.appendChild (child)
Set child = xmldoc.createNode("element", "fieldc", "")
child.Text = rs1.fields(1)
inode.appendChild (child)


SQL2  'SQL query 2

Set rs2 = conn.Execute(SQL2)

If Not (rs2.EOF = True And rs2.bof = True) Then
Set inode = xmldoc.createNode("element", "fielda", "")
onode.appendChild (inode)
Set child = xmldoc.createNode("element", "fieldb", "")
child.Text = rs2.fields(0)
inode.appendChild (child)
Set child = xmldoc.createNode("element", "fieldc", "")
child.Text = rs2.fields(1)
inode.appendChild (child)

SQL3  'SQL query 3

Set rs3 = conn.Execute(SQL3)

If Not (rs3.EOF = True And rs3.bof = True) Then
Set inode = xmldoc.createNode("element", "fielda", "")
onode.appendChild (inode)
Set child = xmldoc.createNode("element", "fieldb", "")
child.Text = rs3.fields(0)
inode.appendChild (child)
Set child = xmldoc.createNode("element", "fieldc", "")
child.Text = rs3.fields(1)
inode.appendChild (child)

This is just an example version. The actual code is about 1000 lines
long.

Does ASP have a better way of handling the creation of XML?

It would really help if you indicated what DB you are using. Also have you
oversimplified your code or do you really not need to loop through the
recordsets.

SQL Server has a With XML modifier that you can use to generate XML from SQL
directly. Whilst its syntax can be a little cumbersome it can generate
hiearchical XML that you seem to want to build.

If your not using SQL Server and the DB you are using isn't able to generate
XML then you'll need to do it the hard way.

First you need to learn to use functions like this one:-

Function AddElem(roParent, rsName, rvntValue)
Set AddElem = roParent.ownerDocument.createElement(rsName)
roParent.appendChild AddElem
If Not IsNull(rvntValue) Then AddElem.Text = rvntValue
End Function

Then code can look like this:-

Set inode = AddElem(onode, "fielda", Null)
AddElem inode, "fieldb", rs3.fields(0).value
AddElem inode, "fieldc", rs3.fields(1).value

If your code is mainly as your example then you'll eliminate 66% of you code
with just that function.

Is it possible to include the results of the some of the recordsets in a
single query using JOINS? This doesn't necessarily prevent you from
creating the heiarchy you need.

 

[TOUDIdel]

Does ASP have a better way of handling the creation of XML?

Better not but in less rows' count

while(!r.EOF){
lml.appendChild(lxml.createElement(raw?raw:'I'))
for(var k=0;k<r.Fields.Count;k++){var fn=new
String(r.Fields(k).Name),fv=new
String(r.Fields(k).Value);llml.setAttribute(fn,fv);};
r.MoveNext();
};

 

[cmt]

Thanks everyone!

I am actually using SQL Server 2000.

 

[Bob Barrows [MVP]]

Thanks everyone!

I am actually using SQL Server 2000.

OK, go into BOL and look up For XML clause. On my machine, this link
gets me to the "guidelines" article (look at the "See Also links as
well):

mkMSITStore:C:\Program%20Files\Microsoft%20SQL%20Server\80\Tools\Books
\xmlsql.chm::/ac_openxml_0alh.htm