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