Skip to main content

xmlnode

1. XMLNODE Function​

The XMLNODE function in H2 database is used to extract a specific XML node from an XML document.

2. Syntax​

The syntax for the XMLNODE function in H2 database is as follows:

XMLNODE(xml, xpath)

Arguments​

  • xml: The XML document from which the node needs to be extracted. It should be a valid XML string or an XML column.
  • xpath: The XPath expression that specifies the node to be extracted.

Return​

  • The XMLNODE function returns the specified XML node as an XML string.

3. Notes​

  • The XMLNODE function in H2 database uses XPath 1.0 syntax for querying XML nodes.
  • If the XML document is not well-formed or the XPath expression is invalid, the XMLNODE function will return NULL.
  • Make sure to use the correct syntax and data type while using the XMLNODE function to avoid errors.

4. Examples​

Here are a few examples demonstrating the usage of the XMLNODE function in H2 database:

Example 1 - Extracting a single node from an XML document:​

SELECT XMLNODE('<bookstore><book><title>SQL Basics</title></book></bookstore>', '/bookstore/book/title') AS node;

Output:

node
-------------------
<title>SQL Basics</title>

Example 2 - Extracting multiple nodes from an XML column:​

CREATE TABLE books (
  id INT PRIMARY KEY,
  xml_data XML
);

INSERT INTO books VALUES (1, '<bookstore><book><title>SQL Basics</title></book><book><title>Advanced SQL</title></book></bookstore>');

SELECT id, XMLNODE(xml_data, '/bookstore/book/title') AS node
FROM books;

Output:

id |         node
---+-----------------------
1  | <title>SQL Basics</title>
1  | <title>Advanced SQL</title>
  • XMLEXISTS - Check if an XML node exists
  • XMLQUERY - Execute an XPath query on an XML document