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

Greg Maxwell Wed, 23 Jul 2003 10:43:02 -0700

Title: Message

So I had the same problem about 6 months ago,

for xml explicit did not return clean xml code

had a job number and line breaks and record count at the end

so I made a bat file called from cfexecute to run isqlw command window to do the work

then cleaned up the mess with some find and replace

use timeout to make sure you give the sql enough time to create the file.a flaw in cfexecute will run till timeout even if job is done sooner.

this is the contents of g_parts_1010_xml.sql

        Select
        *
        From Parts_1010 Parts
        Order by Make,Model,sYear,Part
        For XML raw

end content

then clean it up

this add the correct xml framing tag and title
<cfset Models_xml="<?xml version='1.0'?><Parts_1010>"&xmlTrim&"</Parts_1010>" >

Hopes this helps

Sincerely

Greg Maxwell

-----Original Message-----
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of peter.dehaan
Sent: Tuesday, July 22, 2003 4:13 PM
To: [EMAIL PROTECTED]
Subject: [cf-xml] CFMX & SQL Server & XML, i'm stuck

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

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

Reply via email to