[cf-xml] CFMX & SQL Server & XML, i'm stuck

[peter.dehaan]

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