Manipulating XML using XPATH in PHP and dynamic query generation
The easiest way to extract the values from an XML document is to create a DOM (Document Object Model) from the document and use XPATH to identify the individual elements and attributes. The new versions of PHP provide built in DOM functionality however if it is not there is an open source API (http://sourceforge.net/projects/phpxpath/) that does exactly this. The following is a code snippet using this API.
<?php
require_once('../commonfiles/XPath.class.php');
$user = "<User><user_id>ID0001</user_id></User>";
$xpath = new XPath();
$xpath->importFromString($user);
$value= $xpath->getData('/User/user_id'));
?>
The next challenge is to dynamically generate the query given the XML. This is quite simple as the tag-names in our XML directly correspond to the table names and the column-names for those tables. This allows us to dynamically generate the SQL corresponding to the elements in the provided XML. For example given the following XML as criteria for one of our find methods:
<User><surname>Jackson</surname><preffered-genre>pop</preffered-genre></User>
The query in SQL would look like the following:
SELECT * FROM User WHERE surname=’Jackson’ AND prefered-genre=’pop’
As you can see, all the dymanic parts of the query can be extracted from the XML itself. The same could be accomplished for the insert and update queries.
NOTE: the above somewhat simplifies the complications involved in dynamic query generation. For example how would you make sure that there are no enclosing commas for numeric values and how you would implement SQL clauses to match patterns (e.g. LIKE ??%@mac.com??). However these could be accomplished using different techniques such as maintaining a data dictionary or factoring in additional metadata from the resultset. These issues are deliberately ignored as they are beyond the scope of this brief article.
