This discussion is archived
2 Replies Latest reply: Aug 8, 2012 5:38 AM by 919560 RSS

extract kml from xml

919560 Newbie
Currently Being Moderated
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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    thanks for the help!!

Legend

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