SitePoint Sponsor

User Tag List

Results 1 to 5 of 5
  1. #1
    SitePoint Member
    Join Date
    Jun 2003
    Location
    va
    Posts
    3
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Question SQL-->ASP-->XML PROBLEM

    PLEASE HELP ME FIGURE IT OUT:

    1. i am using Msxml2.DOMDocument.4.0 for my xml document

    2. i call a stored proceudre that uses for xml explicit. Results look good in Query analyser except that its broken down in multiple rows, so it would need to be concatnated before processing as xml.

    3. in asp i use ADODB.Stream object to stream the RS and load into XML


    My output looks very funky:
    (here is a short example
    <html>

    <body>
    <Center><Font Size=5 Color=Red>XML Output</Font></Center>
    <xml xmlns:s='uuid:BDC6E3F0-6DA3-11d1-A2A3-00AA00C14882'
    xmlns:dt='uuid:C2F41010-65B3-11d1-A29F-00AA00C14882'
    xmlns:rs='urn:schemas-microsoft-com:rowset'
    xmlns:z='#RowsetSchema'>
    <s:Schema id='RowsetSchema'>
    <s:ElementType name='row' content='eltOnly' rs:CommandTimeout='30'>
    <s:AttributeType name='c0' rs:name='XML_F52E2B61-18A1-11d1-B105-00805F49916B'
    rs:number='1' rs:nullable='true' rs:maydefer='true'>
    <s:datatype dt:type='string' dt:maxLength='1073741823'
    rs:long='true'/>
    </s:AttributeType>
    <s:extends type='rs:rowbase'/>
    </s:ElementType>
    </s:Schema>
    <rs:data>
    <z:row c0='&#x3c;consTemp id=&#x22;1&#x22;&#x3e;&#x3c;prp_id&#x3e;1&#x3c;/prp_id&#x3e;&#x3c;name&#x3e;First&#x3c;/name&#x3e;&#x3c;description&#x3e;First&#x3c;/description&#x3e;&#x3c;Season id=&#x22;1&#x22;&#x3e;&#x3c;ctm_id&#x3e;1&#x3c;/ctm_id&#x3e;&#x3c;name&#x3e;Season 1&#x3c;/name&#x3e;&#x3c;description&#x3e;Season


    -----------------------------------------
    How do I decode it to display proper characters? also how do I set it up to be all in one row? Is there a way to disable it to be as 'RowsetSchema' ? SOrry this is my frist one and i have been struggling with it. any help would be greatly appreciated.

  2. #2
    Sultan of Ping jofa's Avatar
    Join Date
    Mar 2002
    Location
    SvÝ■jˇ­
    Posts
    4,080
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    "Results look good in Query analyser" ?
    for xml explicit queries never look good in QA

    Does your code look something like this?
    http://www.sitepoint.com/article/515/3

  3. #3
    SitePoint Member
    Join Date
    Jun 2003
    Location
    va
    Posts
    3
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    YES YOu see my pain!

    Quote Originally Posted by jofa
    "Results look good in Query analyser" ?
    for xml explicit queries never look good in QA

    Does your code look something like this?
    http://www.sitepoint.com/article/515/3
    code looks very much like that. -- any ideas? what am i doing wrong?

    -- YEah results look good in QA. Except that the return gets split between 4 8000char rows which kinda bites. other then that its fine.


    thanks

  4. #4
    Sultan of Ping jofa's Avatar
    Join Date
    Mar 2002
    Location
    SvÝ■jˇ­
    Posts
    4,080
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by genideva
    what am i doing wrong?
    Difficult to tell what you are doing wrong, without a copy of your code to look at

  5. #5
    SitePoint Member
    Join Date
    Jun 2003
    Location
    va
    Posts
    3
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Ok here is the code:
    <%
    'set xml doc
    Set xmldoc = Server.CreateObject("Msxml2.DOMDocument.4.0")
    xmlDoc.async = false
    xmlDoc.resolveExternals = false
    xmldoc.loadXML _
    "<?xml version='1.0' encoding='UTF-8'?>" & _
    vbNewLine & "<RootTemplates />"

    'Formats root elements
    Set vRoot = xmldoc.documentElement
    vRoot.appendChild xmldoc.createTextNode(vbNewLine)
    vRoot.appendChild xmldoc.createTextNode(vbTab)

    'Formats node elements
    Set vNode = xmldoc.createElement("FeedText")
    vNode.appendChild xmldoc.createTextNode(vbNewLine)
    vNode.appendChild xmldoc.createTextNode(vbTab)
    vNode.appendChild xmldoc.createTextNode(vbTab)

    'Create the recordset that you will be generating the XML Stream from
    Set cmdTemp = Server.CreateObject("ADODB.Command")
    Set rsXML = Server.CreateObject("ADODB.Recordset")
    strSQL = "explicitTestXML"
    cmdTemp.CommandText = strSQL
    cmdTemp.CommandType = 1
    Set cmdTemp.ActiveConnection = vdb
    rsXML.Open cmdTemp, , 0, 1

    'Create the Stream Ojbect
    Set stStream = Server.CreateObject("ADODB.Stream")
    rsXML.Save stStream, adPersistXML

    'Generate the Stream of XML Data from the recordset
    strXML = stStream.ReadText

    'Load the XML object
    'xmldoc.loadXML(strXML)

    vNode.appendChild xmlDoc.createTextNode(strXML)
    vNode.appendChild xmlDoc.createTextNode(vbNewLine)
    vNode.appendChild xmlDoc.createTextNode(vbTab)
    vRoot.appendChild vNode
    vRoot.appendChild xmlDoc.createTextNode(vbNewLine)

    'Error Checking and Printing
    if xmlDoc.parseError.errorCode <> 0 then
    set myErr = xmlDoc.parseError
    response.write("You have error " + myErr.reason)
    else
    response.write(xmlDoc.xml)
    end if

    'Close the objects that you created
    stStream.Close
    Set stStream = Nothing
    rsXML.Close
    Set rsXML = Nothing
    %>

    --vdb is my dataconnection taht is actually included on the page
    Last edited by genideva; Jun 19, 2003 at 06:09.


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •