2 Replies Latest reply: Aug 8, 2012 7:38 AM by 919560 RSS

    extract kml from xml

    919560
      hi!

      I have a problem with reading kml from xml.

      my xml file looks something like this:
      <spoints>
                <spoint>
                     <id>6</id>
                     <geometry>
                          <Point>
                               <extrude>0</extrude>
                               <tessellate>0</tessellate>
                               <altitudeMode>relativeToGround</altitudeMode>
                               <coordinates>15, 46 </coordinates>
                          </Point>
                     </geometry>
                </spoint>
                <spoint>
                     <id>7</id>
                     <geometry>
                          <Point>
                               <extrude>0</extrude>
                               <tessellate>0</tessellate>
                               <altitudeMode>relativeToGround</altitudeMode>
                               <coordinates>16, 47</coordinates>
                          </Point>
                     </geometry>
                </spoint>
                </spoints>

      i don't know how to read kml from this xml with extract value. i want to convert kml to sdo_geometry, so i need to get this in clob.

      id geometry
      6 '<Point><extrude>0</extrude><tessellate>0</tessellate><altitudeMode>relativeToGround</altitudeMode><coordinates>16, 47</coordinates></Point>'


      Can anybody help me? thanks in advance..
        • 1. Re: extract kml from xml
          odie_63
          Hi,

          Assuming you have loaded the file in a table :
          SQL> 
          SQL> create table tmp_kml_geometry (
            2    id  number
            3  , kml xmltype
            4  );
           
          Table created
           
          SQL> 
          SQL> insert into tmp_kml_geometry values (1, xmltype('<spoints>
            2  <spoint>
            3  <id>6</id>
            4  <geometry>
            5  <Point>
            6  <extrude>0</extrude>
            7  <tessellate>0</tessellate>
            8  <altitudeMode>relativeToGround</altitudeMode>
            9  <coordinates>15, 46 </coordinates>
           10  </Point>
           11  </geometry>
           12  </spoint>
           13  <spoint>
           14  <id>7</id>
           15  <geometry>
           16  <Point>
           17  <extrude>0</extrude>
           18  <tessellate>0</tessellate>
           19  <altitudeMode>relativeToGround</altitudeMode>
           20  <coordinates>16, 47</coordinates>
           21  </Point>
           22  </geometry>
           23  </spoint>
           24  </spoints>'));
           
          1 row inserted
           
          SQL> set long 5000
          SQL> 
          SQL> select x.id
            2       , xmlserialize(document x.geometry as clob) as clob_result
            3  from tmp_kml_geometry t
            4     , xmltable('/spoints/spoint'
            5         passing t.kml
            6         columns id        number  path 'id'
            7               , geometry  xmltype path 'geometry/*'
            8       ) x
            9  ;
           
                  ID CLOB_RESULT
          ---------- --------------------------------------------------------------------------------
                   6 <Point>
                       <extrude>0</extrude>
                       <tessellate>0</tessellate>
                       <altitudeMode>relativeToGround</altitudeMode>
                       <coordinates>15, 46 </coordinates>
                     </Point>
           
                   7 <Point>
                       <extrude>0</extrude>
                       <tessellate>0</tessellate>
                       <altitudeMode>relativeToGround</altitudeMode>
                       <coordinates>16, 47</coordinates>
                     </Point>
           
          • 2. Re: extract kml from xml
            919560
            thanks for the help!!