CODEWITHSUNDEEP
sqlxmlcoldfusion
Ostfriese
Ostfriese

Reputation: 67

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 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?

Edit: 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

Upvotes: 0

Views: 639

Answers (3)

Rumments
Rumments

Reputation: 9

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>

Upvotes: 0

Ostfriese
Ostfriese

Reputation: 67

Got it

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

does the trick

Upvotes: 0

Mikael Eriksson
Mikael Eriksson

Reputation: 139000

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

Upvotes: 2

Related Questions