• Global community
    • Language:
      • Deutsch
      • English
      • Español
      • Français
      • Português
  • 日本語コミュニティ
    Dedicated community for Japanese speakers
  • 한국 커뮤니티
    Dedicated community for Korean speakers
Exit
0

Retrieve xml-data from text-column

Community Beginner ,
Apr 05, 2012 Apr 05, 2012

Copy link to clipboard

Copied

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

Views

1.6K

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines

correct answers 1 Correct answer

Community Beginner , Apr 05, 2012 Apr 05, 2012

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

Votes

Translate

Translate
Community Expert ,
Apr 05, 2012 Apr 05, 2012

Copy link to clipboard

Copied

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

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Community Beginner ,
Apr 05, 2012 Apr 05, 2012

Copy link to clipboard

Copied

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.

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Community Expert ,
Apr 05, 2012 Apr 05, 2012

Copy link to clipboard

Copied

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?

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Community Beginner ,
Apr 05, 2012 Apr 05, 2012

Copy link to clipboard

Copied

Yes, that's it.

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Contributor ,
Apr 05, 2012 Apr 05, 2012

Copy link to clipboard

Copied

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.

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Community Beginner ,
Apr 05, 2012 Apr 05, 2012

Copy link to clipboard

Copied

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

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Contributor ,
Apr 05, 2012 Apr 05, 2012

Copy link to clipboard

Copied

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

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

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Community Expert ,
Apr 05, 2012 Apr 05, 2012

Copy link to clipboard

Copied

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

FOR XML RAW, ROOT('myRoot')

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Community Beginner ,
Apr 05, 2012 Apr 05, 2012

Copy link to clipboard

Copied

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.

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Community Beginner ,
Apr 05, 2012 Apr 05, 2012

Copy link to clipboard

Copied

LATEST

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

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Resources
Documentation