Problem

Given an XML document, write a SQL query to find all the nodes with a given attribute value.
Example input:
<root>
<node attribute="value1">
<child attribute="value2"/>
</node>
<node attribute="value3">
<child attribute="value4"/>
</node>
</root>
Example output:
<node attribute="value1">
<child attribute="value2"/>
</node>
<node attribute="value3">
<child attribute="value4"/>
</node>

Solution

/*

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 a path expression as arguments. The path expression is used to extract the desired nodes from the XML document.

The XMLTABLE function returns a table with a column for each node in the path expression.

The XMLTABLE function is used in the FROM clause of the SQL query.

The WHERE clause is used to filter the results to only include rows where the value of the attribute is equal to the desired value.

*/

SELECT *
FROM XMLTABLE('/root/node'
              PASSING XMLPARSE(DOCUMENT '<root><node attr="value1"/><node attr="value2"/></root>')
              COLUMNS attr VARCHAR(100) PATH '@attr')
WHERE attr = 'value1';