1 Reply Latest reply: Sep 7, 2012 1:42 AM by AlexAnd RSS

    XML Parsing for specific data

    960413
      Hi All,

      There is an urgent requirement.

      Our Oracle database has XML_TABLE in which the data of a column DETAILS is in .xml format as below.
      <Headers>
      <Header2 Action="" ActionType="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>

      We 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 =5 and Old= 10.
      We tried using extractvalue() as below but it returns null.

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

      d1
      ===
      null
      null
      null

      Kindly let us know what could be the possible solution.
      TIA.
        • 1. Re: XML Parsing for specific data
          AlexAnd
          plz don't duplicate - Urgent!! ExtractValue function returns null.

          >
          There is an urgent requirement.
          >
          why not search on forum as first?

          >
          We need to write a query to extract the value for the third A1 tag.
          >
          SQL> select * from v$version where rownum=1;
           
          BANNER
          ----------------------------------------------------------------
          Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod
           
          SQL> 
          SQL> with XML_TABLE as
            2  (select xmltype(
            3  '<Headers>
            4  <Header2 Action="" ActionType="xxx">
            5  <A1>
            6  <A1 DataType="class java.lang.String"
            7  Type="OTHERS"
            8  Name="Flag"
            9  New="N"
           10  Old="Y"/>
           11  <A1 DataType="class java.lang.String"
           12  Type="OTHERS"
           13  Name="Draft"
           14  New=" "
           15  Old=" "/>
           16  <A1 DataType="class java.lang.String"
           17  Type="OTHERS"
           18  Name="Order"
           19  New="5"
           20  Old="10"/>
           21  </A1>
           22  </Header2>
           23  </Headers>') DETAIL from dual)
           24  select aName, aNew, aOld
           25  from XML_TABLE t,
           26          xmltable(
           27               '/Headers/Header2/A1/A1[3]'
           28                passing t.DETAIL
           29                columns
           30                        aName varchar2(30) path '@Name',
           31                        aNew varchar2(30) path '@New',
           32                        aOld varchar2(30) path '@Old'
           33              ) x
           34  /
           
          ANAME                          ANEW                           AOLD
          ------------------------------ ------------------------------ ------------------------------
          Order                          5                              10
           
          SQL> 
          The query should basically filter the xml based on the value of Name = 'Order' and if satisfied it should return the value of New =5 and Old= 10.
          >
          adding for previous
          SQL> with XML_TABLE as
            2  (select xmltype(
            3  '<Headers>
            4  <Header2 Action="" ActionType="xxx">
            5  <A1>
            6  <A1 DataType="class java.lang.String"
            7  Type="OTHERS"
            8  Name="Flag"
            9  New="N"
           10  Old="Y"/>
           11  <A1 DataType="class java.lang.String"
           12  Type="OTHERS"
           13  Name="Draft"
           14  New=" "
           15  Old=" "/>
           16  <A1 DataType="class java.lang.String"
           17  Type="OTHERS"
           18  Name="Order"
           19  New="5"
           20  Old="10"/>
           21  </A1>
           22  </Header2>
           23  </Headers>') DETAIL from dual)
           24  select aName, aNew, aOld
           25  from XML_TABLE t,
           26          xmltable(
           27               '/Headers/Header2/A1/A1[3][@Name="Order"]'
           28                passing t.DETAIL
           29                columns aName varchar2(30) path '@Name',
           30                        aNew varchar2(30) path '@New',
           31                        aOld varchar2(30) path '@Old'
           32              ) x
           33  /
           
          ANAME                          ANEW                           AOLD
          ------------------------------ ------------------------------ ------------------------------
          Order                          5                              10
           
          SQL>