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]
