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, 2360🔥, 0💬
Popular Posts:
What Happens to an Arithmetic Operation with Two Different Data Types in SQL Server Transact-SQL? Wh...
How To Break Query Output into Pages in MySQL? If you have a query that returns hundreds of rows, an...
Where to find answers to frequently asked questions on INSERT, UPDATE and DELETE Statements in MySQL...
Where to find MySQL database server tutorials? Here is a collection of tutorials, tips and FAQs for ...
What Is the Difference Between GETDATE() and GETUTCDATE() in SQL Server Transact-SQL? The difference...