By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
440,873 Members | 1,040 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 440,873 IT Pros & Developers. It's quick & easy.

What is the best way to store XML file in MySQL?

P: n/a
Hi,

I would like to store XML files in MySQL. What is the best solution:

1. Convert it to string and store it as CLOB/text
2. Serialize it and store as byte array
3. Flatten it out and create one column per element, each column is VARCHAR

Does MySQL has anything special for XML data?
Is there any software which helps to store XML data in MySQL
more efficiently?

Thank you,

Jacob Nikom
Jul 19 '05 #1
Share this Question
Share on Google+
12 Replies


P: n/a
On 15 Feb 2004 07:34:00 -0800, in mailing.database.mysql
ja*********@ieee.org (jacob nikom) wrote:
| Hi,
|
| I would like to store XML files in MySQL. What is the best solution:
|
| 1. Convert it to string and store it as CLOB/text
| 2. Serialize it and store as byte array
| 3. Flatten it out and create one column per element, each column is VARCHAR
|
| Does MySQL has anything special for XML data?
| Is there any software which helps to store XML data in MySQL
| more efficiently?


You could do any of the above. It depends upon what you plan to do
with the XML data once it is in the database.
---------------------------------------------------------------
jn****@yourpantsbigpond.net.au : Remove your pants to reply
---------------------------------------------------------------
Jul 19 '05 #2

P: n/a
On 15 Feb 2004 07:34:00 -0800, in mailing.database.mysql
ja*********@ieee.org (jacob nikom) wrote:
| Hi,
|
| I would like to store XML files in MySQL. What is the best solution:
|
| 1. Convert it to string and store it as CLOB/text
| 2. Serialize it and store as byte array
| 3. Flatten it out and create one column per element, each column is VARCHAR
|
| Does MySQL has anything special for XML data?
| Is there any software which helps to store XML data in MySQL
| more efficiently?


You could do any of the above. It depends upon what you plan to do
with the XML data once it is in the database.
---------------------------------------------------------------
jn****@yourpantsbigpond.net.au : Remove your pants to reply
---------------------------------------------------------------
Jul 19 '05 #3

P: n/a
On 15 Feb 2004 07:34:00 -0800, in mailing.database.mysql
ja*********@ieee.org (jacob nikom) wrote:
| Hi,
|
| I would like to store XML files in MySQL. What is the best solution:
|
| 1. Convert it to string and store it as CLOB/text
| 2. Serialize it and store as byte array
| 3. Flatten it out and create one column per element, each column is VARCHAR
|
| Does MySQL has anything special for XML data?
| Is there any software which helps to store XML data in MySQL
| more efficiently?


You could do any of the above. It depends upon what you plan to do
with the XML data once it is in the database.
---------------------------------------------------------------
jn****@yourpantsbigpond.net.au : Remove your pants to reply
---------------------------------------------------------------
Jul 19 '05 #4

P: n/a
Heya Jacob -
If the file is less than 10 megabytes - I usually will store an xml file in
varchar without any conversion.
We are talking ascii text here [with a few exceptions] but mostly you should
be ok.
I take an entire xml file and store it in mysql utilizing remote views with
vfp, mapping a memo field in vfp to varchar in mysql, all slung via odbc and
connection strings.
No Hiccups, so far.
mondo regards [Bill]
--
William Sanders / Electronic Filing Group Remove the DOT BOB to reply via
email.
Mondo Cool TeleCom -> http://www.efgroup.net/efgcog.html
Mondo Cool WebHosting -> http://www.efgroup.net/efglunar.html
Mondo Cool Satellites -> http://www.efgroup.net/sat
mySql / VFP / MS-SQL

"jacob nikom" <ja*********@ieee.org> wrote in message
news:6e**************************@posting.google.c om...
Hi,

I would like to store XML files in MySQL. What is the best solution:

1. Convert it to string and store it as CLOB/text
2. Serialize it and store as byte array
3. Flatten it out and create one column per element, each column is VARCHAR
Does MySQL has anything special for XML data?
Is there any software which helps to store XML data in MySQL
more efficiently?

