XPATH_STRING
Description
The XPATH_STRING function is used to parse the XML string and return the first XML node that matches the XPath (opens in a new tab) 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 (opens in a new tab) 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 |
+----------------------------------------------------+- Handling CDATA and comments
SELECT xpath_string('<a><![CDATA[123]]></a>', '/a'), xpath_string('<a>123</a>', '/a');+-----------------------------------------------+---------------------------------------------------+
| xpath_string('<a><![CDATA[123]]></a>', '/a') | xpath_string('<a>123</a>', '/a') |
+-----------------------------------------------+---------------------------------------------------+
| 123 | 123 |
+-----------------------------------------------+---------------------------------------------------+