How to use XPATH in MySQL?

Question

Let's say I have a table called "xml" that stores XML files in a single "data" column. How to write a MySQL query that runs XPath and return only rows matching that XPath?

Answer 1

SELECT * FROM xml
WHERE EXTRACTVALUE(data, '<xpath-expr>') != '';

It should be noted, however, that there are restrictions on supporting MySQL XPath.

• EXTRACTVALUE() returns only CDATA.
• Not all XPath constructs are supported. Details are under the heading "XPath Limitations" on the doc page mentioned in abatishchev's answer.

Answer 2

I just got a response from a colleague, it seems that xml cropping often helps:

select * from xml where 
   trim(both '\r\n' from ExtractValue(xml, '/some/xpath')) = 'value';