Collections:
EXTRACTVALUE() - Extracting Text Content from XML
How to extract text content from an XML document at a given location using the EXTRACTVALUE() function?
✍: FYIcenter.com
EXTRACTVALUE(xml, path) is a MySQL built-in function that extracts text content from an XML document at a given location. For example:
SET @xml = 'XY'; SELECT EXTRACTVALUE(@xml, '//b[1]'), EXTRACTVALUE(@xml, '//b[2]'); -- +------------------------------+------------------------------+ -- | EXTRACTVALUE(@xml, '//b[1]') | EXTRACTVALUE(@xml, '//b[2]') | -- +------------------------------+------------------------------+ -- | X | Y | -- +------------------------------+------------------------------+ SELECT EXTRACTVALUE(@xml, '/a'), EXTRACTVALUE(@xml, '//b'); -- +--------------------------+---------------------------+ -- | EXTRACTVALUE(@xml, '/a') | EXTRACTVALUE(@xml, '//b') | -- +--------------------------+---------------------------+ -- | | X Y | -- +--------------------------+---------------------------+ SELECT EXTRACTVALUE(@xml, 'count(//b)'), EXTRACTVALUE(@xml, 'count(//c)'); -- +----------------------------------+----------------------------------+ -- | EXTRACTVALUE(@xml, 'count(//b)') | EXTRACTVALUE(@xml, 'count(//c)') | -- +----------------------------------+----------------------------------+ -- | 2 | 0 | -- +----------------------------------+----------------------------------+
Note that variables used in the location path must be escaped with the '$' prefix. For example,
SET @i =1, @j = 2; SELECT EXTRACTVALUE(@xml, '//b[$@i]'), EXTRACTVALUE(@xml, '//b[$@j]'); -- +--------------------------------+--------------------------------+ -- | EXTRACTVALUE(@xml, '//b[$@i]') | EXTRACTVALUE(@xml, '//b[$@j]') | -- +--------------------------------+--------------------------------+ -- | X | Y | -- +--------------------------------+--------------------------------+
Reference information of the EXTRACTVALUE() function:
EXTRACTVALUE(xml, path): str Extracts text content from an XML document at a given location. Arguments, return value and availability: xml: Required. The XML document to be extracted from. path: Required. The location path where the value to be extracted. str: Return value. The extracted text content. Available since MySQL 4.0.
⇒ GET_LOCK() - Requesting User Defined Lock
⇐ DEFAULT() - Table Column Default Value
2023-12-17, 233🔥, 0💬
Popular Posts:
Where to find SQL Server Transact-SQL language references? You can find SQL Server Transact-SQL lang...
What Happens If the Imported Table Already Exists in Oracle? If the import process tries to import a...
How To Drop a Stored Procedure in Oracle? If there is an existing stored procedure and you don't wan...
Where to find MySQL database server tutorials? Here is a collection of tutorials, tips and FAQs for ...
How to set database to be READ_ONLY in SQL Server? Databases in SQL Server have two update options: ...