XML/MySQL: Storing complete XML in MySQL and performing CRUD operations.

Question

Is is possible to store directly complete XML file into MySQL db and perform CRUD operations on XML data present in MySQL ?

Scenario: I am getting XML file which has product related details like product_id, product_spec, product_price and many more and I have to store all this details into MySQL database and whenever user enter portal he selects for particular product from the product catalog and depending upon his selection shopping cart is populated i.e, depending upon user's selection shopping cart performs read operation on MySQL db to get the relevant data for the product, as an aside XML file which I am getting from 3rd party is very large as it has millions of products with all relevant details.

If it is possible than what are ways to do it ?

I'd appreciate guidance.

Answer 1

for very large XML files I think what you want is a streaming parser, as opposed to a DOM based parser. See http://us3.php.net/manual/en/book.xml.php and Google examples and read books. The question is too broad to offer much more than that I think.

Answer 2

Beginning with MySQL 5.1.5, two functions providing basic XPath 1.0 capabilities are available as explained in the chapter 11.10 XML Functions in the reference manual.

Check out MySQL 5.1's New XML Functions or Using XML in MySQL 5.1 and 6.0 for some examples.