|
yeah, I managed to find some info on google
re:limits on 2033 chars, but most of the conversations were regarding the query
analyzer...
I tried using the columnname and it worked like a
charm...
the other thing i'm considering is setting up IIS
to do the SQLXML or XMLSQL or whatever it is called. I bookmarked a page or
three on it, and just has something like an XML document on the webserver that
contains a query. somebody requests the XML file and SQL server does its thing.
not sure if that is the best solution, but hey, something fun to try and mess
around with.
thanks for the response.
peter
----- Original Message -----
Sent: Wednesday, July 23, 2003 9:54
AM
Subject: RE: [cf-xml] CFMX & SQL
Server & XML, i'm stuck
Use
columnlist to find the name of the column... it will probably change
every time.
loop
through the records: 1 to query.recordcount.
Reference the column using
query["columnname"][currentrow] and combine all of them into one long
string. this will then be your full XML document.
The
reason sql server breaks them up is there is size limit on the column, thus it
breaks them into multiple rows. Sorry if my code above is sloppy or
slightly incorrect, I've done this before but can't find it right now
=(
Jason
Hello
all,
I am hoping somebody has some insight into this, or if not, some
sort of
funny flame directed towards me along with a starting point on
finding an
answer (other than google, i've looked)...
I am
starting to get more and more into XML and SQL Server and coming up to
a
few roadblocks. I have managed to figure out how to insert a whole load
of
records into my SQL database by sending an XML packet via a
stored
procedure, and have managed to figure out the bare basics of
returning a
recordset from SQL Server as an XML packet (I have no clue,
but I just
figured it'd be easier/faster to do the conversion in SQL
Server rather than
in my CF code -- but I just made that part
up).
So in my db table I have a table of countries, along with their
2 and 3
character country codes, and which continent they are on, can
anybody tell
me how to retrieve the data from the database in a way that
it is usable?
this is the stored proc I have so
far:
-----
CREATE PROCEDURE [dbo].[countriesAsXml]
AS
select 1 as TAG, null as parent, null as
[countries!1!countryname], null as
[country!2!countryname], null as
[country!2!countrycode2], null as
[country!2!countrycode3], null as
[country!2!continent]
UNION
select 2 as TAG, 1 as parent, null,
countryname, countrycode2, countrycode3,
continent
from
countries
FOR XML Explicit
GO
-----
probably not the
best code in the world, but hey, it works... BUT, it
returns a recordset
with a column named
"XML_F52E2B61-18A1-11D1-B105-00805F49916B".
to
make matters worse (for any of those still reading), it seems to
return
about 11.5 records, each with 2033 characters... would there be
any way to:
a) rename the column to something usable
b) return the XML
in one giant blob instead of having me use <cfloop> over
each
record and join the strings back together?
thank you,
[the
often confused, and somewhat slow] peter
ps: for anybody that
really cares, or is just curious, the output from the
above query
is:
<countries>
<country
countryname="Afghanistan" countrycode2="AF"
countrycode3="AFG"
continent="Asia"/>
<country
countryname="Albania" countrycode2="AL"
countrycode3="ALB"
continent="Europe"/>
<country countryname="Algeria" countrycode2="DZ"
countrycode3="DZA"
continent="Africa"/>
<country countryname="American Samoa"
countrycode2="AS"
countrycode3="ASM" continent="Pacific
Ocean"/>
<country
countryname="Andorra" countrycode2="AD"
countrycode3="AND"
continent="Europe"/>
...
</countries>
and returns a
little over 20,000+
characters...
|
