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

[Simon Horwith]

Title: Message

there are illegal characters in the name of the variable that is returned (it's a UUID), which is why you cannot reference it.  I just opened the article with no problem.  I don't blame you about the HTTP request - but that's the only way to directly return the XML without use of a COM object (that I know of).  I also don't know of any way to rename that "column" when you select it - it's system-defined.  I suppose you could write an SP that declares a private variable, sets the variable equal to a "FOR XML" query, then runs a second SELECT that selects that variable AS some valid column name... I've never actually tried to do that... so you may want to give that a shot. Let us know if that works?     ~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: Saturday, March 22, 2003 23:58 To: [EMAIL PROTECTED] Subject: Re: [cf-xml] CF5, SQL Server 2K Question Thank you for the advice.  It's good to know I exhausted most every option.  I had looked at the use of the SQLXML API, but had ruled it out due to the sensitive nature of my application.  In addition, I'm not too partial to making an extra HTTP requests when I may have other low level protocols available to me. I guess I'm just gonna use ADO via CF.   On a side note I was unable to pull up your article, they may have taken it down.  In your article, did you talk about the reasons why CF and this feature of SQL 2K, won't interoperate?  Do you know if my assumption about the column name being too long for CF's use, is correct?  Just being over curious I guess.   Thanks again for the help.  If anyone else has any other ideas or opinions, feel free to chime in.   -Brian ----- Original Message ----- From: Simon Horwith To: '[EMAIL PROTECTED]' Sent: Saturday, March 22, 2003 10:25 PM Subject: RE: [cf-xml] CF5, SQL Server 2K Question 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