RE: [cf-xml] CF5, SQL Server 2K Question

[Simon Horwith]

Title: Message

that is correct - you cannot use data selected as XML like this.  In order to use the XML data, you need to make an http request of a "SQL Server Over HTTP" virtual mapping (either pass the SQL over the URL or embed it within an MS - SQL XML packet).  You can find out more about this in an article I wrote a year and a half ago for CFDJ:  http://www.sys-con.com/coldfusion/article.cfm?id=331   Good Luck!   ~Simon     Simon Horwith Macromedia Certified Instructor Certified Advanced ColdFusion MX Developer Certified Flash MX Developer CFDJList - List Administrator Fig Leaf Software 1400 16th St NW, # 220 Washington DC 20036 202.797.6570 (direct line) www.figleaf.com -----Original Message----- From: Brian LeGros [mailto:[EMAIL PROTECTED] Sent: Friday, March 21, 2003 17:50 To: [EMAIL PROTECTED] Subject: [cf-xml] CF5, SQL Server 2K Question I'm currently using CF 5.0 with SQL Server 2K.  I'm using <cfquery> to write the following query:   SELECT pi.*, pli.*, pf.* FROM ((PURCHASE_INVOICE pi INNER JOIN PURCHASE_LINE_ITEMS pli  ON pi.Invoice_ID = pli.Invoice_ID) INNER JOIN PURCHASE_FEE pf  ON pli.Invoice_ID = pf.Invoice_ID AND pli.LineItem_ID = pf.LineItem_ID) FOR XML AUTO, ELEMENTS   I get back a recordset with one col and one row.  The column name is crazy long (40 chars) and is not something I can't predict (i.e. - XML_F52E2B61-18A1-11d1-.....).  I write the following code:   <cfloop type="query"><cfoutput>#Evaluate(ListGetAt(temp.columnlist, 1))#</cfoutput></cfquery>   I get an error saying that the variable name after "A1-" is invalid.  I'm guessing this is some kind of constraint on CF variable name lengths.   I tried reading up on SQL 2K and found that if it was updated to SP3, a string could be returned instead of a recordset when using FOR XML.  Unfortunately, the only code I've found to utilize this is using the ADO COM API.   Does anyone know of a way using the FOR XML clause in SQL Server 2K to get that XML doc back into CF using <cfquery> or stored procedures?  Any advice / help would be greatly appreciated.  I'm really stuck here.   -Brian