2 Replies Latest reply: Sep 13, 2012 9:34 AM by odie_63 RSS

    Urgent!! ExtractValue function returns null.

    960413
      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.