This discussion is archived
2 Replies Latest reply: Sep 13, 2012 7:34 AM by odie_63 RSS

Urgent!! ExtractValue function returns null.

960413 Newbie
Currently Being Moderated
My Oracle database has XML_TABLE in which the data of a column DETAILS is in .xml format as below.


<Headers>
     <Header2 Action="" AuditType="xxx">
          <A1>
          <A1 DataType="class java.lang.String"
          Type="OTHERS"
          Name="Flag"
          New="N"
          Old="Y"/>
          <A1 DataType="class java.lang.String"
          Type="OTHERS"
          Name="Draft"
          New=" "
          Old=" "/>
          <A1 DataType="class java.lang.String"
          Type="OTHERS"
          Name="Order"
          New="5"
          Old="10"/>
          </A1>
     </Header2>
</Headers>

I need to write a query to extract the value for the third A1 tag.

The query should basically filter the xml based on the value of Name = 'Order' and if satisfied it should return the value of New as 5 and Old as 10.


I tried using extractvalue() as below but it returns null.

select
extractvalue(xmltype(DETAIL),'*/Header2/A1/name') d1
from
XML_TABLE

Output
-----------------


d1
===
null
null
null

Kindly tell me what could be the possible solution.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points