Hello,
I am experimenting with code that I found on the CF forums that will
parse XML and place it into a database. The example from the forums uses
a very simple XML document, which I have successfully placed into the
database using the code. I'll show a pared-down version of that XML
document below (The CF code that I'm using appears at the bottom of this
message):
<?xml version="1.0" ?>
<MessageItem>
<Message>
<Message_ID>346</Message_ID>
<Thread_ID>162</Thread_ID>
<UserName>Frank Johnson</UserName>
<Subject>Serving from NT</Subject>
<Posted>1996-08-27 20:09:04</Posted>
<Body>We are looking at setting up a Web site for our company
(a Fortune 1000 health care provider). </Body>
</Message>
<Message>
<Message_ID>347</Message_ID>
<Thread_ID>162</Thread_ID>
<UserName>Linda Jason</UserName>
<Subject>RE: Serving from NT</Subject>
<Posted>1996-08-27 20:14:43</Posted>
<Body>You need to look seriously at the cost of deployment and
ongoing maintenance for Web servers. </Body>
</Message>
</MessageItem>
Now, the problem occurs when I substitute my own XML document and, using
the same code, I get the following error message, which occurs at the
point where the data should be inserted into the database.:
Expression result cannot be converted to a string
Expressions used inside tags like CFOUTPUT, CFQUERY, CFMAIL, etc.
must evaluate to a value that can be converted to a string for output or
dynamic text accumulation purposes. Complex objects, such as queries,
arrays, and COM/DCOM objects, cannot be represented as strings.
The error occurred while processing an element with a general identifier
of (#xorigin#), occupying document position (82:16) to (82:24) in the
template file c:\inetpub\wwwroot\practice\fgdc.cfm.
My XML document is:
<?xml version="1.0" ?>
<metadata>
<citation>
<origin>Connecticut Department of Environmental
Protection</origin>
<pubdate>1997</pubdate>
<title>Digital Raster Graphic of the 7.5 minute Quadrangle of
West Granville</title>
<edition>Version 1</edition>
<geoform>Map</geoform>
<pubplace>Reston, VA</pubplace>
<publish>USGS, Mapping Division</publish>
<onlink>http://magic.lib.uconn.edu/</onlink>
</citation>
<citation>
<origin>Connecticut Department of Environmental
Protection</origin>
<pubdate>1998</pubdate>
<title>Digital Raster Graphic of the 7.5 minute Quadrangle of
West Granville</title>
<edition>Version 2</edition>
<geoform>Map</geoform>
<pubplace>Reston, VA</pubplace>
<publish>USGS, Mapping Division</publish>
<onlink>http://magic.lib.uconn.edu/</onlink>
</citation>
<citation>
<origin>Connecticut Department of Environmental
Protection</origin>
<pubdate>1999</pubdate>
<title>Digital Raster Graphic of the 7.5 minute Quadrangle of
West Granville</title>
<edition>Version 3</edition>
<geoform>Map</geoform>
<pubplace>Reston, VA</pubplace>
<publish>USGS, Mapping Division</publish>
<onlink>http://magic.lib.uconn.edu/</onlink>
</citation>
</metadata>
The Codefollows (I hope it maintains the identations for readability):
<CFSETTING showdebugoutput="YES">
<html>
<head>
<title>Example: Build XML from query then read xml back into a
table.</title>
</head>
<CFOBJECT action="CREATE" type="COM" class="Microsoft.XMLDOM"
name="objXML">
<CFSET currentDir =
LCase(GetDirectoryFromPath(getcurrentTemplatePath()))>
<CFSET XMLOutputFile = currentDir & "fgdc_xml.xml">
!--- Read the previous output file path into a new input variable
--->
<CFSET XMLInputFile = XMLOutputFile>
<!---Load XML Document into MS XML DOM --->
<CFSET objXML.LOAD(XMLInputFile)>
<CFSET FGDCList = objXML.getElementsByTagName("citation")>
<!--- try to quietly drop any earlier version of the new
table --->
<CFTRY>
<CFQUERY name="maketable" datasource="fgdc">
Drop Table fgdcxml
</CFQUERY>
<CFCATCH type="Any"></CFCATCH>
</CFTRY>
<!--- make table to insert xml data --->
<CFTRY>
<CFQUERY name="maketable" datasource="fgdc">
CREATE TABLE fgdcxml (
mykey number,
origin text,
pubdate text,
title text,
edition text,
geoform text,
pubplace text,
publish text,
onlink text)
</CFQUERY>
<CFCATCH type="Any"></CFCATCH>
</CFTRY>
<CFTRY>
<CFSILENT>
<CFSET i = 1>
<CFLOOP COLLECTION="#FGDCList#"
Item="citation">
<CFSET xorigin =
citation.SelectSingleNode("origin")>
<CFSET xpubdate =
citation.SelectSingleNode("pubdate")>
<CFSET xtitle =
citation.SelectSingleNode("title")>
<CFSET xedition =
citation.SelectSingleNode("edition")>
<CFSET xgeoform =
citation.SelectSingleNode("geoform")>
<CFSET xpubplace =
citation.SelectSingleNode("pubplace")>
<CFSET xpublish =
citation.SelectSingleNode("publish")>
<CFSET xonlink =
citation.SelectSingleNode("onlink")>
<!--- insert data row by
row --->
<CFOUTPUT>
<CFQUERY name="insertXMLdata"
datasource="fgdc">
INSERT INTO fgdcxml(
mykey,
origin,
pubdate,
title,
edition,
geoform,
pubplace,
publish,
onlink)
VALUES(
#i#,
'#xorigin#',
'#xpubdate#',
'#xtitle#',
'#xedition#',
'#xgeoform#',
'#xpubplace#',
'#xpublish#',
'#xonlink#')
</CFQUERY>
</CFOUTPUT>
<CFSET i = i + 1>
</CFLOOP>
</CFSILENT>
<!--- find out how many rows were
inserted --->
<CFQUERY name="getmaxrow"
datasource="fgdc">
Select Max(mykey) As
maxrownum From fgdcxml
</CFQUERY>
<CFCATCH
type="Any"><CFOUTPUT>#cfcatch.message#<BR>#cfcatch.detail#</CFOUTPUT></CFCATCH>
</CFTRY>
<CFOUTPUT query="getmaxrow"><h3>Successfully
inserted <font color="FF0000">#Round(maxrownum)#</font> rows into the
new fgdc table fgdcxml!</h3> </CFOUTPUT>
<!--- now build a table to display the XML content
--->
<TABLE BORDER="1" bgcolor="eeeeee" width="100%">
<TR>
<!---<TD><strong>ID</strong></TD>--->
<TD><strong>OriginD</strong></TD>
<TD><strong>Publish Date</strong></TD>
<TD><strong>Title</strong></TD>
<TD><strong>Edition</strong></TD>
<TD><strong>Geoform</strong></TD>
<TD><strong>Publish Place</strong></TD>
<TD><strong>Publish</strong></TD>
<TD><strong>Link</strong></TD>
</TR>
<!--- Loop through the collection --->
<CFLOOP COLLECTION="#FGDCList#"
Item="citation">
<CFSET xorigin =
citation.SelectSingleNode("origin")>
<CFSET xpubdate =
citation.SelectSingleNode("pubdate")>
<CFSET xtitle =
citation.SelectSingleNode("title")>
<CFSET xedition =
citation.SelectSingleNode("edition")>
<CFSET xgeoform =
citation.SelectSingleNode("geoform")>
<CFSET xpubplace =
citation.SelectSingleNode("pubplace")>
<CFSET xpublish =
citation.SelectSingleNode("publish")>
<CFSET xonlink =
citation.SelectSingleNode("onlink")>
<CFOUTPUT>
<TR>
<TD><font size="2"
face="Arial">#xorigin.Text#</font></TD>
<TD><font size="2"
face="Arial">#xpubdate.Text#</font></TD>
<TD><font size="2"
face="Arial">#xtitle.Text#</font></TD>
<TD><font size="2"
face="Arial">#xedition.Text#</font></TD>
<TD><font size="2"
face="Arial">#xgeoform.Text#</font></TD>
<TD><font size="2"
face="Arial">#xpubplace.Text#</font></TD>
<TD><font size="2"
face="Arial">#xpublish.Text#</font></TD>
<TD><font size="2"
face="Arial">#xonlink.Text#</font></TD>
</TR>
</CFOUTPUT>
</CFLOOP>
</TABLE>
</body>
</html>
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4
Subscription: http://www.houseoffusion.com/index.cfm?sidebar=lists&body=lists/cf_talk
FAQ: http://www.thenetprofits.co.uk/coldfusion/faq
Signup for the Fusion Authority news alert and keep up with the latest news in
ColdFusion and related topics. http://www.fusionauthority.com/signup.cfm
