|
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 "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
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
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 -----
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
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
|
