Wow, that is pretty easy, thank you so much to all that answered. Puzzled me for a bit.
----- Original Message ----- From: "Samuel Neff" <[EMAIL PROTECTED]> To: "CF-Talk" <[EMAIL PROTECTED]> Sent: Tuesday, October 08, 2002 11:34 PM 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 This list and all House of Fusion resources hosted by CFHosting.com. The place for dependable ColdFusion Hosting.
