Skip navigation
Currently Being Moderated

Retrieve xml-data from text-column

Apr 5, 2012 12:33 AM

Tags: #cfquery #coldfusion #sql #cf9

I've a table which has a field (datatype "text") that contains a xml-file. After some try and error I found out that the only way to fetch the complete xml-file from that table is to use the "FOR XML" clause of ms sql. But my next problem is, that the colum containing the xml-file is named something like "XML_F52E2B61-18A1-11D1-B105-00805F49916B" which is an invald identifier for cf.

 

Is there a way to access that column by its id instead of its name or what's the best way to access this column?

 

The SQL statement I'm using is:

 

SELECT XmlPackage
FROM LogK3OnChange
WHERE DealerID = IsNull(<cfqueryparam cfsqltype="cf_sql_varchar" value="#arguments.dealerid#">, DealerID)
AND LogID = <cfqueryparam cfsqltype="cf_sql_varchar" value="#arguments.logid#">
FOR XML RAW

 

Regards,

Heiko

 
Replies
  • Currently Being Moderated
    Apr 5, 2012 1:04 AM   in reply to K3NetSolutions

    K3NetSolutions wrote:

     

    But my next problem is, that the colum containing the xml-file is named something like "XML_F52E2B61-18A1-11D1-B105-00805F49916B" which is an invald identifier for cf.

     

    Is there a way to access that column by its id instead of its name or what's the best way to access this column?

     

    The SQL statement I'm using is:

     

    SELECT XmlPackage
    FROM LogK3OnChange
    WHERE DealerID = IsNull(<cfqueryparam cfsqltype="cf_sql_varchar" value="#arguments.dealerid#">, DealerID)
    AND LogID = <cfqueryparam cfsqltype="cf_sql_varchar" value="#arguments.logid#">
    FOR XML RAW

    Use aliasing:

     

    SELECT XML_F52E2B61-18A1-11D1-B105-00805F49916B as myXML

    from LogK3OnChange

     

    or

     

    SELECT XML_F52E2B61-18A1-11D1-B105-00805F49916B myXML

    from LogK3OnChange

     
    |
    Mark as:
  • Currently Being Moderated
    Apr 5, 2012 2:02 AM   in reply to K3NetSolutions

    I took it for granted your SQL is embedded in cfquery. Do you mean that, when you run "SELECT XmlPackage" the result is a column named XML_F52E2B61-18A1-11D1-B105-00805F49916B?

     
    |
    Mark as:
  • Currently Being Moderated
    Apr 5, 2012 2:49 AM   in reply to K3NetSolutions

    SELECT XmlPackage as testXML
    FROM LogK3OnChange
    WHERE DealerID = IsNull(<cfqueryparam cfsqltype="cf_sql_varchar" value="#arguments.dealerid#">, DealerID)
    AND LogID = <cfqueryparam cfsqltype="cf_sql_varchar" value="#arguments.logid#">
    FOR XML RAW

     

    I hope it may work.

     
    |
    Mark as:
  • Currently Being Moderated
    Apr 5, 2012 3:40 AM   in reply to K3NetSolutions

    You have the datatype of that XML column as "TEXT" of what length?

    Try to change the datatype to varchar2(4000) and check it.

     
    |
    Mark as:
  • Currently Being Moderated
    Apr 5, 2012 3:58 AM   in reply to K3NetSolutions

    Does it help when you specify the XML's root? I believe the syntax is:

     

    FOR XML RAW, ROOT('myRoot')

     
    |
    Mark as:

More Like This

  • Retrieving data ...

Bookmarked By (0)

Answers + Points = Status

  • 10 points awarded for Correct Answers
  • 5 points awarded for Helpful Answers
  • 10,000+ points
  • 1,001-10,000 points
  • 501-1,000 points
  • 5-500 points