Using the XQUERY node function to extract values into rows

Question

My question is identical to this one but instead of SQL Server I am using Oracle 12.1.

I would like to extract all the name elements from the following xml document:

<ci5>
   <employee>
      <name>George Cowley</name>  
      <salary via="B">80000</salary>
   </employee>
   <employee>
      <name>William Bodie</name>  
      <salary via="C">40000</salary>
   </employee>
   <employee>
      <name>Ray Doyle</name>  
      <salary via="C">40000</salary>
   </employee>
</ci5>

The output I want is three separate rows such as:

<name>George Cowley</name>
<name>William Bodie</name>
<name>Ray Doyle</name>

Using the following queries

SELECT XMLQUERY('ci5/employee/name' 
                  PASSING BY VALUE XMLTYPE.CREATEXML('<ci5><employee><name>George Cowley</name><salary via="B">80000</salary></employee><employee><name>William Bodie</name><salary via="C">40000</salary></employee><employee><name>Ray Doyle</name><salary via="C">40000</salary></employee></ci5>') RETURNING CONTENT) x
   FROM dual;  

 SELECT XMLQUERY('for $i in /ci5
                 return $i/employee/name'
                 PASSING BY VALUE XMLTYPE.CREATEXML('<ci5><employee><name>George Cowley</name><salary via="B">80000</salary></employee><employee><name>William Bodie</name><salary via="C">40000</salary></employee><employee><name>Ray Doyle</name><salary via="C">40000</salary></employee></ci5>') RETURNING CONTENT) x
   FROM dual;

the names are shown, but the output is all on one line:

<name>George Cowley</name><name>William Bodie</name><name>Ray Doyle</name>

It looks like I need to use the NODE function but I don't how to use it within an Oracle context.

OldProgrammer · Accepted Answer · 2015-03-22 22:06:59Z

1

I don't think you need any xquery for this. A simple xpath expression should suffice. Here are two options. The first returns an XML sequence (separate xml nodes), the second returns as table rows:

    WITH test AS
      (SELECT xmltype('<ci5>   
    <employee>      
    <name>George Cowley</name>        
    <salary via="B">80000</salary>   
    </employee>   
    <employee>      
    <name>William Bodie</name>        
    <salary via="C">40000</salary>   
    </employee>   
    <employee>      
    <name>Ray Doyle</name>        
    <salary via="C">40000</salary>   
    </employee>
    </ci5>  ') data
      FROM dual
      )
    SELECT xmlsequence(extract(data,'/ci5/employee/name')) FROM test

WITH test AS
      (SELECT xmltype('<ci5>   
    <employee>      
    <name>George Cowley</name>        
    <salary via="B">80000</salary>   
    </employee>   
    <employee>      
    <name>William Bodie</name>        
    <salary via="C">40000</salary>   
    </employee>   
    <employee>      
    <name>Ray Doyle</name>        
    <salary via="C">40000</salary>   
    </employee>
    </ci5>  ') data
      FROM dual
      )
    SELECT extract(value(d), '*') AS name
    FROM test,
      TABLE(xmlsequence(extract(data, '/ci5/employee/name'))) d

answered Mar 22, 2015 at 22:06

OldProgrammer

12.2k4 gold badges27 silver badges52 bronze badges

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

1 Comment

Ian Carpenter Over a year ago

Thanks I appreciate the response, I didn't want to fill up my question with cruft but the reason for using XQUERY is that I am getting up to speed with it because extract(XML) is deprecated @ 11GR2 see: docs.oracle.com/cd/E11882_01/server.112/e41084/…

Ian Carpenter · Accepted Answer · 2015-03-23 21:58:47Z

0

This Oracle white paper answered the question, I needed to use XMLTABLE which

...maps the result of an XQuery evaluation into relational rows and columns...

Changing my query to use XMLTABLE

 SELECT x.ename
   FROM XMLTABLE('/ci5/employee/name'                 
                 PASSING XMLTYPE.CREATEXML('<ci5><employee><name>George Cowley</name><salary via="B">80000</salary></employee><employee><name>William Bodie</name><salary via="C">40000</salary></employee><employee><name>Ray Doyle</name><salary via="C">40000</salary></employee></ci5>') 
                 COLUMNS ename VARCHAR2(30) PATH '/name') x

gives the following output in SQL Developer:

enter image description here

edited Mar 23, 2015 at 21:58

answered Mar 22, 2015 at 22:17

Ian Carpenter

8,6566 gold badges55 silver badges86 bronze badges

Collectives™ on Stack Overflow

Using the XQUERY node function to extract values into rows

2 Answers 2

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related