Select xpath values as separate rows in Oracle SQL

Question

I need to select some values from an XML stored in a CLOB column in an Oracle database. The best I could come up with is the following:

select extract(xmltype(COLUMN), 'xpath-expression').getStringVal() as XMLVAL from TABLE t;

The problem is that when the XPATH selects multiple nodes, the values are concatenated. I need to have each selected node on a separate row. Obviously the concatenation must occur in getStringVal(), I use that because I need to have strings in my client (not XMLType). What should I use instead of getStringVal()?

EDIT: note that there is a similar question here: Oracle Pl/SQL: Loop through XMLTYPE nodes - but I couldn't apply it to my case. It uses two different XPATH expressions, and the principle of separation is not clear.

EDIT2: The XML is very complex, but basically I need to find the "some value" entries in

<string name="SOME_KEY" value="some value"/>

elements that are burried under many other elements. I use the XPATH //*[@name="SOME_KEY"]/@value and it finds successfully the value attribute of all the XML elements that have a SOME_KEY attribute.

Answer 1

Try this.

SELECT EXTRACTVALUE (x.COLUMN_VALUE, 'xpath-expression')
  FROM TABLE (
          SELECT XMLSEQUENCE (
                    xmltype (column).EXTRACT ('xpath-expression'))
            FROM t) x;

Sample at http://sqlfiddle.com/#!4/87af2/1