Tyler,
I've done this successfully in CF 5 (not tried it in CFMX yet). There's a
simple trick to it. The problem is that the column name (a uuid) has
characters that are invalid as a CF variable. You can't output them without
throwing an error. For example, an XML Auto query might return:
XM_3jfa-343-bbb
as the name of the column. CF will choke on the dashes in the variable
name. However, you CAN determine the name of the column using the
"queryname.columnname" syntax. You can aslo serialize the query into WDDX
(don't ask me why THAT works) - so here's my kludgy work-around example:
..run the query....
<cfquery name="GetXML" datasource="myDsn">
SELECT * FROM myTable
FOR XML AUTO
</cfquery>
<!--- an output like this ---
<Cfoutput>#Listfirst(GetXML.Columnlist)#</cfoutput> --- will NOT work
because the column name contains invalid characters --->
... only one column is returned so this statement gives you the offending
string ....
<cfset TheCol = GetXML.Columnlist>
...Serialize the data inot a WDDX string....
<Cfwddx action="CFMX2WDDX" input="#GetXML#" output="XMLString">
....Replace the offending string with a valid CF variable name that
represents the column of data.....
<Cfset XMLString = Replace(XMLString,theCol,'XmlDataFromTable','ALL')
.... Now re-serialize the query ....
<cfwddx action="wddx2cfml" input="XMLString" output="GetXML">
<!--- now this output ---
<Cfoutput>#Listfirst(GetXML.Columnlist)#</cfoutput> --- WILL work
because the column name contains is "XmlDataFromTable" rather than a
UUID --->
I hope this helps - let me know if it's not clear.
P.S. - If you are having trouble in this with CFMX contact me off list and
I'll help you work through it. There are also some other tricks for setting
up that return XML to be structured in a readable or logical way.
-mark
-----Original Message-----
From: Tyler Clendenin [mailto:[EMAIL PROTECTED]]
Sent: Tuesday, October 08, 2002 2:47 PM
To: CF-Talk
Subject: Reading MSSQL 2000 FOR XML AUTO data
When i run a query in query manager i get a recordset with the first row
being the XML data in the column of XM_[some uuid]. the problem is that
when i try to obtain this information in cold fusion there are no rows
returned. anyone know this problem?
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4
Subscription: http://www.houseoffusion.com/index.cfm?sidebar=lists&body=lists/cf_talk
FAQ: http://www.thenetprofits.co.uk/coldfusion/faq
Signup for the Fusion Authority news alert and keep up with the latest news in
ColdFusion and related topics. http://www.fusionauthority.com/signup.cfm
