Select xpath values as separate rows in Oracle SQL

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

I had close to the same thing, but it didn't quite work with "Eat A Peach"'s answer. I had something like the following in a column as xmltype.

<?xml version="1.0" encoding="UTF-8"?><serviceRequestAnswer xmlns:fo="http://www.w3.org/1999/XSL/Format" xmlns:xs="http://www.w3.org/2001/XMLSchema" xmlns:fn="http://www.w3.org/2005/xpath-functions" xmlns:ns2="http://www.something.something/bla/v1">  <Persons>    <Person>      <InternalIdNumber>2935612467</InternalIdNumber>      <PublicIdNumber>9871256327</PublicIdNumber>      <FirstNames>Remy</FirstNames>      <LastName>Smith</LastName>      <BirthName>Smith</BirthName>      <BirthDate>19900101</BirthDate>      <PlaceOfBirth>0209</PlaceOfBirth>      <CountryOfBirth>6030</CountryOfBirth>      <Sex>M</Sex>      <Nationality>0001</Nationality>    </Person>    <Person>      <InternalIdNumber>7163584061</InternalIdNumber>      <PublicIdNumber>123432678</PublicIdNumber>      <FirstNames>Jesse</FirstNames>      <LastName>Smith</LastName>      <BirthName>Smith</BirthName>      <BirthDate>19900101</BirthDate>      <PlaceOfBirth>0012</PlaceOfBirth>      <CountryOfBirth>6030</CountryOfBirth>      <Sex>M</Sex>      <Nationality>0001</Nationality>    </Person>  </Persons></serviceRequestAnswer>

Let's call the column xmlcontent, and have this in a table named mytable. Then extracting the 2 public ID numbers as 2 rows can be done like so:

select  nvl(value (line).extract ('/Person/PublicIdNumber/text()').getstringval (),'') PublicIdfrom mytable, table ( xmlsequence (extract(xmlcontent,'serviceRequestAnswer/Persons/Person'))) line where id_mytable = 10092053;

Hope this helps someone :)