Select Data from XML string in MYSQL

Question

I am trying to select certain values that are posted back to our sql server from an xml into one field as a string..

Example:

<?XMLVERSION><result><id>0</id><response>success</response><did>B2A5DY53</did><price>0.00</price></result>

How would I select just the value between "price" ' ' "/price"?

Ike Walker · Accepted Answer · 2014-07-18 20:53:39Z

1

You can use the built-in ExtractValue() function for this:

SELECT ExtractValue('<result>
<id>0</id>
<response>success</response>
<did>B2A5DY53</did>
<price>0.00</price>
</result>', '/result/price')

answered Jul 18, 2014 at 20:53

Ike Walker

65.8k14 gold badges115 silver badges112 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Is it not possible to use the field name like: SELECT ExtractValue(xml_field, '/result/price')?

Yes, you can use a column name as the first argument of ExtractValue(). I only used an XML string since you used one in your example.