Replace SQL statements in XML with query results

Question

Hey everyone,

I have functions in place that will convert the results of sql queries into XML. However, I now want to be able to, using PHP, read in an XML document (that has nested SQL statements), execute those statements, and essentially replace the sql statement with the results.

(Just a note, using PostgreSQL)

For example:

 <customers>
    <customer>
       <info>* from customer for where customer = 1</info>
          <details>
             <po>
                <info>* from po join where customer = "master" customer</info>
             </po>
             <order_history>
                <info>* from order_history where customer = "master" customer</info>
             </order_history>
          </details>
    </customer>
    <customer>
       <info>* from customer for where customer = 2</info>
          <details>
             <po>
                <info>* from po join where customer = "master" customer</info>
             </po>
             <order_history>
                <info>* from order_history where customer = "master" customer</info>
             </order_history>
          </details>
    </customer>
    ...
 </customers>

I am confident in reading in the XML document and querying the DB with the SQL statements, however I am not to sure how to go about populating the results in the XML structure.

Some sort of text replacement? Or simultaneously rebuild a duplicate XML document with results? Any suggestions? Thanks...

Also, these queries are correlated in the sense that the sub-queries (or higher level queries) depend on the lower level queries...

Answer 1

Is your question: would it be acceptable performance-wise to recreate another XML structure? If so, my answer: you cannot know until you try.

You said, you already have a function to create XML for 1 query. Now you can write a function on top of it: Take (Copy?) the XML that you read from the SQL-Query-XML, and insert, sort of "embed" the XML given from the SQL_TO_QUERY()-function (replacing the query?).

If you need more details, please give more details: which PHP-Functions are you using to manipulate XML, SimpleXML? How big is the Database-XML-File? Why exactly can't you do a general statement for all customers?

Answer 2

This may not be what you're looking for but I suggest not including SQL in the XML if you can avoid it. If your application grows you may end up wanting to use something else to store data (memcache?) and then you'll have to write a SQL->(something else) translator.

So, something more like:

<customers>
  <customer id="1">
    <fetch />
    <details>
      <po>
        <fetch />
      </po>
...

Then in your PHP you can parse the XML with the DOM methods. When you run across a customer tag you set the "current customer id", remove the subordinate fetch tag, perform whatever query you want, add tags containing the results, and then move on down the tree to the next tags.

When you hit the fetch tag inside the po tag you can fetch the current customer's PO data, repeating the process above (remove, query, add tags).

The output will contain the customer id in the same format as the input, making it easy to correlate output and input.