Updating an attribute in XML with MySQL

Question

I have a chunk of XML stored as a string in a MySQL database, and need to update one of the attributes using a query.

Given the following string:

<?xml version="1.0" encoding="UTF-8" standalone="yes"?>    
<town>
   <road name="Main" direction="north"/>
</town>

I would need to change it to update the attribute direction to a different value:

<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<town>
   <road name="Main" direction="east"/>
</town>

Is there an easy way to accomplish this? Thanks in advance!

EDIT: The query would be ran in a SQL script file containing various other upgrade queries, which is called by a piece of code in Java.

Answer 1

Checkout PHP's simple XML

http://www.php.net/manual/en/class.simplexmlelement.php

$xml=new SimpleXMLElement($xml);
print $xml->road->attributes('direction') = 'east';