Hi Gene
One unusual but potentially high-performance solution to your problem would
be to use an XSLT stylesheet to transform your XML news feed into SQL
'INSERT' statements (each terminated by a semicolon). You could then pass
the result of this transformation to PostGreSQL via a single CFQUERY call.
For optimum performance, you could compile the stylesheet into a Translet,
which you would then call from ColdFusion. The steps would be something
like:
1. Write an XSLT stylesheet to transform XML news feed into SQL 'INSERT'
statements, so that, for example
<news>
<item id = "1">foo</item>
<item id = "2">bah</item>
</news>
would be transformed into
INSERT into t_news (id,text) values (1,'foo');
INSERT into t_news (id,text) values (2,'bah');
2. Compile the Stylesheet into a Translet/XSLTC
java org.apache.xalan.xsltc.compiler.XSLTC News2SQL.xsl
3. Seven times a day, call your new 'News2SQL' Translet using
CFX_TransletWrapper (free download from http://torchbox.com/xml)
<cfx_transletwrapper
���xml = "http://news_site/newsfeed.xml";
���translet = "News2SQL"
���result = "SQL">
4. Pass the generated SQL statements to PostGreSQL
<cfquery datasource = "newsDSN">
#SQL#
</cfquery>
Alternatively, you could use the attached tag, which will parse an incoming
XML feed into a ColdFusion structure, using Xerces. I found the tag on the
Allaire developers exchange last year, but I'm afraid I haven't been able to
contact the author. Since it was open source, and based heavily on the SoXML
tag from SiteObjects, I'm sure he wouldn't mind me attaching it here.
Good luck with your project
Tom
-----------------+
tom dyson
t: +44 (0)1608 811870
m: +44 (0)7958 752657
http://torchbox.com
> My site will be receiving a news feed in XML format up
> to seven times a day.
>
> The server that my site is hosted on is a linux box
> running Redhat 7.2. Also, it has PostgresQL 7.2 as the server-side databse,
cold fusion 5, and PHP installed. I had our Network Administrator download
> and configure Apache's Xerces (XML) and Xalan (XSLT) Java processor engines.
> I want to:
>
> 1. Parse the incoming XML using Xerces into an object
> that Cold Fusion recognizes.
> 2. Store all the data into the PostgresQL database
> (PostgresQl does recognize subqueries and ANSI standards.
> 3. Display data from the database on Cold Fusion
> templates (pages).
> 4. Allow users to search based on keywords.
>
> Now, having said all that, Cold Fusion 5 does not have
> native XML capabilities
>
> (CF MX does). That's why I had the Network
> Administrator download and install
>
> the afforementioned XML and XSLT processors. I cannot
> find very detailed
>
> information on how to make Java (Xerces) talk to Cold
> Fusion 5. I have
>
> checked Cold Fusion's Developer Exchange and cannot
> locate any free tags that
>
> would be beneficial. I would greatly appreciate your
> insight, ideas, or any
>
> comments.
<!---
Purpose : Parses xml using xerces java xml parser
Created On : 08/06/2001
Original Developer : Russell Brown (freeserve) copyright freeserve 2001
Long Description : Borrows heavily form SiteObjects soxml.cfm custom tag.
Uses xeces rather that msxmlso (in theory) it
is cross platform.
Thanks to SiteObjects for soxml. Ths tag
should be expanded to include
creating xml functionality.
Usage : <cf_fsxml input="[url to xml or full path to xml on local machine]"
output="[name of var to hold returned struct]">
Requires : init (set to 1 unless called from within the tag) input, must be an
absolute path to a local or remote xml file.
Returns : whatever is in output
* History : Originally developed by SiteObjects as soxml, cribbed and xerces-ified by
Me!
*
--->
<cfparam name="Attributes.Init" default="1">
<cfif Attributes.Init>
<cfparam name="Attributes.Input">
<cfparam name="Attributes.Output" default="Struct">
<CFOBJECT
TYPE=JAVA
ACTION=CREATE
CLASS="org.apache.xerces.parsers.DOMParser"
NAME=xmlParser>
<cfscript>
// Parse document
xmlParser.parse('#Attributes.input#');
Attributes.Node = xmlParser.getDocument();
Attributes.Struct = StructNew();
</cfscript>
</cfif>
<!--- Intermediary node list --->
<cfset nList = Attributes.Node.getChildNodes()>
<!--- Loop over list --->
<cfloop from="0" to="#evaluate(nList.getLength()-1)#" index="i">
<!--- //set intermediary for this node --->
<cfset thisNode = nList.item(i)>
<!--- //switch on type --->
<cfswitch expression="#thisNode.getNodeType()#">
<cfcase value="1">
<cfscript>
CurrentNode = StructNew();
//for attributes
if(thisNode.hasAttributes()) {
CurrentNode["Attributes"] = StructNew();
//get atts
theseAtts = thisNode.getAttributes();
//loop over atts
for(j=0;j LT theseAtts.getLength();j = j+1) {
thisAtt = theseAtts.item(j);
CurrentNode.Attributes[thisAtt.getNodeName()]
= thisAtt.getNodeValue();
}
}//end attributes if
//write this node to structure
if(isArray(Attributes.Struct)) // Currently part of a
collection
Attributes.Struct[ArrayLen(Attributes.Struct)+1] =
CurrentNode;
else
if(structKeyExists(Attributes.Struct,ThisNode.getNodeName())){ // Duplicate keys found
if
(isArray(Attributes.Struct[ThisNode.getNodeName()])) // Collection already exists
Attributes.Struct[ThisNode.getNodeName()][ArrayLen(Attributes.Struct[ThisNode.getNodeName()])+1]
= CurrentNode;
else{ // Create new collection
TempCollection = ArrayNew(1);
TempCollection[1] =
Attributes.Struct[ThisNode.getNodeName()];
TempCollection[2] = CurrentNode;
Attributes.Struct[ThisNode.getNodeName()] =
TempCollection;
}
}
else // Single element
Attributes.Struct[ThisNode.getNodeName()] =
CurrentNode;
</cfscript>
<!--- //do we recurse ? --->
<cfif thisNode.hasChildNodes()>
<cf_fsxml struct="#CurrentNode#" Node="#ThisNode#"
init="0">
<cfelse>
<cfset CurrentNode.Value = "">
</cfif>
</cfcase>
<cfcase value="3,4" delimiters=",">
<cfset Attributes.Struct.Value = ThisNode.getNodeValue()>
</cfcase>
<cfcase value="8">
<cfset Attributes.Struct.Comment = ThisNode.getNodeValue()>
</cfcase>
</cfswitch>
</cfloop>
<cfif Attributes.Init>
<!--- Return the new structure to the calling template --->
<cfset "Caller.#Attributes.Output#"= Attributes.Struct>
</cfif>
