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
Structure your ColdFusion code with Fusebox. Get the official book at
http://www.fusionauthority.com/bkinfo.cfm
