Sam, hey - that works? Very cool. How come it doesn't choke on the UUID as a structure key name?
-mk -----Original Message----- From: Samuel Neff [mailto:[EMAIL PROTECTED]] Sent: Tuesday, October 08, 2002 10:34 PM To: CF-Talk Subject: Re: Reading MSSQL 2000 FOR XML AUTO data Tyler, As the previous poster mentioned, because the column name is invalid it can be difficult at first to access the data. However, there is a very simple work-around. Use CF's structure notation. When doing this, you must use the record number to identify the record in your loop, as follows: <cfquery name="xmlQuery" datasource="tstsql"> SELECT * FROM BOOKS FOR XML AUTO </cfquery> <cfset xmlString=""> <cfloop index="i" from="1" to="#xmlQuery.RecordCount#"> <cfset xmlString=xmlString & xmlQuery[xmlQuery.ColumnList][i]> </cfloop> <cfcontent type="text/xml"><root> <cfoutput>#xmlString#</cfoutput> </root> This works fine for me in CF5 and CFMX. A couple of notes: a) Since there is always just one column, you can use ColumnList by itself without a ListGetAt or ListFirst or anything. b) The column name is actually static--it's not supposed to ever change from "XML_F52E2B61-18A1-11D1-B105-00805F49916B". That said, I would personally always use ColumnList, just in case MS changes their mind, even though we all know they would never do that. :-) c) Remember the XML returned from MSSQL is not a valid XML document in an of itself--it's just an XML fragment. It needs a root element to make it valid. HTH, Sam ---------------------------------------- Samuel R. Neff Senior Software Engineer B-Line Express Advanced ColdFusion 5.0 Certified Team Macromedia Volunteer for ColdFusion ---------------------------------------- Date: Tue, 8 Oct 2002 15:47:20 -0400 From: "Tyler Clendenin" <[EMAIL PROTECTED]> Subject: Reading MSSQL 2000 FOR XML AUTO data Message-ID: <009401c26f03$841244d0$[EMAIL PROTECTED]> 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 Your ad could be here. Monies from ads go to support these lists and provide more resources for the community. http://www.fusionauthority.com/ads.cfm