Thank you,

Jacob Nikom

Jul 19 '05 #5

P: n/a
Heya Jacob -
If the file is less than 10 megabytes - I usually will store an xml file in
varchar without any conversion.
We are talking ascii text here [with a few exceptions] but mostly you should
be ok.
I take an entire xml file and store it in mysql utilizing remote views with
vfp, mapping a memo field in vfp to varchar in mysql, all slung via odbc and
connection strings.
No Hiccups, so far.
mondo regards [Bill]
--
William Sanders / Electronic Filing Group Remove the DOT BOB to reply via
email.
Mondo Cool TeleCom -> http://www.efgroup.net/efgcog.html
Mondo Cool WebHosting -> http://www.efgroup.net/efglunar.html
Mondo Cool Satellites -> http://www.efgroup.net/sat
mySql / VFP / MS-SQL

"jacob nikom" <ja*********@ieee.org> wrote in message
news:6e**************************@posting.google.c om...
Hi,

I would like to store XML files in MySQL. What is the best solution:

1. Convert it to string and store it as CLOB/text
2. Serialize it and store as byte array
3. Flatten it out and create one column per element, each column is VARCHAR
Does MySQL has anything special for XML data?
Is there any software which helps to store XML data in MySQL
more efficiently?

Thank you,

Jacob Nikom

Jul 19 '05 #6

P: n/a
Heya Jacob -
If the file is less than 10 megabytes - I usually will store an xml file in
varchar without any conversion.
We are talking ascii text here [with a few exceptions] but mostly you should
be ok.
I take an entire xml file and store it in mysql utilizing remote views with
vfp, mapping a memo field in vfp to varchar in mysql, all slung via odbc and
connection strings.
No Hiccups, so far.
mondo regards [Bill]
--
William Sanders / Electronic Filing Group Remove the DOT BOB to reply via
email.
Mondo Cool TeleCom -> http://www.efgroup.net/efgcog.html
Mondo Cool WebHosting -> http://www.efgroup.net/efglunar.html
Mondo Cool Satellites -> http://www.efgroup.net/sat
mySql / VFP / MS-SQL

"jacob nikom" <ja*********@ieee.org> wrote in message
news:6e**************************@posting.google.c om...
Hi,

I would like to store XML files in MySQL. What is the best solution:

1. Convert it to string and store it as CLOB/text
2. Serialize it and store as byte array
3. Flatten it out and create one column per element, each column is VARCHAR
Does MySQL has anything special for XML data?
Is there any software which helps to store XML data in MySQL
more efficiently?

Thank you,

Jacob Nikom

Jul 19 '05 #7

P: n/a
Do you only store it or you do something with it. like searching,
viewing, etc?

Jacob Nikom

"swdev1" <ws**********@advmed.com> wrote in message news:<K3****************@newssvr23.news.prodigy.co m>...
Heya Jacob -
If the file is less than 10 megabytes - I usually will store an xml file in
varchar without any conversion.
We are talking ascii text here [with a few exceptions] but mostly you should
be ok.
I take an entire xml file and store it in mysql utilizing remote views with
vfp, mapping a memo field in vfp to varchar in mysql, all slung via odbc and
connection strings.
No Hiccups, so far.
mondo regards [Bill]
--
William Sanders / Electronic Filing Group Remove the DOT BOB to reply via
email.
Mondo Cool TeleCom -> http://www.efgroup.net/efgcog.html
Mondo Cool WebHosting -> http://www.efgroup.net/efglunar.html
Mondo Cool Satellites -> http://www.efgroup.net/sat
mySql / VFP / MS-SQL

"jacob nikom" <ja*********@ieee.org> wrote in message
news:6e**************************@posting.google.c om...
Hi,

I would like to store XML files in MySQL. What is the best solution:

1. Convert it to string and store it as CLOB/text
2. Serialize it and store as byte array
3. Flatten it out and create one column per element, each column is

