Select xml element value in Oracle

Question

I am trying to extract a value from an xml element, located in an XMLTYPE column in an Oracle Table. The xml element which I am trying to extract have a parent for which a namespace is defined. The xml looks something like:

<a>
  <b xmlns="urn:www.someSite.com/myModel">
    <c>my value</c>
  </b>
</a>

If I want to extract the content of the "a" element, its context is correctly returned:

SELECT Extract(myColumn, '/a') FROM myTable;

But for returning the content of the "c" element I didn't succeed to find any version to work. The following instructions does not work:

SELECT Extract(myColumn, '/a/b/c') FROM myTable;

SELECT Extract(myColumn, '/a/b/c', 'xmlns="urn:www.someSite.com/myModel"') FROM myTable;

SELECT Extract(myColumn, '/a/b/c', 'urn:www.someSite.com/myModel') FROM myTable;

Can anybody help me, with the extract statement that would work in this case?

Jokke Heikkilä · Accepted Answer · 2012-04-02 12:28:54Z

5

select a.*
from   XMLTABLE(
         XMLNAMESPACES('urn:www.someSite.com/myModel' AS "ns"),
         '/*'
         PASSING my.myColumn           
         COLUMNS
           val  VARCHAR2(2000)   PATH '/a/ns:b/ns:c'
       ) a, myTable my;

edited Apr 2, 2012 at 12:28

answered Apr 2, 2012 at 12:04

Jokke Heikkilä

9281 gold badge8 silver badges17 bronze badges

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

1 Comment

REW Over a year ago

I will add that this is also the better modern solution now since extract/extractvalue are depreciated.

A.B.Cade · Accepted Answer · 2012-04-02 11:56:52Z

4

Since the a element does not have the namespace, you can first extract its child elements without using namespaces in the function, and then extract the value from the b with the namespace:
Try:

select extract(extract(myColumn, 'a/*'),
               'b/c/text()',
               'xmlns=urn:www.someSite.com/myModel') 
  from myTable

answered Apr 2, 2012 at 11:56

A.B.Cade

16.9k1 gold badge40 silver badges55 bronze badges

Comments

Miguel Angel Maroto · Accepted Answer · 2014-12-11 13:08:57Z

0

When the default namespace changes, one way to specify namespaces is to write the wildcard character ('*') and the local-name() and namespace-uri() XPath functions.

select extract(myColumn, '/a/*[local-name()='b' and namespace-uri()='urn:www.someSite.com/myModel']/*[local-name()='c' and namespace-uri()='urn:www.someSite.com/myModel']') from myTable

answered Dec 11, 2014 at 13:08

Miguel Angel Maroto

1

Comments

Mureinik · Accepted Answer · 2014-03-25 13:29:38Z

-1

SELECT XMLAGG(XMLELEMENT(E,ename||',')).EXTRACT('//text()') "Result"
FROM   emp

edited Mar 25, 2014 at 13:29

Mureinik

316k54 gold badges402 silver badges406 bronze badges

answered Mar 25, 2014 at 13:10

sony

1

Collectives™ on Stack Overflow

Select xml element value in Oracle

4 Answers 4

1 Comment

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

1 Comment

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related