|
hhmm, so far i dont think mine is returning invalid
XML, i'll have to check (currently i'm using SQL Server 2000 SP3)... right now
i'm just messing around with a few ideas, so i'm not really pushing it too hard,
but your ideas below definitely give me a few more things to try and
learn.
thanks
peter
----- Original Message -----
Sent: Wednesday, July 23, 2003 11:43
AM
Subject: RE: [cf-xml] CFMX & SQL
Server & XML, i'm stuck
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
<cfset dir_file="path
to /g_parts_1010.xml">
<!--- <CFIF
#FileExists(dir_file)#>
<cffile action=""
file="#dir_file#">
</cfif>
<cfexecute name="C:\Program
Files\Microsoft SQL Server\80\Tools\binn\isqlw.exe" arguments="-S server -d database -E -i path to sql/g_parts_1010_xml.sql -o path to output /g_parts_1010.xml"
timeout=30>
</cfexecute>
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
<cffile action=""
file="#dir_file#" Variable="XMLfile">
<cfset
x_end=#findNoCase("(",XMLFile,len(XMLFile)-100)#>
<cfset
x_start=#findNoCase("<",XMLFile)#>
<cfset
xmlTrim=left(XMLFILE,x_end-2)>
<cfset
xmlTrim=Right(XMLtrim,Len(XMLTrim)-(x_start-1))>
<cfset XMLtrim =
#replace(XMLTrim,"#chr(10)#","","ALL")#>
<cfset XMLtrim =
#replace(XMLTrim,"#chr(13)#","","ALL")#>
this add the correct
xml framing tag and title
<cfset Models_xml="<?xml
version='1.0'?><Parts_1010>"&xmlTrim&"</Parts_1010>"
>
<cffile action=""
file="output cleaned file to /
g_parts_1010.xml" output="#Models_xml#">
Hopes this helps
Sincerely
Greg
Maxwell
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...
|
