MySQL - working with xml data

MySQL (5.1 and onward) provide XML functions; such as ExtractValue() that can be helpful to read required elements from xml; instead of returning entire xml back to the client application we can return one (or more) xml elements containing required data.

ExtractValue(xml_fragment, xpath_expression)

http://dev.mysql.com/doc/refman/5.1/en/xml-functions.html
In the following example, we use ExtractValue() with column content as the first argument. For the second argument, we employ an XPath expression that means “the content of the element having the name attribute "bankName"”:

SELECT ExtractValue(content, '//variable[@name="bankName"][1]') "bankName" from xml_test where id=2;  
 +----------+  
 | bankName |  
 +----------+  
 | xyz      |  
 +----------+  
 1 row in set (0.00 sec)  

 We can read more than one attribute:

SELECT ExtractValue(content, '//variable[@name="bankName"][1]') "bankName", ExtractValue(content, '//variable[@name="productType"][1]') "productType" from xml_test where id=1;  
 +----------+-------------+  
 | bankName | productType |  
 +----------+-------------+  
 | xyz      | cvv=***     |  
 +----------+-------------+  
 1 row in set (0.00 sec)  

Similarly we can extract all attributes of a particular xml element 

 SELECT ExtractValue(content,'//dictionary[@name="client_b"]/*') as "all attributes" from xml_test where id=2;  
 +----------------+  
 | all attributes |  
 +----------------+  
 | abc cvv=***    |  
 +----------------+  
 1 row in set (0.00 sec)