XML parsing via Extractvalue in Oracle returning null

Question

I am trying to parse the XML below residing in a CLOB column in an Oracle table but it is returning null.

I am trying to retrieve the ElementValue=36 from the below XML.

Please advise.

<?xml version="1.0" encoding="iso-8859-1"?>
<AttributeContent xmlns="urn:schemas.td.com/ENT/AttributeContent" xmlns:xsdi="http://www.w3.org/2001/XMLSchema-instance" xsdi:schemaLocation="urn:schemas.td.com/ENT/AttributeContent/AttributeContent.xsd">
   <ContentElements>
    <ElementName>value</ElementName>
    <ElementValue>36</ElementValue>  
  </ContentElements> 
  <ContentElements>  
    <ElementName>flag</ElementName>
    <ElementValue>Y</ElementValue>  
  </ContentElements>
 </AttributeContent>

SQL used for parsing:

SELECT  EXTRACTVALUE(xmltype(ATTRIBUTE_CONTENT),'/AttributeContent/ContentElements/Elementvalue[1]')
-- INTO    l_batchCycle     
FROM    fea_com_ctrl.control_attribute 
WHERE   attribute_name = 'BATCH_CYCLE'
and     end_date is null;

Table definition:

ATTRIBUTE_NAME  VARCHAR2(30 BYTE)
EFFECTIVE_DATE  DATE
END_DATE    DATE
ATTRIBUTE_CONTENT   CLOB

Evgeniy K. · Accepted Answer · 2016-07-29 01:54:00Z

1

Try this

EXTRACTVALUE(
              xmltype(ATTRIBUTE_CONTENT),
              '/AttributeContent/ContentElements[1]/ElementValue',
               'xmlns="urn:schemas.td.com/ENT/AttributeContent"')

answered Jul 29, 2016 at 1:54

Evgeniy K.

1,1477 silver badges11 bronze badges

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

Collectives™ on Stack Overflow

XML parsing via Extractvalue in Oracle returning null

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