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.

Can you get XMLType out of the select statement, then iterate over the results to return a string value for each one to the client? — LarsH
– LarsH, Commented May 24, 2013 at 15:03
@LarsH I tried that, but so far I only had errors and nulls in the output. — lbalazscs
– lbalazscs, Commented May 24, 2013 at 15:52

Noel · Accepted Answer · 2013-05-25 04:59:29Z

5

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

edited May 25, 2013 at 4:59

answered May 24, 2013 at 15:45

Noel

10.6k30 gold badges53 silver badges74 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

lbalazscs Over a year ago

it gives me Error: ORA-01427: single-row subquery returns more than one row. Also note that there is a SO answer similar to yours (stackoverflow.com/questions/985894/…) but it uses two different XPATHs, and it is PL/SQL.

lbalazscs Over a year ago

I edited my question to explain the XML structure and my XPATH

Noel Over a year ago

@lbalazscs, I have updated the answer with a sample sqlfiddle. Hope it helps.

lbalazscs Over a year ago

Thank you, following your example I could extract the name and value of all string elements, and later I filtered them with simple SQL.

Matt Byrne Over a year ago

sqlfiddle no longer loads :(

Rubenisme · Accepted Answer · 2015-02-17 14:19:39Z

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 (),'') PublicId
from mytable, table ( xmlsequence (extract(xmlcontent,'serviceRequestAnswer/Persons/Person'))) line 
where id_mytable = 10092053;

Hope this helps someone :)

This helped me. I've only needed to use xmltype(xmlcontent) in place of xmlcontent

Collectives™ on Stack Overflow

Select xpath values as separate rows in Oracle SQL

2 Answers 2

5 Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related