Hi, Matt...
I've been tinkering with parsing the xml file and, though
I haven't developed a full solution, I've made some progress
that I thought I'd share.
I created a MySQL 5 database called, "xmlTest".
I created a MySQL 5 database table called, "xmlBlob".
Fields:
- rowID (int, not null, auto increment)
- xmlContent (blob)
- comment (varchar, 200)
I then ran this code:
<cfset filePath = #expandPath('data.xml')#>
<cfoutput>filePath = #filePath#</cfoutput> (checking accuracy)
<cfquery name="processXML" datasource="xmlTest">
select xmlContent
from xmlBlob
limit 1
into @xml
</cfquery>
Data extraction method 1:
<cfquery name="extractData01" datasource="xmlTest">
select extractValue(@xml, '//dataRecord[1]/recordID[1]')
recordID
</cfquery>
<p>cfdump toString(extractData01.recordID) = <cfdump
var="#toString(extractData01.recordID)#"></p>
Data extraction method 2:
<cfquery name="extractData02" datasource="xmlTest">
select extractValue(b.xmlContent, '//dataRecord/recordID')
recordID,
extractValue(b.xmlContent,
'//dataRecord/year') year,
extractValue(b.xmlContent,
'//dataRecord/make') make,
extractValue(b.xmlContent,
'//dataRecord/model') model
from xmlBlob b
</cfquery>
<cfoutput>extractData02.recordCount =
#extractData02.recordCount#</cfoutput><br>
<br>
<cfoutput query="extractData02">
#toString(extractData02.recordID)#<br>
#toString(extractData02.year)#<br>
#toString(extractData02.make)#<br>
#toString(extractData02.model)#<br>
<br>
</cfoutput>
-------------------------------------------------------
When the code above is run in a browser, it outputs:
cfdump toString(extractData01.recordID) = 123
extractData02.recordCount = 1
123 124
1980 1981
Trabant Ford
TurboXL TurboDL
-------------------------------------------------------
I was just to the point of figuring out how to get
the two records (I modified your data.xml file example so there
would be two records) to show separately, instead of
the data being in a list for the fields, when I saw your
post and thought I'd go ahead and respond so you could see
if this might be an approach that would work for you.
In the data extraction method 1 above, the idea is to insert
the xml file (which might be too large for this?)
into a blob field in a MySQL table, then read
the blob field and put the xml data file into a variable, '@xml',
then run a cfquery against the variable.
Data extraction method 2 skips reading the xml into a variable
and queries the blob field directly. This method uses the
MySQL 'xpath_expression' syntax to access data in multi-level
xml data files. With xpath, you can go as deeply into the levels
as needed.
Like I said, this is just the result of tinkering around with
what I've been finding and attempting to get some usable output.
Perhaps this will be of some help.
Rick
-----Original Message-----
From: Matt Robertson [mailto:websitema...@gmail.com]
Sent: Wednesday, February 16, 2011 10:18 PM
To: cf-talk
Subject: Re: Big XML files processing Really s-l-o-w. Solution?
On Wed, Feb 16, 2011 at 3:49 PM, Jochem van Dieten wrote:
> Would it help if you split the file into individual records before
> processing them?
I think it would help immensely I think. I'd prefer to fight my way
thru this in CF if at all possible and things are just complex enough
that I may need to go the route of stax. Outside my comfort zone in
terms of installing it on the server but I can likely figure it out.
This morning I investigated the possibility of dropping into java and
reading one line at a time, parsing out the text in that line... Its
what I do in this file's CSV-format predecessor. But there are no
CR's or LF's in the file so that is out.
Thanks very much for that post and your thoughts.
Incidentally if anyone is looking for an xml reader/editor that can
almost instantly read these kinds of moster files, check out the free
firstobject xml editor.
http://www.firstobject.com/
--
--m@Robertson--
Janitor, The Robertson Team
mysecretbase.com
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Order the Adobe Coldfusion Anthology now!
http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion
Archive:
http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:342361
Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
