Using ASP to generate XML documents from SQL

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:

[CODE]

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)

[/CODE]

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!

Re: Using ASP to generate XML documents from SQL

cmt wrote:
> 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.

--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

Re: Using ASP to generate XML documents from SQL

cmt wrote:
> 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.as p



--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

Re: Using ASP to generate XML documents from SQL

"cmt" wrote in message
news:a759e7a2-f77d-403a-bba6-84db347ba6ba@k2g2000hse.googleg roups.com...
> 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:
>
> [CODE]
>
> 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)
>
> [/CODE]
>
> 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.


--
Anthony Jones - MVP ASP/ASP.NET

Re: Using ASP to generate XML documents from SQL

Uzytkownik "cmt" napisal w wiadomosci
news:a759e7a2-f77d-403a-bba6-84db347ba6ba@k2g2000hse.googleg roups.com...
> 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 String(r.Fields(k).Name),fv=new
String(r.Fields(k).Value);llml.setAttribute(fn,fv);};
r.MoveNext();
};
--
td

Re: Using ASP to generate XML documents from SQL

Thanks everyone!

I am actually using SQL Server 2000.

Re: Using ASP to generate XML documents from SQL

cmt wrote:
> 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):

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

--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.