Using ASP to generate XML documents from SQL

Discussion in 'ASP General' started by cmt, Jan 11, 2008.

  1. cmt

    cmt Guest

    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!
     
    cmt, Jan 11, 2008
    #1
    1. Advertising

  2. 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.
     
    Bob Barrows [MVP], Jan 11, 2008
    #2
    1. Advertising

  3. 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.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.
     
    Bob Barrows [MVP], Jan 11, 2008
    #3
  4. "cmt" <> wrote in message
    news:...
    > 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
     
    Anthony Jones, Jan 11, 2008
    #4
  5. cmt

    TOUDIdel Guest

    Uzytkownik "cmt" <> napisal w wiadomosci
    news:...
    > 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
     
    TOUDIdel, Jan 11, 2008
    #5
  6. cmt

    cmt Guest

    Thanks everyone!

    I am actually using SQL Server 2000.
     
    cmt, Jan 11, 2008
    #6
  7. 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:mad: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.
     
    Bob Barrows [MVP], Jan 11, 2008
    #7
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. =?Utf-8?B?cmljaGk=?=
    Replies:
    3
    Views:
    3,486
    =?Utf-8?B?cmljaGk=?=
    Nov 18, 2004
  2. Replies:
    1
    Views:
    492
    Juan T. Llibre
    Oct 18, 2006
  3. Simon Willison
    Replies:
    10
    Views:
    576
    Paul Boddie
    Jul 31, 2008
  4. Harlan Messinger
    Replies:
    2
    Views:
    2,321
    John Bell
    Mar 28, 2010
  5. Matt
    Replies:
    3
    Views:
    355
    Bob Barrows [MVP]
    Apr 23, 2004
Loading...

Share This Page