Select xpath values ​​as separate rows in Oracle SQL

Question

I need to select some values ​​from the XML stored in the 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 XPATH selects multiple nodes, the values ​​are combined. I need each selected node on a separate line. Obviously, concatenation must happen in getStringVal (), I use this because I need to have strings in my client (and not in 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 could not apply it to my case. It uses two different XPATH expressions, and the principle of separation is not clear.

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

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

which clog under many other elements. I use XPATH //*[@name="SOME_KEY"]/@value and successfully find the value attribute of all XML elements that have the SOME_KEY attribute.

Answer 1

Try it.

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

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

Answer 2

I was close to the same thing, but he did not quite work with the answer "Eat a peach." I had something like xmltype in the column.

 <?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 us call the xmlcontent column and have it in a table called mytable. Then, extracting 2 public identification numbers as 2 lines can be performed as follows:

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

Hope this helps someone :)