Extracting XML data from NCLOB from XML with attributes in Oracle db

Question

I am trying to extract values from XML data stored as NCLOB column in Oracle db table.

xml structure

<?xml version="1.0" encoding="UTF-8"?>
<root xmlns="http://example.com/FAS/DOC/2011/v3.0b" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
  <record xmlns="http://example.com/FAS/DOC/2011/v3.0b">
    <pdate xmlns="http://example.com/FAS/DOC/2011/v3.0b">2014-05-15</pdate>
  </record>
</root>

Query

select EXTRACTVALUE(XMLTYPE(nclob_column),'/root/record/pdate','xmlns="http://example.com/FAS/DOC/2011/v3.0b"') pdate1,
       EXTRACTVALUE(XMLTYPE(nclob_column),'/root/record/pdate') pdate2
from   nclob_table

Problem

The pdate1 does return the value, but pdate2 returns null. I cannot use the third parameter of EXTRACTVALUE() to specify the xmlns attribute value as that changes on every row/record. So I get the value for one row but null for all others.

How do I extract the value without specifying the attribute?

Thanks.

har07 · Accepted Answer · 2015-05-19 02:39:10Z

0

If you can guarantee that the namespace doesn't matter for selecting an element in this case, you can use local-name() to match element only by it's local name, ignoring the namespace :

select EXTRACTVALUE(
            XMLTYPE(nclob_column),
            '/*[local-name()="root"]/*[local-name()="record"]/*[local-name()="pdate"]'
        ) pdate
from   nclob_table

SQL Fiddle

edited May 19, 2015 at 2:39

answered May 17, 2015 at 4:59

har07

89.5k12 gold badges87 silver badges143 bronze badges

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

Collectives™ on Stack Overflow

Extracting XML data from NCLOB from XML with attributes in Oracle db

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related