Skip to main content
K3NetSolutions
K3NetSolutions
Inspiring
April 5, 2012
Answered

Retrieve xml-data from text-column

  • April 5, 2012
  • 2 replies
  • 2085 views

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

This topic has been closed for replies.
Correct answer K3NetSolutions

No, because my problem is the column name which is allways XML_F52E2B61-18A1-11D1-B105-00805F49916B which coldfusion claims as an invalid identifier. Trying to access the colum by its index doesn't also work.


Got it!

If I use something like

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

I can access the column without any problems

2 replies

M
meensi
Inspiring
April 5, 2012

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.

K3NetSolutions
K3NetSolutionsAuthor
Inspiring
April 5, 2012

Sorry, that doesn't work. The alias definition is ignored and the column still has its crappy name

M
meensi
Inspiring
April 5, 2012

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

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

BKBK
Community Expert
BKBKCommunity Expert
Community Expert
April 5, 2012

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

K3NetSolutions
K3NetSolutionsAuthor
Inspiring
April 5, 2012

You missunderstood me. I execute the sql statement inside of an cfquery tag and my query has only one result column which is named XML_F52E2B61-18A1-11D1-B105-00805F49916B. And this name seems to be autogenerated.

BKBK
Community Expert
BKBKCommunity Expert
Community Expert
April 5, 2012

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?

Terms & ConditionsAccessibility statement
Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices