Yeah I have seen that, but I'm up against a time constraint and there
are a number of issues I don't have my head wrapped around, and the
client needs a solution asap given that he has a daily feed
encompassing thousands of records and dozens of clients ... that has
now been dead about 10 days.
I'm sure I could figure it out with enough time, but the client would
be better served if someone who actually has done this can hit the
ground running on it.
BUT with this whole thing in mind, there are some things I don't get
about this whole load_file() thing that maybe you or someone else can
answer. I get how the file is pulled into memory, but how can a
multi-level file get pulled in?
Hopefully this excerpt (and its only a fragment) will explain my problem.
- I don't need the header sectioon and want to skip over that.
- The dataArea contains the line item records I want to pull in.
- I can write CF code to pull the price and currency out of the
pricing section, but how would mySQL do something like that
where the xml design was made as complicated as humanly
possible? (this format is StarStandard.org-based, btw)
- Note the ImageAttachmentExtended records. There can
be any number of those, or none. A CFloop works great but ...
- the VehicleRemarketingBoat section can have any number
of line items *or groups* and they vary from record to record.
The groups can have more than one entry in them, or not.
Here again CF gives me tools to deal with this.
I just don't see how something like this can be dumped into a db at
all. I'm assuming its inexperience with the task. Or is this xml a
bit beyond that type of approach?
<headerSection>
<headerField1>blah</headerField1>
<headerField2>blah</headerField2>
<headerField3>yoo</headerField3>
<headerField4>hoo</headerField4>
<headerField5>woof</headerField5>
</headerSection>
<dataArea>
<dataRecord>
<recordID>123</recordID>
<year>1980</year>
<make>Trabant</make>
<model>TurboXL</model>
<pricing>
<hidePrice>false</hidePrice>
<hidePrice>8</hidePrice>
<Price>
<ChargeAmount currencyID='USD'>99500</ChargeAmount>
</Price>
</pricing>
<Location>
<LocationAddress>
<CityName>Annapolis</CityName>
<CountryID>US</CountryID>
<StateOrProvinceCountrySubDivisionID>MD</StateOrProvinceCountrySubDivisionID>
</LocationAddress>
</Location>
<ImageAttachmentExtended>
<ID>46795014</ID>
<URI>http://img.arf.com/images/1/70/33/3197033_0_080220111124_1.jpg</URI>
<ImageWidthMeasure unitCode='pixel'>412</ImageWidthMeasure>
<ImageHeightMeasure unitCode='pixel'>471</ImageHeightMeasure>
<ImageLastModifiedDateTime>2011-02-08T11:24:00-08:00</ImageLastModifiedDateTime>
<UsagePreference>
<PriorityRankingNumeric>1</PriorityRankingNumeric>
</UsagePreference>
</ImageAttachmentExtended>
<ImageAttachmentExtended>
<ID>123456</ID>
<URI>http://img.arf.com/images/1/70/33/3197033_0_080220111124_2.jpg</URI>
<ImageWidthMeasure unitCode='pixel'>412</ImageWidthMeasure>
<ImageHeightMeasure unitCode='pixel'>471</ImageHeightMeasure>
<ImageLastModifiedDateTime>2011-02-08T11:24:00-08:00</ImageLastModifiedDateTime>
<UsagePreference>
<PriorityRankingNumeric>2</PriorityRankingNumeric>
</UsagePreference>
</ImageAttachmentExtended>
<ImageAttachmentExtended>
<ID>987654</ID>
<URI>http://img.arf.com/images/1/70/33/3197033_0_080220111124_3.jpg</URI>
<ImageWidthMeasure unitCode='pixel'>412</ImageWidthMeasure>
<ImageHeightMeasure unitCode='pixel'>471</ImageHeightMeasure>
<ImageLastModifiedDateTime>2011-02-08T11:24:00-08:00</ImageLastModifiedDateTime>
<UsagePreference>
<PriorityRankingNumeric>1</PriorityRankingNumeric>
</UsagePreference>
</ImageAttachmentExtended>
<VehicleRemarketingBoat>
<MakeString>Starratt and Jenks</MakeString>
<ModelYear>1979</ModelYear>
<SaleClassCode>Used</SaleClassCode>
<Model>Morgan 45</Model>
<BoatLengthGroup>
<BoatLengthCode>Nominal Length</BoatLengthCode>
<BoatLengthMeasure unitCode='feet'>45</BoatLengthMeasure>
</BoatLengthGroup>
<BoatLengthGroup>
<BoatLengthCode>Length At Water Line</BoatLengthCode>
<BoatLengthMeasure unitCode='feet'>31.42</BoatLengthMeasure>
</BoatLengthGroup>
<BoatLengthGroup>
<BoatLengthCode>Length Overall</BoatLengthCode>
<BoatLengthMeasure unitCode='feet'>45</BoatLengthMeasure>
</BoatLengthGroup>
<BeamMeasure unitCode='feet'>11</BeamMeasure>
<DraftMeasureGroup>
<DraftMeasure unitCode='feet'>6.08</DraftMeasure>
<BoatDraftCode>Max Draft</BoatDraftCode>
</DraftMeasureGroup>
<DisplacementMeasure unitCode='pound'>25000</DisplacementMeasure>
<BoatCategoryCode>Sail</BoatCategoryCode>
<BoatClassGroup>
<BoatClassCode>Cruisers</BoatClassCode>
<PrimaryBoatClassIndicator>true</PrimaryBoatClassIndicator>
</BoatClassGroup>
<GeneralBoatDescription>One Big Boat</GeneralBoatDescription>
<BuilderName>Starratt and Jenks</BuilderName>
<DesignerName>Charles Morgan</DesignerName>
<BoatName>Inventions</BoatName>
<Hull>
<BoatHullMaterialCode>Fiberglass</BoatHullMaterialCode>
</Hull>
<BallastWeightMeasure unitCode='pound'>12500</BallastWeightMeasure>
</VehicleRemarketingBoat>
</dataRecord>
</dataArea>
On Wed, Feb 16, 2011 at 10:09 AM, Rick Faircloth
<r...@whitestonemedia.com> wrote:
>
> Hi, Matt...
>
> I know you might have looked at this link at dev.mysql.com
> for a solution to processing your data feed, but I thought I'd
> bring it up, just in case you haven't seen it. Looks like it
> might be right up your alley.
>
> http://dev.mysql.com/tech-resources/articles/xml-in-mysql5.1-6.0.html#xml-5.
> 1-in-and-out
>
> While I didn't have to process xml files, I do have to process
> a daily data feed of real estate data in comma-delimited files
> and insert that info into a MySQL 5 database.
>
> At first, I tried processing the files with CF, but it was a
> *really* slow process using CF, taking almost a minute to process
> some files. Finally, someone on this list
> suggested I take a look at the MySQL commands for loading data
> from files, such as, in your case, "Load_File()", which opens
> an entire XML document, makes it available as a string,
> and inserts this string into a table column. In my case, using
> MySQL's load data infile, the time was reduced to less than a second.
>
> Check out the page above for details on usage and syntax.
>
> hth,
>
> Rick
>
> -----Original Message-----
> From: Matt Robertson [mailto:websitema...@gmail.com]
> Sent: Wednesday, February 16, 2011 12:22 PM
> To: cf-talk
> Subject: Re: Big XML files processing Really s-l-o-w. Solution?
>
>
> Hey everybody, we're in a bind here and I pitched the idea to the
> client of bringing in a ringer. Is there someone out there who is
> interested in building a direct-to-SQL Server or direct-to-mySQL-based
> solution?
>
> Solution would have to, on a scheduled basis, grab a file in a
> location on the server on its own without CF and process it (we have
> stuff that retrieves/places the file). Current file size is about 50
> mb but must be expected to go to around 100. At 42mb using just CF I
> am at about 70 seconds per record loop. We need a *significant*
> improvement in performance. Work would be on a dedicated Windows
> server. Present box is a fairly powerful Crystaltech Win2003 Server
> w/4GB RAM and a Xeon 2.8 processor w/4 cores. We'd consider a
> Win2008, 64-bit installation to improve speed. Looking at a 24-core
> server at Viviotech w/16gb as a hi-horsepower alternative.
>
> Our feed partner is probably going to be able to use a different
> method of delivery that will reduce file size, but for now we need to
> plan for the worst and move on it.
>
> Anyone interested? You can email me at my for-reals email at matt AT
> mysecretbase DOT 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:342342
Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