VARCHAR

Does MySQL has anything special for XML data?
Is there any software which helps to store XML data in MySQL
more efficiently?

Thank you,

Jacob Nikom

Jul 19 '05 #8

P: n/a
Do you only store it or you do something with it. like searching,
viewing, etc?

Jacob Nikom

"swdev1" <ws**********@advmed.com> wrote in message news:<K3****************@newssvr23.news.prodigy.co m>...
Heya Jacob -
If the file is less than 10 megabytes - I usually will store an xml file in
varchar without any conversion.
We are talking ascii text here [with a few exceptions] but mostly you should
be ok.
I take an entire xml file and store it in mysql utilizing remote views with
vfp, mapping a memo field in vfp to varchar in mysql, all slung via odbc and
connection strings.
No Hiccups, so far.
mondo regards [Bill]
--
William Sanders / Electronic Filing Group Remove the DOT BOB to reply via
email.
Mondo Cool TeleCom -> http://www.efgroup.net/efgcog.html
Mondo Cool WebHosting -> http://www.efgroup.net/efglunar.html
Mondo Cool Satellites -> http://www.efgroup.net/sat
mySql / VFP / MS-SQL

"jacob nikom" <ja*********@ieee.org> wrote in message
news:6e**************************@posting.google.c om...
Hi,

I would like to store XML files in MySQL. What is the best solution:

1. Convert it to string and store it as CLOB/text
2. Serialize it and store as byte array
3. Flatten it out and create one column per element, each column is

VARCHAR

Does MySQL has anything special for XML data?
Is there any software which helps to store XML data in MySQL
more efficiently?

Thank you,

Jacob Nikom

Jul 19 '05 #9

P: n/a
Do you only store it or you do something with it. like searching,
viewing, etc?

Jacob Nikom

"swdev1" <ws**********@advmed.com> wrote in message news:<K3****************@newssvr23.news.prodigy.co m>...
Heya Jacob -
If the file is less than 10 megabytes - I usually will store an xml file in
varchar without any conversion.
We are talking ascii text here [with a few exceptions] but mostly you should
be ok.
I take an entire xml file and store it in mysql utilizing remote views with
vfp, mapping a memo field in vfp to varchar in mysql, all slung via odbc and
connection strings.
No Hiccups, so far.
mondo regards [Bill]
--
William Sanders / Electronic Filing Group Remove the DOT BOB to reply via
email.
Mondo Cool TeleCom -> http://www.efgroup.net/efgcog.html
Mondo Cool WebHosting -> http://www.efgroup.net/efglunar.html
Mondo Cool Satellites -> http://www.efgroup.net/sat
mySql / VFP / MS-SQL

"jacob nikom" <ja*********@ieee.org> wrote in message
news:6e**************************@posting.google.c om...
Hi,

I would like to store XML files in MySQL. What is the best solution:

1. Convert it to string and store it as CLOB/text
2. Serialize it and store as byte array
3. Flatten it out and create one column per element, each column is

VARCHAR

Does MySQL has anything special for XML data?
Is there any software which helps to store XML data in MySQL
more efficiently?

Thank you,

Jacob Nikom

Jul 19 '05 #10

P: n/a
Hullo Jakob ..
I store it there because I want to parse it later .

When I parse it ? I use VFP to parse it ...

I assume at some point you will want to parse it, get the table and column
names and associated column values, and somehow recreate a table based on
the original xml file.

For that kinda work ? I only use mySql to STORE the xml file - not to parse
it and not to re-create the original table sets and data sets from the xml
file.

I understand [in passing] that there are php libraries to parse the xml
file - but I am a VFP geek and I prefer string parsing with VFP.

Usually when I have to parse its a file to be read for orders or catalog
entries , and has to feed different database systems [mySql, Oracle, ms-sql,
VFP] and I also prefer the feeding methods available to me in VFP. But I
digress - at this point ? Is purely my own personal choice based on
automation and coding time for same.

