Re: xml to mySQL
Hi Adhiraj, I am going to assume that the keys and values are not static. If they are, then this is just a matter of parsing XML into key, value combinations. A simple, but often less than ideal way of solving a problem like this is to use several tables: # The entries table is the equivilant of something like the prymary key... CREATE TABLE `entries` ( `entry_id` int(10) NOT NULL auto_increment, `entry_name` varchar(255) NOT NULL default '', PRIMARY KEY (`entry_id`), KEY `entry_name` (`entry_name`) ) TYPE=MyISAM AUTO_INCREMENT=2 ; # The keys table holds collumn names... CREATE TABLE `keys` ( `key_id` int(10) NOT NULL auto_increment, `key` varchar(255) default NULL, PRIMARY KEY (`key_id`), KEY `key` (`key`) ) TYPE=MyISAM AUTO_INCREMENT=3 ; # The values table would hold the data... CREATE TABLE `values` ( `value_id` int(10) NOT NULL auto_increment, `value` varchar(255) default NULL, `key_id` int(10) default NULL, `entry_id` int(10) default NULL, PRIMARY KEY (`value_id`), KEY `value` (`value`),KEY `key_id` (`key_id`),KEY `entry_id` (`entry_id`) ) TYPE=MyISAM AUTO_INCREMENT=3 ; #Add in some data INSERT INTO `entries` (`entry_id`, `entry_name`) VALUES (1, 'Rob'); INSERT INTO `keys` (`key_id`, `key`) VALUES (1, 'age'), (2, 'nationality'); INSERT INTO `values` (`value_id`, `value`, `key_id`, `entry_id`) VALUES (1, '23', 1, 1), (2, 'American', 2, 1); # And now you can pull out information: SELECT `entry_name` ,`key`,`value` FROM `entries` INNER JOIN `values` USING(`entry_id`) INNER JOIN `keys` USING(`key_id`) WHERE `entry_name` = 'Rob' So, whenever you insert xml into the db you will first insert a single record into the entries table, and store the last insert id. You would then go through each key/value combination, first selecting ( and if no results are found, inserting) the relevant key from the keys table, and then inserting the key_id, entry_id and value into the values table. On 9/26/07, Adhiraj Joshi [EMAIL PROTECTED] wrote: Hi All, I wanted to know the possible ways in which an xml document can be stored in the mysql database. So the keys in an xml are the columns in the database and the key values in xml go as the values for columns. I dont want to use java if thats possible. Thanks in advance, Adhiraj Joshi. -- Rob Wultsch (480)223-2566 [EMAIL PROTECTED] (email/google im) wultsch (aim) [EMAIL PROTECTED] (msn) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: XML in MySQL
* [EMAIL PROTECTED] geez mysql is an open source product you cannot expect so much too soon over a very expensive commercial product which has been out for years , for xml i usually generate it on the fly when i am extracting the data using the field names as the xml tag nodes. Converting a relational dbms into an XML frontend is like trying to change a Chevrolet into a Beetle. (The problem is of course that too many managers orders too much xml these days.) -- Jon Haugsand, [EMAIL PROTECTED] http://www.norges-bank.no -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: XML in MySQL
- Original Message - From: Jon Haugsand [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Monday, June 23, 2003 2:57 AM Subject: Re: XML in MySQL * [EMAIL PROTECTED] geez mysql is an open source product you cannot expect so much too soon over a very expensive commercial product which has been out for years , for xml i usually generate it on the fly when i am extracting the data using the field names as the xml tag nodes. Converting a relational dbms into an XML frontend is like trying to change a Chevrolet into a Beetle. (The problem is of course that too many managers orders too much xml these days.) Hear, hear! A good place to look if you're interested in the views of relational DB purists (and what they think of XML, etc) is http://www.dbdebunk.com/ -S -- Jon Haugsand, [EMAIL PROTECTED] http://www.norges-bank.no -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: XML in MySQL
Jeremy Zawodny wrote: SOAP is one thing. But what about storing XML documents in the DB (with validation on the way in) and querying them using XPath? Oracle does that. MySQL does not. And off-topic from the original question but to repeat myself from before, using XML and MySQL together isn't that difficult in most situations you may encounter. A little wrapping to be sure, but validating parsers are easy to get your hands on and designing an expat-based parser that knows your schema (XML DB) for translation isn't a big chore for most data types I've come across. -- Michael T. Babcock C.T.O., FibreSpeed Ltd. http://www.fibrespeed.net/~mbabcock -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: XML in MySQL
On Sun, Jun 22, 2003 at 01:30:15PM -0700, Joel Amoussou wrote: Hello, What is the level of support for XML in MySQL. Is there any kit similar to the Oracle XML Developper Kit or the XML features in SQL Server 2000? No. MySQL doesn't nativel deal with XML. -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! [EMAIL PROTECTED] | http://jeremy.zawodny.com/ MySQL 4.0.13: up 19 days, processed 619,490,390 queries (366/sec. avg) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: XML in MySQL
but see SOAP and WSDL support add ons which are freely available and probably what you want anyway -C - Original Message - From: Jeremy Zawodny [EMAIL PROTECTED] To: Joel Amoussou [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Sent: Sunday, June 22, 2003 4:56 PM Subject: Re: XML in MySQL On Sun, Jun 22, 2003 at 01:30:15PM -0700, Joel Amoussou wrote: Hello, What is the level of support for XML in MySQL. Is there any kit similar to the Oracle XML Developper Kit or the XML features in SQL Server 2000? No. MySQL doesn't nativel deal with XML. -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! [EMAIL PROTECTED] | http://jeremy.zawodny.com/ MySQL 4.0.13: up 19 days, processed 619,490,390 queries (366/sec. avg) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: XML in MySQL
On Sun, Jun 29, 2003 at 07:53:00PM -0400, chad pratt wrote: but see SOAP and WSDL support add ons which are freely available and probably what you want anyway I don't buy that. If you've really looked at what Oracle is offering, it's hard to argue that the MySQL addons really solve the same problems. Have you seen otherwise? Maybe the addons are quite a bit better than last time I looked? Jeremy -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! [EMAIL PROTECTED] | http://jeremy.zawodny.com/ MySQL 4.0.13: up 19 days, processed 619,598,219 queries (366/sec. avg) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: XML in MySQL
PHP-SOAP, PEAR SOAP, NuSOAP need more SOAP? -C - Original Message - From: Jeremy Zawodny [EMAIL PROTECTED] To: chad pratt [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Sent: Sunday, June 22, 2003 5:04 PM Subject: Re: XML in MySQL On Sun, Jun 29, 2003 at 07:53:00PM -0400, chad pratt wrote: but see SOAP and WSDL support add ons which are freely available and probably what you want anyway I don't buy that. If you've really looked at what Oracle is offering, it's hard to argue that the MySQL addons really solve the same problems. Have you seen otherwise? Maybe the addons are quite a bit better than last time I looked? Jeremy -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! [EMAIL PROTECTED] | http://jeremy.zawodny.com/ MySQL 4.0.13: up 19 days, processed 619,598,219 queries (366/sec. avg) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: XML in MySQL
On Sun, Jun 29, 2003 at 08:18:24PM -0400, chad pratt wrote: PHP-SOAP, PEAR SOAP, NuSOAP need more SOAP? SOAP is one thing. But what about storing XML documents in the DB (with validation on the way in) and querying them using XPath? Oracle does that. MySQL does not. Jeremy -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! [EMAIL PROTECTED] | http://jeremy.zawodny.com/ MySQL 4.0.13: up 19 days, processed 620,269,579 queries (365/sec. avg) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: XML in MySQL
geez mysql is an open source product you cannot expect so much too soon over a very expensive commercial product which has been out for years , for xml i usually generate it on the fly when i am extracting the data using the field names as the xml tag nodes. -Original Message- From: Jeremy Zawodny [mailto:[EMAIL PROTECTED] Sent: Monday, June 23, 2003 7:55 AM To: chad pratt Cc: [EMAIL PROTECTED] Subject: Re: XML in MySQL On Sun, Jun 29, 2003 at 08:18:24PM -0400, chad pratt wrote: PHP-SOAP, PEAR SOAP, NuSOAP need more SOAP? SOAP is one thing. But what about storing XML documents in the DB (with validation on the way in) and querying them using XPath? Oracle does that. MySQL does not. Jeremy -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! [EMAIL PROTECTED] | http://jeremy.zawodny.com/ MySQL 4.0.13: up 19 days, processed 620,269,579 queries (365/sec. avg) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: XML in MySQL
On Mon, Jun 23, 2003 at 07:58:51AM +1000, electroteque wrote: geez mysql is an open source product you cannot expect so much too soon over a very expensive commercial product which has been out for years , for xml i usually generate it on the fly when i am extracting the data using the field names as the xml tag nodes. This has nothing to do with my expectations and everything to do with answering Joel Amoussou's question(s). As a long-time MySQL and open source software user, I'm well aware of the development differences between OS and commercial software. Jeremy -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! [EMAIL PROTECTED] | http://jeremy.zawodny.com/ MySQL 4.0.13: up 19 days, processed 620,460,949 queries (365/sec. avg) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: XML in MySQL
well you probably want to import and export XML into tables.. is this what you want to do? IF you want to process it qua XML as opposed to TEXT or BLOB then it 's not that hard, right? You have data in XML that needs to be put in columns and visa-versaI am not *certain* what you're looking for...here's a resource for you- http://www.xmlphp.com/ I am sure there are probably others books and packages out there.. that';s why God gave you Google.. -C - Original Message - From: chad pratt [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Sunday, June 29, 2003 7:53 PM Subject: Re: XML in MySQL but see SOAP and WSDL support add ons which are freely available and probably what you want anyway -C - Original Message - From: Jeremy Zawodny [EMAIL PROTECTED] To: Joel Amoussou [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Sent: Sunday, June 22, 2003 4:56 PM Subject: Re: XML in MySQL On Sun, Jun 22, 2003 at 01:30:15PM -0700, Joel Amoussou wrote: Hello, What is the level of support for XML in MySQL. Is there any kit similar to the Oracle XML Developper Kit or the XML features in SQL Server 2000? No. MySQL doesn't nativel deal with XML. -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! [EMAIL PROTECTED] | http://jeremy.zawodny.com/ MySQL 4.0.13: up 19 days, processed 619,490,390 queries (366/sec. avg) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
