I concure. I have done this, and it works. Jason
>-----Original Message----- >From: Lomvardias, Christopher [mailto:[EMAIL PROTECTED] >Sent: Monday, March 24, 2003 1:47 PM >To: 'Brian LeGros '; '[EMAIL PROTECTED] ' >Subject: RE: [cf-xml] CF5, SQL Server 2K Question > > >Brian, > >Actually, you should be able to get your query back without any trouble >using the following code (example uses PUBS database on SQL2K). > ><cfquery name="xmlQuery" datasource="localpubs"> > select au_id, au_lname, au_fname > from authors > FOR XML AUTO ></cfquery> > ><cfset xmlString=""> ><cfloop index="i" from="1" to="#xmlQuery.RecordCount#"> > <cfset xmlString=xmlString & xmlQuery[xmlQuery.ColumnList][i]> ></cfloop> > ><cfoutput>#htmleditformat(xmlString)#</cfoutput> > > >Regards, > >Chris Lomvardias > > > > >-----Original Message----- >From: Brian LeGros >To: [EMAIL PROTECTED] >Sent: 3/23/03 2:21 AM >Subject: Re: [cf-xml] CF5, SQL Server 2K Question > >In terms of XML support for SP3, in regards to this topic, I did find >out that you can get a scalar value back from SQL Server 2K when using >FOR XML but only when utilizing ADO or ADO.NET. I was kinda >disappointed that they didn't offer something more like the aliasing >idea. I guess they figure everyone should use ADO right ;) > >Oh well, my new focus is going to be on connection pooling >using ADO and >CF. You wouldn't happen to know if COM+ is built into the latest MDAC >would you? I figure maybe with object pooling turned on, I might not >have to make my own CF solution. Any ideas, I'm just brainstorming? > >Thanks again. > >-Brian > >From: Simon <mailto:[EMAIL PROTECTED]> Horwith >To: '[EMAIL PROTECTED]' <mailto:'[EMAIL PROTECTED]'> >Sent: Sunday, March 23, 2003 12:43 AM >Subject: RE: [cf-xml] CF5, SQL Server 2K Question > >though it's still worth a try - I don't think you'll be able to do what >I've suggesed....unless the docs on MSDN are wrong. I found two >statements there that lead me to this conclusion: > >1. "FOR XML cannot be used in a selection that requires further >processing in a stored procedure" > >2."Generally, FOR XML cannot be used for any selections that do not >produce direct output to the Microsoft� SQL Server(tm) 2000 client." > >What's more, many areas of the SQLXML docs point the developer to the " ><http://msdn.microsoft.com/library/en-us/xmlsql/ac_xml1_6a5o.asp> >Executing SQL Statements Using HTTP" section of the site... which >implies that my first hunch was right - FOR XML is really designed for >HTTP access use. That said, the docs don't appear to be updated for >SP3, so they may not be applicable when running SP3. Don't know - I >haven't had time to test SP3 yet. Sorry. > >~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: Simon Horwith [mailto:[EMAIL PROTECTED] >Sent: Sunday, March 23, 2003 0:18 >To: '[EMAIL PROTECTED]' >Subject: RE: [cf-xml] CF5, SQL Server 2K Question > > >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 <mailto:[EMAIL PROTECTED]> Horwith >To: '[EMAIL PROTECTED]' <mailto:'[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 ><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 > > >-----------------------+ >cf-xml mailing list >http://torchbox.com/xml/list.cfm > -----------------------+ cf-xml mailing list http://torchbox.com/xml/list.cfm
