XML data

I have access to a data feed that provides data in XML documents, with
a structure that matches the columns in a table. (I.e. it is a simple
XMl format where the child elements correspond exactly to the columns
in the target table). When using the feed's web interface, that
interface provides the option to retrieve the same data as in excel
spreadsheet or as a CSV file. Alas, the API provided does not seem to
provide the option of obtaining the data in these formats.

Is there a relatively painless option for processing the XML file to
parse it and place the data it contains into the right records and
columns? Yes, I saw the option for getting a dump of an existing
database in XML format and subsequently reading that data into MySQL,
but the difference here is that MySQL is not what is producing the XML
file.

Equally importantly, I can not store the data in MySQL for a number of
reasons. There is a lot of data, the maximum length of the strings is
unknown (i.e. if it would be stored as a string, varchar would be the
obvious option, but what length?). And reports must be generated and
it would be silly to process the strings anew every time a new report
is required. It is best to process the XML just once and store the
data in the fields in the appropriate record/column.

Is there something built into MySQL that would make what I need to do
relatively painless, or an external tool?

I have managed to avoid parsing XML up till now (parsing strings is
one of the programming tasks I dislike the most), but alas it has
caught up to me and I have to bear down on it and put together
something which will make processing XML less painful when I have to
do it. :-(

Thanks,

Ted

Ted wrote:
> I have access to a data feed that provides data in XML documents, with
> a structure that matches the columns in a table. (I.e. it is a simple
> XMl format where the child elements correspond exactly to the columns
> in the target table). When using the feed's web interface, that
> interface provides the option to retrieve the same data as in excel
> spreadsheet or as a CSV file. Alas, the API provided does not seem to
> provide the option of obtaining the data in these formats.
>
> Is there a relatively painless option for processing the XML file to
> parse it and place the data it contains into the right records and
> columns? Yes, I saw the option for getting a dump of an existing
> database in XML format and subsequently reading that data into MySQL,
> but the difference here is that MySQL is not what is producing the XML
> file.
>
> Equally importantly, I can not store the data in MySQL for a number of
> reasons. There is a lot of data, the maximum length of the strings is
> unknown (i.e. if it would be stored as a string, varchar would be the
> obvious option, but what length?). And reports must be generated and
> it would be silly to process the strings anew every time a new report
> is required. It is best to process the XML just once and store the
> data in the fields in the appropriate record/column.
>
> Is there something built into MySQL that would make what I need to do
> relatively painless, or an external tool?
>
> I have managed to avoid parsing XML up till now (parsing strings is
> one of the programming tasks I dislike the most), but alas it has
> caught up to me and I have to bear down on it and put together
> something which will make processing XML less painful when I have to
> do it. :-(
>
> Thanks,
>
> Ted
>

Ted,

I doubt there's much you can do with MySQL itself unless it matches the
XML produced by MySQL. You'll probably have to use another language
such as Perl or PHP - both of which have ways of working with XML files.

And I don't know why you can't store the data in the database. It would
be much more efficient than working with the xml data. Things like
column lengths are up to you - they just need to be big enough to hold
the largest string you will store in them.

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex@attglobal.net
==================

On Apr 7, 9:24*am, Jerry Stuckle <jstuck...@attglobal.net> wrote:
> Ted wrote:
> > I have access to a data feed that provides data in XML documents, with
> > a structure that matches the columns in a table. *(I.e. it is a simple
> > XMl format where the child elements correspond exactly to the columns
> > in the target table). *When using the feed's web interface, that
> > interface provides the option to retrieve the same data as in excel
> > spreadsheet or as a CSV file. *Alas, the API provided does not seem to
> > provide the option of obtaining the data in these formats.
>
> > Is there a relatively painless option for processing the XML file to
> > parse it and place the data it contains into the right records and
> > columns? *Yes, I saw the option for getting a dump of an existing
> > database in XML format and subsequently reading that data into MySQL,
> > but the difference here is that MySQL is not what is producing the XML
> > file.
>
> > Equally importantly, I can not store the data in MySQL for a number of
> > reasons. *There is a lot of data, the maximum length of the strings is
> > unknown (i.e. if it would be stored as a string, varchar would be the
> > obvious option, but what length?). *And reports must be generated and
> > it would be silly to process the strings anew every time a new report
> > is required. *It is best to process the XML just once and store the
> > data in the fields in the appropriate record/column.
>
> > Is there something built into MySQL that would make what I need to do
> > relatively painless, or an external tool?
>
> > I have managed to avoid parsing XML up till now (parsing strings is
> > one of the programming tasks I dislike the most), but alas it has
> > caught up to me and I have to bear down on it and put together
> > something which will make processing XML less painful when I have to
> > do it. *:-(
>
> > Thanks,
>
> > Ted
>
> Ted,
>

Hi Jerry,

Thanks.

> I doubt there's much you can do with MySQL itself unless it matches the
> XML produced by MySQL. *You'll probably have to use another language
> such as Perl or PHP - both of which have ways of working with XML files.
>
I know both and can work with either. I have just avoided, or
ignored, development tools related to XML. Do you have a favourite
Perl package for this?

> And I don't know why you can't store the data in the database. *It would
> be much more efficient than working with the xml data. *Things like
> column lengths are up to you - they just need to be big enough to hold
> the largest string you will store in them.
>
That is what I am aiming for. I do not want to store the XML in the
database as a single string. Rather, I want to extract the data from
the XML and store THAT in the database. Once I have a handle on that,
everything else is easy, though tedious.

What I don't want to do is store the data as an XML string in a single
column. I have seen cases where a DBA did exactly that, with no
thought on how to get at individual fields. I am sure that that DBA
would have stored the files I'll get as a single string containing all
of the XML tags and data.

I can create a table for the main data, and set up several lookup
tables that would be appropriate, without breaking a sweat.

What I was asking for was recommendations on a relatively painless way
to get the data from the XML file into the tables I'd set up. If the
vendor would provide the data in CSV files through their API, then I'd
have the script for getting and storing the data don in less than an
hour. But, while I work with XML files in configuring my web server
and application server, I have not looked at writing a program to
parse XML to retrive data from it. I guess it is mostly a question of
familiarity. My greatest strengths are in raw number crunching in
fortran and especially C++. At present, the prospect of writing code
to process XML feels like being a fish out of water.

Thanks again,

Ted

Follow-ups to comp.text.xml

Ted <r.ted.byers@rogers.com> wrote in
<501aba57-5f89-4291-a393-e6767b282229@r9g2000prd.googlegroups.com>:
> On Apr 7, 9:24Â*am, Jerry Stuckle <jstuck...@attglobal.net>
> wrote:
>> Ted wrote:
>> > Is there a relatively painless option for processing
>> > the XML file to parse it and place the data it contains
>> > into the right records and columns?
>>
>> > I have managed to avoid parsing XML up till now
>> > (parsing strings is one of the programming tasks I
>> > dislike the most), but alas it has caught up to me and
>> > I have to bear down on it and put together something
>> > which will make processing XML less painful when I have
>> > to do it. Â*:-(

Writing a standard-compliant XML parser is no easy task. But
there are very few reasons for you to want that, since XML
parsers are readily available for pretty much every
language and platform with a market share larger than a
breadcrumb.

>> I doubt there's much you can do with MySQL itself unless
>> it matches the XML produced by MySQL. Â*You'll probably
>> have to use another language such as Perl or PHP - both
>> of which have ways of working with XML files.
>>
> I know both and can work with either. I have just
> avoided, or ignored, development tools related to XML. Do
> you have a favourite Perl package for this?

The URL for CPAN is <http://cpan.org/> I believe.
XML::Simple is a common choice for a lightweight,
easy-to-use XML parser among Perl programmers.

Both PHP4 and PHP5 come with standard extensions capable of
parsing XML: see DOM XML for PHP4 and DOM for PHP5. Both
make a good effort at mapping W3C's DOM API to PHP.

> My greatest strengths are in raw number crunching in
> fortran and especially C++.

For that matter, libxml2 and Xerces-C++ are two XML parsers
commonly used with C++, if that happens to be your language
of choice.

> At present, the prospect of writing code to process XML
> feels like being a fish out of water.

There's absolutely no reason for you to process XML
documents without using a canned parser. The whole point of
canned parsers is allowing you to work with a DOM tree, SAX
event stream or some other data model of XML Infoset.

--
"...a Netscape engineer who shan't be named once passed a
pointer to JavaScript, stored it as a string and later
passed it back to C, killing 30..." --Blake Ross

Ted wrote:
> On Apr 7, 9:24 am, Jerry Stuckle <jstuck...@attglobal.net> wrote:
>> Ted wrote:
>>> I have access to a data feed that provides data in XML documents, with
>>> a structure that matches the columns in a table. (I.e. it is a simple
>>> XMl format where the child elements correspond exactly to the columns
>>> in the target table). When using the feed's web interface, that
>>> interface provides the option to retrieve the same data as in excel
>>> spreadsheet or as a CSV file. Alas, the API provided does not seem to
>>> provide the option of obtaining the data in these formats.
>>> Is there a relatively painless option for processing the XML file to
>>> parse it and place the data it contains into the right records and
>>> columns? Yes, I saw the option for getting a dump of an existing
>>> database in XML format and subsequently reading that data into MySQL,
>>> but the difference here is that MySQL is not what is producing the XML
>>> file.
>>> Equally importantly, I can not store the data in MySQL for a number of
>>> reasons. There is a lot of data, the maximum length of the strings is
>>> unknown (i.e. if it would be stored as a string, varchar would be the
>>> obvious option, but what length?). And reports must be generated and
>>> it would be silly to process the strings anew every time a new report
>>> is required. It is best to process the XML just once and store the
>>> data in the fields in the appropriate record/column.
>>> Is there something built into MySQL that would make what I need to do
>>> relatively painless, or an external tool?
>>> I have managed to avoid parsing XML up till now (parsing strings is
>>> one of the programming tasks I dislike the most), but alas it has
>>> caught up to me and I have to bear down on it and put together
>>> something which will make processing XML less painful when I have to
>>> do it. :-(
>>> Thanks,
>>> Ted
>> Ted,
>>
>
> Hi Jerry,
>
> Thanks.
>
>> I doubt there's much you can do with MySQL itself unless it matches the
>> XML produced by MySQL. You'll probably have to use another language
>> such as Perl or PHP - both of which have ways of working with XML files.
>>
> I know both and can work with either. I have just avoided, or
> ignored, development tools related to XML. Do you have a favourite
> Perl package for this?
>
>> And I don't know why you can't store the data in the database. It would
>> be much more efficient than working with the xml data. Things like
>> column lengths are up to you - they just need to be big enough to hold
>> the largest string you will store in them.
>>
> That is what I am aiming for. I do not want to store the XML in the
> database as a single string. Rather, I want to extract the data from
> the XML and store THAT in the database. Once I have a handle on that,
> everything else is easy, though tedious.
>
> What I don't want to do is store the data as an XML string in a single
> column. I have seen cases where a DBA did exactly that, with no
> thought on how to get at individual fields. I am sure that that DBA
> would have stored the files I'll get as a single string containing all
> of the XML tags and data.
>
> I can create a table for the main data, and set up several lookup
> tables that would be appropriate, without breaking a sweat.
>
> What I was asking for was recommendations on a relatively painless way
> to get the data from the XML file into the tables I'd set up. If the
> vendor would provide the data in CSV files through their API, then I'd
> have the script for getting and storing the data don in less than an
> hour. But, while I work with XML files in configuring my web server
> and application server, I have not looked at writing a program to
> parse XML to retrive data from it. I guess it is mostly a question of
> familiarity. My greatest strengths are in raw number crunching in
> fortran and especially C++. At present, the prospect of writing code
> to process XML feels like being a fish out of water.
>
> Thanks again,
>
> Ted
>

Ted,

I don't have any particular Perl package I like for this - I don't do
all that much in Perl. I'd recommend comp.lang.perl. PHP has a couple
of different ways; there I'd recommend comp.lang.php.

You don't need to write the whole parser - that's been done for you.
Just use the tools to parse the file and pull out your values.

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex@attglobal.net
==================

On Apr 8, 8:10*am, Jerry Stuckle <jstuck...@attglobal.net> wrote:
> Ted wrote:
> > On Apr 7, 9:24 am, Jerry Stuckle <jstuck...@attglobal.net> wrote:
> >> Ted wrote:
> >>> I have access to a data feed that provides data in XML documents, with
> >>> a structure that matches the columns in a table. *(I.e. it is a simple
> >>> XMl format where the child elements correspond exactly to the columns
> >>> in the target table). *When using the feed's web interface, that
> >>> interface provides the option to retrieve the same data as in excel
> >>> spreadsheet or as a CSV file. *Alas, the API provided does not seem to
> >>> provide the option of obtaining the data in these formats.
> >>> Is there a relatively painless option for processing the XML file to
> >>> parse it and place the data it contains into the right records and
> >>> columns? *Yes, I saw the option for getting a dump of an existing
> >>> database in XML format and subsequently reading that data into MySQL,
> >>> but the difference here is that MySQL is not what is producing the XML
> >>> file.
> >>> Equally importantly, I can not store the data in MySQL for a number of
> >>> reasons. *There is a lot of data, the maximum length of the strings is
> >>> unknown (i.e. if it would be stored as a string, varchar would be the
> >>> obvious option, but what length?). *And reports must be generated and
> >>> it would be silly to process the strings anew every time a new report
> >>> is required. *It is best to process the XML just once and store the
> >>> data in the fields in the appropriate record/column.
> >>> Is there something built into MySQL that would make what I need to do
> >>> relatively painless, or an external tool?
> >>> I have managed to avoid parsing XML up till now (parsing strings is
> >>> one of the programming tasks I dislike the most), but alas it has
> >>> caught up to me and I have to bear down on it and put together
> >>> something which will make processing XML less painful when I have to
> >>> do it. *:-(
> >>> Thanks,
> >>> Ted
> >> Ted,
>
> > Hi Jerry,
>
> > Thanks.
>
> >> I doubt there's much you can do with MySQL itself unless it matches the
> >> XML produced by MySQL. *You'll probably have to use another language
> >> such as Perl or PHP - both of which have ways of working with XML files..
>
> > I know both and can work with either. *I have just avoided, or
> > ignored, development tools related to XML. *Do you have a favourite
> > Perl package for this?
>
> >> And I don't know why you can't store the data in the database. *It would
> >> be much more efficient than working with the xml data. *Things like
> >> column lengths are up to you - they just need to be big enough to hold
> >> the largest string you will store in them.
>
> > That is what I am aiming for. *I do not want to store the XML in the
> > database as a single string. *Rather, I want to extract the data from
> > the XML and store THAT in the database. *Once I have a handle on that,
> > everything else is easy, though tedious.
>
> > What I don't want to do is store the data as an XML string in a single
> > column. *I have seen cases where a DBA did exactly that, with no
> > thought on how to get at individual fields. *I am sure that that DBA
> > would have stored the files I'll get as a single string containing all
> > of the XML tags and data.
>
> > I can create a table for the main data, and set up several lookup
> > tables that would be appropriate, without breaking a sweat.
>
> > What I was asking for was recommendations on a relatively painless way
> > to get the data from the XML file into the tables I'd set up. *If the
> > vendor would provide the data in CSV files through their API, then I'd
> > have the script for getting and storing the data don in less than an
> > hour. *But, while I work with XML files in configuring my web server
> > and application server, I have not looked at writing a program to
> > parse XML to retrive data from it. *I guess it is mostly a question of
> > familiarity. *My greatest strengths are in raw number crunching in
> > fortran and especially C++. *At present, the prospect of writing code
> > to process XML feels like being a fish out of water.
>
> > Thanks again,
>
> > Ted
>
> Ted,
>
> I don't have any particular Perl package I like for this - I don't do
> all that much in Perl. *I'd recommend comp.lang.perl. *PHP has a couple
> of different ways; there I'd recommend comp.lang.php.
>
> You don't need to write the whole parser - that's been done for you.
> Just use the tools to parse the file and pull out your values.
>
> --
> ==================
> Remove the "x" from my email address
> Jerry Stuckle
> JDS Computer Training Corp.
> jstuck...@attglobal.net
> ==================- Hide quoted text -
>
> - Show quoted text -

Thanks Jerry.

I now have a collection of tools which look like they'll ease the task
at hand, once I figure out how to use them.

Cheers,

Ted