Syntax\n------\n\nEXTRACTVALUE(xml_frag, xpath_expr)\n\nDescription\n-----------\n\nThe EXTRACTVALUE() function takes two string arguments: a fragment of XML markup and an XPath expression, (also known as a locator). It returns the text (That is, CDDATA), of the first text node which is a child of the element or elements matching the XPath expression.\n\nIn cases where a valid XPath expression does not match any text nodes in a valid XML fragment, (including the implicit /text() expression), the EXTRACTVALUE() function returns an empty string.\n\nInvalid Arguments\n\nWhen either the XML fragment or the XPath expression is NULL, the EXTRACTVALUE() function returns NULL. When the XML fragment is invalid, it raises a warning Code 1525:\n\n``sql\nWarning (Code 1525): Incorrect XML value: 'parse error at line 1 pos 11: unexpected END-OF-INPUT'\n`\n\nWhen the XPath value is invalid, it generates an Error 1105:\n\n`sql\nERROR 1105 (HY000): XPATH syntax error: ')'\n`\n\nExplicit text() Expressions\n\nThis function is the equivalent of performing a match using the XPath expression after appending /text(). In other words:\n\n`sql\nSELECT\n   EXTRACTVALUE('<cases><case>example</case></cases>', '/cases/case') \n    AS 'Base Example',\n   EXTRACTVALUE('<cases><case>example</case></cases>', '/cases/case/text()') \n    AS 'text() Example';\n+--------------+----------------+\n| Base Example | text() Example |\n+--------------+----------------+\n| example      | example        |\n+--------------+----------------+\n`\n\nCount Matches\n\nWhen EXTRACTVALUE() returns multiple matches, it returns the content of the first child text node of each matching element, in the matched order, as a single, space-delimited string.\n\nBy design, the EXTRACTVALUE() function makes no distinction between a match on an empty element and no match at all. If you need to determine whether no matching element was found in the XML fragment or if an element was found that contained no child text nodes, use the XPath count() function.\n\nFor instance, when looking for a value that exists, but contains no child text nodes, you would get a count of the number of matching instances:\n\n`sql\nSELECT\n   EXTRACTVALUE('<cases><case/></cases>', '/cases/case') \n    AS 'Empty Example',\n   EXTRACTVALUE('<cases><case/></cases>', 'count(/cases/case)') \n    AS 'count() Example';\n+---------------+-----------------+\n| Empty Example | count() Example |\n+---------------+-----------------+\n|               |               1 |\n+---------------+-----------------+\n`\n\nAlternatively, when looking for a value that doesn't exist, count() returns 0.\n\n`sql\nSELECT\n   EXTRACTVALUE('<cases><case/></cases>', '/cases/person') \n    AS 'No Match Example',\n   EXTRACTVALUE('<cases><case/></cases>', 'count(/cases/person)') \n    AS 'count() Example';\n+------------------+-----------------+\n| No Match Example | count() Example |\n+------------------+-----------------+\n|                  |                0|\n+------------------+-----------------+\n`\n\nMatches\n\nImportant: The EXTRACTVALUE() function only returns CDDATA. It does not return tags that the element might contain or the text that these child elements contain.\n\n`sql\nSELECT \n  EXTRACTVALUE('<cases><case>Person<email>x@example.com</email></case></cases>', '/cases')\n   AS Case;\n+--------+\n| Case   |\n+--------+\n| Person |\n+--------+\n`\n\nNote, in the above example, while the XPath expression matches to the parent <case> instance, it does not return the contained <email>` tag or its content.\n\nExamples\n--------\n\nSELECT\n    ExtractValue('<a>ccc<b>ddd</b></a>', '/a')            AS val1,\n    ExtractValue('<a>ccc<b>ddd</b></a>', '/a/b')          AS val2,\n    ExtractValue('<a>ccc<b>ddd</b></a>', '//b')           AS val3,\n    ExtractValue('<a>ccc<b>ddd</b></a>', '/b')            AS val4,\n    ExtractValue('<a>ccc<b>ddd</b><b>eee</b></a>', '//b') AS val5;\n+------+------+------+------+---------+\n| val1 | val2 | val3 | val4 | val5    |\n+------+------+------+------+---------+\n| ccc  | ddd  | ddd  |      | ddd eee |\n+------+------+------+------+---------+\n\nURL: https://mariadb.com/docs/server/reference/sql-functions/string-functions/extractvalue