SQL Query to Return Value of XPath Expression

Problem

Given an XML document and an XPath expression, write a SQL query to return the value of the XPath expression.
Example input:
<books>
  <book>
    <title>Harry Potter and the Philosopher's Stone</title>
    <author>J.K. Rowling</author>
  </book>
  <book>
    <title>To Kill a Mockingbird</title>
    <author>Harper Lee</author>
  </book>
</books>
XPath expression: /books/book[1]/title
Example output:
Harry Potter and the Philosopher's Stone

Solution

This solution is in SQL. Our A.I. can create solutions in multiple languages.

sarastevens

/*

The solution below is optimal because it uses the XMLTABLE function to parse the XML document and return a table of values.

The XMLTABLE function takes an XML document and an XPath expression as input and returns a table of values.

The XPath expression in the example below is /books/book[1]/title.

The XPath expression is evaluated against the XML document and the value of the XPath expression is returned.

*/

SELECT x.title
FROM books,
XMLTABLE('/books/book[1]/title' PASSING books.xml_doc COLUMNS title VARCHAR(100) PATH '.') x;