Retrieve Xml-data From Text-column

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

Solution 1:

If you embed your query in a sub-query you can give your column a name.

select
  (
    select *
    from YourTable
    for xml path('Row'), root('Root')
  ) as NewColumnName

With your query:

SELECT
  (
    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 PATH
  ) AS NewColumnName

Update: If you want the column to be a XML data type you should add the type keyword to your query. Not sure if that would make a difference for coldfusion.

SELECT
  (
    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 PATH, TYPE
  ) AS NewColumnName

Solution 2:

Got it

<cfoutput>#myQuery["XML_F52E2B61-18A1-11D1-B105-00805F49916B"][1]#</cfoutput>

does the trick

Solution 3:

This is a better answer as it does not rely on a string that may potentially change in some later revision of coldfusion.

<cfoutput>#QueryName[QueryName.ColumnList][1]#</cfoutput>