Velocity Reviews - Computer Hardware Reviews

Velocity Reviews > Newsgroups > Programming > ASP .Net > ASP General > Using ASP to generate XML documents from SQL

Reply
Thread Tools

Using ASP to generate XML documents from SQL

 
 
cmt
Guest
Posts: n/a
 
      01-11-2008
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)
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!
 
Reply With Quote
 
 
 
 
Bob Barrows [MVP]
Guest
Posts: n/a
 
      01-11-2008
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.


 
Reply With Quote
 
 
 
 
Bob Barrows [MVP]
Guest
Posts: n/a
 
      01-11-2008
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/...ta.islands.asp



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


 
Reply With Quote
 
Anthony Jones
Guest
Posts: n/a
 
      01-11-2008
"cmt" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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)
>
>
>
> 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


 
Reply With Quote
 
TOUDIdel
Guest
Posts: n/a
 
      01-11-2008

Uzytkownik "cmt" <(E-Mail Removed)> napisal w wiadomosci
news:(E-Mail Removed)...
> 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();
};
--
td


 
Reply With Quote
 
cmt
Guest
Posts: n/a
 
      01-11-2008


Thanks everyone!

I am actually using SQL Server 2000.

 
Reply With Quote
 
Bob Barrows [MVP]
Guest
Posts: n/a
 
      01-11-2008
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%2 0Server\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.


 
Reply With Quote
 
 
 
Reply

Thread Tools

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Failed to generate a user instance of SQL Server. Only an integratedconnection can generate a user instance. Harlan Messinger ASP .Net 2 03-28-2010 06:51 PM
Is it possible to consume UTF8 XML documents using xml.dom.pulldom? Simon Willison Python 10 07-31-2008 10:41 AM
No more stuff on C:\Documents and Settings\[User]\My Documents\Visual Studio 2005\ craigkenisston@hotmail.com ASP .Net 1 10-18-2006 03:31 PM
Opening word documents stored in SQL Server using ASP.Net =?Utf-8?B?cmljaGk=?= ASP .Net 3 11-18-2004 10:53 AM
ASP XML Support - Generate XML string from FORM DATA based on XML schema Matt ASP General 3 04-23-2004 07:12 PM



Advertisments