You can check it out as well - got the Visual Studio 6 Enterprise Edition CD
SET ? Pop in CD 1, and install VFP on your wintel boxen .

hth - mondo regards [Bill]

--
William Sanders / Electronic Filing Group Remove the DOT BOB to reply via
email.
Mondo Cool TeleCom -> http://www.efgroup.net/efgcog.html
Mondo Cool WebHosting -> http://www.efgroup.net/efglunar.html
Mondo Cool Satellites -> http://www.efgroup.net/sat
mySql / VFP / MS-SQL

"jacob nikom" <ja*********@ieee.org> wrote in message
news:6e**************************@posting.google.c om...
Do you only store it or you do something with it. like searching,
viewing, etc?

Jacob Nikom

"swdev1" <ws**********@advmed.com> wrote in message

news:<K3****************@newssvr23.news.prodigy.co m>...
Heya Jacob -
If the file is less than 10 megabytes - I usually will store an xml file in varchar without any conversion.
We are talking ascii text here [with a few exceptions] but mostly you should be ok.
I take an entire xml file and store it in mysql utilizing remote views with vfp, mapping a memo field in vfp to varchar in mysql, all slung via odbc and connection strings.
No Hiccups, so far.
mondo regards [Bill]
--
William Sanders / Electronic Filing Group Remove the DOT BOB to reply via email.
Mondo Cool TeleCom -> http://www.efgroup.net/efgcog.html
Mondo Cool WebHosting -> http://www.efgroup.net/efglunar.html
Mondo Cool Satellites -> http://www.efgroup.net/sat
mySql / VFP / MS-SQL

"jacob nikom" <ja*********@ieee.org> wrote in message
news:6e**************************@posting.google.c om...
Hi,

I would like to store XML files in MySQL. What is the best solution:

1. Convert it to string and store it as CLOB/text
2. Serialize it and store as byte array
3. Flatten it out and create one column per element, each column is

VARCHAR

Does MySQL has anything special for XML data?
Is there any software which helps to store XML data in MySQL
more efficiently?

Thank you,

Jacob Nikom

Jul 19 '05 #11

P: n/a
Hullo Jakob ..
I store it there because I want to parse it later .

When I parse it ? I use VFP to parse it ...

I assume at some point you will want to parse it, get the table and column
names and associated column values, and somehow recreate a table based on
the original xml file.

For that kinda work ? I only use mySql to STORE the xml file - not to parse
it and not to re-create the original table sets and data sets from the xml
file.

I understand [in passing] that there are php libraries to parse the xml
file - but I am a VFP geek and I prefer string parsing with VFP.

Usually when I have to parse its a file to be read for orders or catalog
entries , and has to feed different database systems [mySql, Oracle, ms-sql,
VFP] and I also prefer the feeding methods available to me in VFP. But I
digress - at this point ? Is purely my own personal choice based on
automation and coding time for same.

You can check it out as well - got the Visual Studio 6 Enterprise Edition CD
SET ? Pop in CD 1, and install VFP on your wintel boxen .

hth - mondo regards [Bill]

--
William Sanders / Electronic Filing Group Remove the DOT BOB to reply via
email.
Mondo Cool TeleCom -> http://www.efgroup.net/efgcog.html
Mondo Cool WebHosting -> http://www.efgroup.net/efglunar.html
Mondo Cool Satellites -> http://www.efgroup.net/sat
mySql / VFP / MS-SQL

"jacob nikom" <ja*********@ieee.org> wrote in message
news:6e**************************@posting.google.c om...
Do you only store it or you do something with it. like searching,
viewing, etc?

Jacob Nikom

"swdev1" <ws**********@advmed.com> wrote in message

