Store XML Object in MySQL

[southbeach]

Working on an API integration where responses are received in XML. There could be any number of sources and each have their own format.

Now, since I am dealing with 45 to 50 different sources, I figure that the best option would be to extract from each whatever I may need for searching/indexing purpose and store the entire XML Object in a column.

The idea is that once the XML content is needed, I can/should simply extract the object from the column, just as you would any other column, and do what needed.

For the life of me, I have search high and low and a couple of hours later, all I find is suggestions to parse the XML document and store the individual values ... but as I said, no two of these people use identical format.

If I save it in a BLOB field, how then do I read it later? is this possible at all?


--
SouthBeach

http://www.fp2php.com

The good thing about not knowing is the opportunity to learn - Yours truly, 2008.

 

[feherke]

Hi

[tt]blob[/tt] ? Binary Large Object ? But XML is text. Why not use a field of type [tt]text[/tt] instead ?


Feherke.
feherke.ga

 

[vacunita]

You can store it in a blob field if you want to, but as Feherke points out a Text field in MYSQL may be more adequate for XML.

As to how you read it, you read it like you read any field in MYSQL. Run a select to get the field you want and read the XML. Since XML is just text, once you get it back from the query and into a variable you can do with it whatever you want.




----------------------------------
Phil AKA Vacunita
----------------------------------
OS-ception: Running Linux on a Virtual Machine in Windows which itself is running in a Virtual Machine on Mac OSx.

Web & Tech

 

[southbeach]

I have tried both, text and blob but nothing seem to be stored.

Come to think of it, I am not serializing nor escaping it ... Let me try that!

--
SouthBeach

http://www.fp2php.com

The good thing about not knowing is the opportunity to learn - Yours truly, 2008.

 

[southbeach]

So, the problem was my attempting to store simplexml object instead of the XML document itself.

Spotting this was one of those moment ...

--
SouthBeach

http://www.fp2php.com

The good thing about not knowing is the opportunity to learn - Yours truly, 2008.