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
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
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
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.
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?
Copy link to clipboard
Copied
Yes, that's it.
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.
Copy link to clipboard
Copied
Sorry, that doesn't work. The alias definition is ignored and the column still has its crappy name
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.
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')
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.
Copy link to clipboard
Copied
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