7 Replies Latest reply on Dec 2, 2007 12:50 PM by coffeedrinker56

    CFXML Problem

    rcjetpilot
    rcjetpilot Nov 29, 2007 6:15 AM
      Whenever I try to use data from joined tables the CFXML blows up. To be more specific when I add the #CustName# to the CFXML output it all stops working. I have tried just about everything but cannot get this to work. The query runs flawlessly in any other instance.

      Any suggestions would be greatly appriciated.

      I have a very simple query:

      SELECT HeaderID, MasterID, Invoice, InvoiceNo,
      InvoiceDate, Header.CustID, CustName FROM Header
      left Join Customer on header.custid = customer.custid
      WHERE header.HeaderID = #SearchInfo#
      ORDER BY InvoiceDate DESC

      Here is my CFXML code:

      <cfxml variable="invoiceXML">
      <Customer>
      <cfoutput query="myquery">
      <Invoices>
      <InvoiceNo>#InvoiceNo#</InvoiceNo>
      <HeaderID>#HeaderID#</HeaderID>
      <InvoiceAmount>#numberformat(InvoiceAmount, "999,999.99")#</InvoiceAmount>
      <InvoiceDate>#DateFormat(InvoiceDate,"mmm-dd-yyyy")#</InvoiceDate>
      <CustID>#CustID#</CustID>
      <CustName>#CustName#</CustName>
      </Invoices>
      </cfoutput>
      </Customer>
      </cfxml>

      <cfoutput>#invoiceXML#</cfoutput>

      • 649 Views
      • Tags:
        • 1. Re: CFXML Problem
          JR "Bob" Dobbs-qSBHQ2 Level 3
          JR "Bob" Dobbs-qSBHQ2 Nov 29, 2007 6:50 AM (in response to rcjetpilot)
          Please define "blows up".

          Is it possible that CustName or another field contains characters such as the ampersand that need to be escaped with the XmlFormat function?
          • 2. Re: CFXML Problem
            rcjetpilot Level 1
            rcjetpilot Nov 30, 2007 7:53 AM (in response to JR "Bob" Dobbs-qSBHQ2)
            This is the Error I receive, and yes I had forgotten the XMLFormat option.

            The XML page cannot be displayed
            Cannot view XML input using XSL style sheet. Please correct the error and then click the Refresh button, or try again later.

            Only one top level element is allowed in an XML document. Error processing resource

            <script language="javascript">
            -^
            :1em;text-indent:-2em"> <CustName>
            • 3. Re: CFXML Problem
              JR "Bob" Dobbs-qSBHQ2 Level 3
              JR "Bob" Dobbs-qSBHQ2 Nov 30, 2007 8:02 AM (in response to rcjetpilot)
              Is your error being generated by ColdFusion or a browser/xml viewer?
              • 4. CFXML Problem
                JR "Bob" Dobbs-qSBHQ2 Level 3
                JR "Bob" Dobbs-qSBHQ2 Nov 30, 2007 8:31 AM (in response to JR "Bob" Dobbs-qSBHQ2)
                The code below creates a well formed XML document.

                Is it possible that some code that is not included in your sample is responsible for the error?

                Is is possible that your text contains smart quotes or other characters not properly handled by the XmlFormat function?
                http://www.coldfusionjedi.com/index.cfm/2006/11/2/xmlFormat-and-Microsofts-Funky-Character s

                Edit:
                Are you using ToString() when converting the XML object to text?

                • 5. Re: CFXML Problem
                  Level 7
                  Newsgroup_User Nov 30, 2007 1:53 PM (in response to JR "Bob" Dobbs-qSBHQ2)
                  > The XML page cannot be displayed
                  > Cannot view XML input using XSL style sheet. Please correct the error and then
                  > click the Refresh button, or try again later.
                  >
                  > Only one top level element is allowed in an XML document. Error processing
                  > resource
                  >
                  > <script language="javascript">

                  This is a browser error, not a CF error.

                  Do a view-source on what's delivered to the browser... from the error
                  message it looks like it's not XML you're sending it.

                  --
                  Adam
                  • 6. Re: CFXML Problem
                    coffeedrinker56
                    coffeedrinker56 Dec 2, 2007 12:47 PM (in response to rcjetpilot)
                    Just a quick note: I use XmlFormat tag for each and every string or date/time field I'm retrieving from a database when the output is to an XML file. The reason is simple: I don't know every keystroke that's going to be used - correctly or incorrectly - by the users. Since it's also one of the cf tags that will accept embedded "#" characters, it's easy to use even in a script with multiple queries. A quick example:
                    <cfquery name="get_Data1" ...>
                    [Select statements here]
                    </cfquery>
                    <cfquery name="get_Data2" ...>
                    [Select statements here]
                    </cfquery>
                    <cfoutput>
                    <cfxml variable="outXML" casesensitive="yes">
                    <myRoot>
                    <cfloop query="get_Data1">
                    <user id="#get_Data1.user_id_number#">
                    <cfquery name="get_Data3" dbtype="query"
                    SELECT User_Last_Name, User_First_Name, User_Address
                    FROM get_Data2
                    </cfquery>
                    <cfloop query="getData3" startrow="1" endrow="1">
                    <user_name last="#XmlFormat('#getData3.User_Last_Name#')#" first="XmlFormat('#getData3.User_First_Name#')#" />
                    <user_address street="#XmlFormat('#getData3.User_Address#')#" />
                    </cfloop>
                    </user>
                    </loop>
                    </myRoot>
                    </cfoutput>
                    </cfxml>
                    <cfoutput>#outXML#</cfoutput>

                    If I haven't mistyped something, the browser reads the output as ...
                    <?xml version="1.0" encoding="UTF-8" ?>
                    <myRoot>
                    <user id="12345">
                    <user_name last="Spencer" first="Herb" />
                    <user_address street="4th &amp; Pine Streets" />
                    </user>
                    <user id="99999">
                    ...
                    </user>
                    </myRoot>

                    The little CPU time required for the XmlFormat tags to process is minor compared to the cost of searching each table to find the ONE character here that would cause an error.

                    • 7. Re: CFXML Problem
                      coffeedrinker56 Level 1
                      coffeedrinker56 Dec 2, 2007 12:50 PM (in response to coffeedrinker56)
                      I did mis-type something... the </cfoutpout> and </cfxml> tags at the end are in the wrong order ... Sorry!