1 Reply Latest reply: Jul 29, 2012 9:40 AM by odie_63 RSS

    Extract data from xmltype column

    382630
      Dear Folks...

      I have column with datatype : xmltype. I have a problem when to extract attribute value. Xml record:

      <data>
      <nama>Joko Tingkir</nama>
      <alamat>JL Rasuna No.11</alamat>
      <alamat addr="2">Kuningan</alamat>
      <alamat addr="3">Jak-Sel</alamat>
      </data>
      <data>
      <nama>Asep Kasep</nama>
      <alamat>JL Raya Bogor Blok A3</alamat>
      <alamat addr="2">Cimanggis</alamat>
      <alamat addr="3">Depok</alamat>
      <alamat addr="4">Jawa Barat</alamat>
      </data>


      I expect the result should be :

      Jl Rasuna No.11, Kuningan, Jak-Sel
      Jl Raya Bogor Blok A3, Cimanggis, Depok, Jawa Barat


      But when I try to extract it whith this query :

      Select extract (xmlcolumn, ("/data/alamat/text()").getStringVal() from table_xml;


      But the result not as expected, the element "alamat" have several attribute "addr" which is have inconsistent value.
      And the value not separated by space or comma:


      Jl Rasuna No.11KuninganJak-Sel
      Jl Raya Bogor Blok A3CimanggisDepokJawa Barat


      Thanks for your help.

      Regards,


      Fahmi
        • 1. Re: Extract data from xmltype column
          odie_63
          Hi,
          I have column with datatype : xmltype. I have a problem when to extract attribute value. Xml record:
          <data>
          <nama>Joko Tingkir</nama>
          ...
          I guess that's not really the content of your column, as you can't insert XML fragments (no root element) in an XMLType column.

          This query is not even syntactically correct :
          Select extract (xmlcolumn, ("/data/alamat/text()").getStringVal() from table_xml;
          If you want help, please post at least a working test case, and always give your exact database version.


          Assuming your XML has a root element, you can do something like :
          SQL> create table table_xml ( xmlcolumn xmltype );
           
          Table created
           
          SQL> 
          SQL> insert into table_xml (xmlcolumn)
            2  values (
            3  xmlparse(document '<root>
            4  <data>
            5     <nama>Joko Tingkir</nama>
            6     <alamat>JL Rasuna No.11</alamat>
            7     <alamat addr="2">Kuningan</alamat>
            8     <alamat addr="3">Jak-Sel</alamat>
            9  </data>
           10  <data>
           11     <nama>Asep Kasep</nama>
           12     <alamat>JL Raya Bogor Blok A3</alamat>
           13     <alamat addr="2">Cimanggis</alamat>
           14     <alamat addr="3">Depok</alamat>
           15     <alamat addr="4">Jawa Barat</alamat>
           16  </data>
           17  </root>')
           18  );
           
          1 row inserted
           
          SQL> 
          SQL> select x.*
            2  from table_xml t
            3     , xmltable('/root/data'
            4        passing t.xmlcolumn
            5        columns nama   varchar2(30)  path 'nama'
            6              , alamat varchar2(200) path 'string-join(alamat, ",")'
            7       ) x
            8  ;
           
          NAMA                           ALAMAT
          ------------------------------ --------------------------------------------------------------------------------
          Joko Tingkir                   JL Rasuna No.11,Kuningan,Jak-Sel
          Asep Kasep                     JL Raya Bogor Blok A3,Cimanggis,Depok,Jawa Barat
           
          SQL>