news:<K3****************@newssvr23.news.prodigy.co m>...
Heya Jacob -
If the file is less than 10 megabytes - I usually will store an xml file in varchar without any conversion.
We are talking ascii text here [with a few exceptions] but mostly you should be ok.
I take an entire xml file and store it in mysql utilizing remote views with vfp, mapping a memo field in vfp to varchar in mysql, all slung via odbc and connection strings.
No Hiccups, so far.
mondo regards [Bill]
--
William Sanders / Electronic Filing Group Remove the DOT BOB to reply via email.
Mondo Cool TeleCom -> http://www.efgroup.net/efgcog.html
Mondo Cool WebHosting -> http://www.efgroup.net/efglunar.html
Mondo Cool Satellites -> http://www.efgroup.net/sat
mySql / VFP / MS-SQL

"jacob nikom" <ja*********@ieee.org> wrote in message
news:6e**************************@posting.google.c om...
Hi,

I would like to store XML files in MySQL. What is the best solution:

1. Convert it to string and store it as CLOB/text
2. Serialize it and store as byte array
3. Flatten it out and create one column per element, each column is

VARCHAR

Does MySQL has anything special for XML data?
Is there any software which helps to store XML data in MySQL
more efficiently?

Thank you,

Jacob Nikom

Jul 19 '05 #12

P: n/a
Hullo Jakob ..
I store it there because I want to parse it later .

When I parse it ? I use VFP to parse it ...

I assume at some point you will want to parse it, get the table and column
names and associated column values, and somehow recreate a table based on
the original xml file.

For that kinda work ? I only use mySql to STORE the xml file - not to parse
it and not to re-create the original table sets and data sets from the xml
file.

I understand [in passing] that there are php libraries to parse the xml
file - but I am a VFP geek and I prefer string parsing with VFP.

Usually when I have to parse its a file to be read for orders or catalog
entries , and has to feed different database systems [mySql, Oracle, ms-sql,
VFP] and I also prefer the feeding methods available to me in VFP. But I
digress - at this point ? Is purely my own personal choice based on
automation and coding time for same.

You can check it out as well - got the Visual Studio 6 Enterprise Edition CD
SET ? Pop in CD 1, and install VFP on your wintel boxen .

hth - mondo regards [Bill]

--
William Sanders / Electronic Filing Group Remove the DOT BOB to reply via
email.
Mondo Cool TeleCom -> http://www.efgroup.net/efgcog.html
Mondo Cool WebHosting -> http://www.efgroup.net/efglunar.html
Mondo Cool Satellites -> http://www.efgroup.net/sat
mySql / VFP / MS-SQL

"jacob nikom" <ja*********@ieee.org> wrote in message
news:6e**************************@posting.google.c om...
Do you only store it or you do something with it. like searching,
viewing, etc?

Jacob Nikom

"swdev1" <ws**********@advmed.com> wrote in message

news:<K3****************@newssvr23.news.prodigy.co m>...
Heya Jacob -
If the file is less than 10 megabytes - I usually will store an xml file in varchar without any conversion.
We are talking ascii text here [with a few exceptions] but mostly you should be ok.
I take an entire xml file and store it in mysql utilizing remote views with vfp, mapping a memo field in vfp to varchar in mysql, all slung via odbc and connection strings.
No Hiccups, so far.
mondo regards [Bill]
--
William Sanders / Electronic Filing Group Remove the DOT BOB to reply via email.
Mondo Cool TeleCom -> http://www.efgroup.net/efgcog.html
Mondo Cool WebHosting -> http://www.efgroup.net/efglunar.html
Mondo Cool Satellites -> http://www.efgroup.net/sat
mySql / VFP / MS-SQL

"jacob nikom" <ja*********@ieee.org> wrote in message
news:6e**************************@posting.google.c om...
Hi,

I would like to store XML files in MySQL. What is the best solution:

1. Convert it to string and store it as CLOB/text
2. Serialize it and store as byte array
3. Flatten it out and create one column per element, each column is

VARCHAR

Does MySQL has anything special for XML data?
Is there any software which helps to store XML data in MySQL
more efficiently?

Thank you,

Jacob Nikom

Jul 19 '05 #13

This discussion thread is closed

Replies have been disabled for this discussion.