XPATH_STRING
Description
The XPATH_STRING function is used to parse the XML string and return the first XML node that matches the XPath expression.
tip
This function is supported since version 3.0.6.
Syntax
XPATH_STRING(<xml_string>, <xpath_expression>)
Parameters
| Parameter | Description |
|---|---|
<xml_string> | Source string. Type: VARCHAR |
<xpath_expression> | XPath expression. Type: VARCHAR |
Return Value
Returns VARCHAR type, representing the contents of the first XML node that matches the XPath expression.
Special cases:
- The function raises an error if xml or xpath are malformed.
Examples
- Basic node value extraction
SELECT xpath_string('<a>123</a>', '/a');
+-----------------------------------+
| xpath_string('<a>123</a>', '/a') |
+-----------------------------------+
| 123 |
+-----------------------------------+
- Nested element extraction
SELECT xpath_string('<a><b>123</b></a>', '/a/b');
+--------------------------------------------+
| xpath_string('<a><b>123</b></a>', '/a/b') |
+--------------------------------------------+
| 123 |
+--------------------------------------------+
- Using attributes
SELECT xpath_string('<a><b id="1">123</b></a>', '//b[@id="1"]');
+----------------------------------------------------------+
| xpath_string('<a><b id="1">123</b></a>', '//b[@id="1"]') |
+----------------------------------------------------------+
| 123 |
+----------------------------------------------------------+
- Using position predicates
SELECT xpath_string('<a><b>1</b><b>2</b></a>', '/a/b[2]');
+----------------------------------------------------+
| xpath_string('<a><b>1</b><b>2</b></a>', '/a/b[2]') |
+----------------------------------------------------+
| 2 |
+----------------------------------------------------+
- NULL input
SELECT xpath_string(NULL, '/a');
+--------------------------+
| xpath_string(NULL, '/a') |
+--------------------------+
| NULL |
+--------------------------+
- CDATA sections — the CDATA payload is returned as plain text
SELECT xpath_string('<a><![CDATA[123]]></a>', '/a');
+----------------------------------------------+
| xpath_string('<a><![CDATA[123]]></a>', '/a') |
+----------------------------------------------+
| 123 |
+----------------------------------------------+
- XML comments are skipped
SELECT xpath_string('<a><!-- comment -->123</a>', '/a');
+--------------------------------------------------+
| xpath_string('<a><!-- comment -->123</a>', '/a') |
+--------------------------------------------------+
| 123 |
+--------------------------------------------------+
- Path does not match any node — returns empty string
SELECT xpath_string('<a>123</a>', '/b');
+----------------------------------+
| xpath_string('<a>123</a>', '/b') |
+----------------------------------+
| |
+----------------------------------+
- Malformed XML — function raises an error
SELECT xpath_string('<a><!-- comment -->123/a>', '/a');
ERROR 1105 (HY000): errCode = 2, detailMessage = [INVALID_ARGUMENT]Function xpath_string failed to parse XML string: Start-end tags mismatch
Keywords
XPATH_STRING, XPATH, XML
