How to search a value from XML that store in MySQL

volant

Dear all,

I have a table to store the identity of my customers. Then I store all the information in XML format.

$xml =
"<Identity>
<ClientID>1234567</ClientID>
<Name>Kelly Jackson</Name>
<Age>18</Age>
<Country>Australia</Country>
</Identity>";

MySQL Table Structure:

| ClientID | XMLValue
| 1234567 | $xml

I want to search all the Clients with age >= 18 in XMLValue field.
What is the best approach for me to do in PHP in this case?

I know that MySQL5.1 introduce new function to search XML documents:
http://dev.mysql.com/tech-resources/articles/mysql-5.1-xml.html

Unfortunately our MySQL server is version 5.0 and does not support this feature 🙁

Appreciate your advice and suggestion.
Thank you!

bradgrafelman

volant wrote:
Then I store all the information in XML format.

But why do you do this? Why wouldn't you instead parse the XML data and store its contents in a meaningful format (e.g. either create columns in the DB to correspond with the different XML tags in the data, or create a second table with three columns: ClientID, FieldName, FieldValue - thus establishing a one-to-many relationship)?

volant

Dear sir,

Thanks for the response.

Reason to put all searching field in the column that store XML is i have too many columns for searching.

Assume i have 100 columns and 90 columns are searchable. I worry this would burden the database server.
Therefore instead of searching 90 columns, i m thinking to search 1 column only...

split into few tables is a good idea but i wonder if i can avoid from JOIN too many tables in one query...

bradgrafelman

volant;10969395 wrote:
Assume i have 100 columns and 90 columns are searchable. I worry this would burden the database server.
Therefore instead of searching 90 columns, i m thinking to search 1 column only...

The problem is that in both scenarios, you're searching through the same amount of data (or perhaps even more, due to the overhead of the XML markup). That's why it seems to me that it would make more sense to at least optimize the storage of this data so that it's easier to search through it in SQL.

Think of it like a library; would you rather have books split up across 90 rows and I ask you to search for a book in row #37, or would you rather search through 1 huge row containing the same amount of books as all 90 rows did?

volant

Dear sir,

Thank you for your advice. This help me to have better idea to proceed it. Thanks!