May I suggest another method.
<cfquery name="xmltest" datasource="someSource">
select top 10 *
from somedb
for xml auto, elements
</cfquery>
<CFXML VARIABLE="xmldoc">
<root>
<CFLOOP FROM="1" TO="#xmltest.recordcount#"
INDEX="x"><CFOUTPUT>#xmltest[xmltest.columnlist][x]#</CFOUTPUT></CFLOOP>
</root>
</CFXML>
All is good and valid.
Mark
-----Original Message-----
From: Samuel R. Neff [mailto:[EMAIL PROTECTED]]
Sent: Wednesday, January 08, 2003 2:20 PM
To: CF-Talk
Subject: RE: CF 2 XML
Oh, that.
That's by design. The issue is that MSSQL is not returning a query but
what MS calls a Stream, which is a one field query meant to be appended
(which is pretty different from Java Streams, afaik). When accessing this
type of SQL with ADO you use MS's ADO Stream object instead of an ADO
Recordset. Unfortunately, CF doesn't have any equivalent, so we have to
hack our way through.
To get valid XML you have to first loop through the records and create a
single string by concatenating all of them (if using CFMX, then it'll be
best to create a Java StringWriter object for this). Ths issue is that
because there's an invalid (according to CF) character in the field name,
you can't directly reference the field--you have to use the array operator
syntax, which also means you have to use numeric record indexes and not
current record.
<cfquery name="q"...>
...for xml...
</cfquery>
<cfset s="">
<cfloop index="i" from="1" to="#q.recordcount#">
<cfset s=s & q[q.columnlist][i]>
</cfloop>
Then that gives you an XML fragment in s. To make the fragment into valid
XML, you have to add a root node:
<cfset s = "<root>" & s & "</root>">
And then you have valid XML.
HTH,
Sam
At 01:20 PM 1/8/2003, you wrote:
>Yes, I realize that. But when you do it in a query
>
><CFQUERY NAME="thequery" ....>
>....
>FOR XML ...
></CFQUERY>
>
><cfdump var="thequery"/>
>
>the xml is broken into query rows that have nothing to do with the xml at
>all - at least for us - could be the JDBC driver.
>
>Rob
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4
Subscription:
http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribe&forumid=4
FAQ: http://www.thenetprofits.co.uk/coldfusion/faq
Get the mailserver that powers this list at http://www.coolfusion.com
Unsubscribe:
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
