Get number of elements in XML object.

[Johan Louwers]

Dear all,
I have the following query:

select
(extractvalue(kml_data, '/kml/Document/Placemark[10]/description',
'xmlns="http://earth.google.com/kml/2.0"' )) as value
from XXX_TS_KML_DATA where url_id = 4


I am getting the value out of a XML datatype object which is located
in the 10e occurrence of the Placemark tag. Inside here I get the
description tag.

I always need the last occurrence of the "Placemark" tag. In this case
this is number 10 so all is working fine. However I need a way to find
out what the number of occurrences is, is there a way to do a count of
tags somehow? If you are interested in the XML source take a look at
the following URL:

http://maps.google.com/maps?f=d&saddr=universiteitsweg,+utrecht,+netherlands&daddr=molensteyn,+de+meern,+Utrecht+Netherlands&hl=en&geocode=&mra=ls&sll=52.082249,5.107098&sspn=0.051691,0.15398&ie=UTF8&ll=52.07191,4.993286&spn=0.20681,0.615921&t=h&z=11&output=kml


Thanks already!
Regards,
Johan Louwers

[Michael Moore]

Check this out. also note that I removed the namespace of the first kml tag and did other tweeks to the document so that it would work as quoted text.

SELECT EXTRACTVALUE (VALUE (d), '/description') the_key
  FROM TABLE
          (XMLSEQUENCE
              (EXTRACT
                  (XMLTYPE.createxml
                      ('<?xml version="1.0" encoding="UTF-8"?>
<kml>
  <Document>
    <name>Universiteitsweg, Utrecht, The Netherlands to Molensteyn, 3454 Utrecht, The Netherlands</name>
    <Style id="roadStyle">
      <LineStyle>
        <color>7fcf0064</color>
        <width>6</width>
      </LineStyle>
    </Style>
    <Snippet>
    </Snippet>
    <Placemark>
      <name>At the traffic circle, take the 2nd exit onto Oudenrijnseweg</name>
      <description>
        <![CDATA[go 0.6?#160;km]]>
      </description>
      <styleUrl>root://styleMaps#default+nicon=0x447+hicon=0x457</styleUrl>
      <Point>
        <coordinates>5.043430,52.071900,0</coordinates>
      </Point>
      <LookAt>
        <longitude>5.043430</longitude>
        <latitude>52.071900</latitude>
        <range>100.000000</range>
        <tilt>45.000000</tilt>
        <heading>103.543640</heading>
      </LookAt>
    </Placemark>
    <Placemark>
      <name>At the traffic circle, take the 1st exit onto Naamrijk</name>
      <description>
        <![CDATA[go 0.5?#160;km]]>
      </description>
      <styleUrl>root://styleMaps#default+nicon=0x447+hicon=0x457</styleUrl>
      <Point>
        <coordinates>5.046120,52.076800,0</coordinates>
      </Point>
      <LookAt>
        <longitude>5.046120</longitude>
        <latitude>52.076800</latitude>
        <range>100.000000</range>
        <tilt>45.000000</tilt>
        <heading>17.535595</heading>
      </LookAt>
    </Placemark>
    <Placemark>
      <name>Arrive at: Molensteyn, 3454 Utrecht, The Netherlands</name>
      <address>Molensteyn, 3454 Utrecht, The Netherlands</address>
      <styleUrl>root://styleMaps#default+nicon=0x467+hicon=0x477</styleUrl>
      <Point>
        <coordinates>5.052550,52.075780,0</coordinates>
      </Point>
    </Placemark>
    <Placemark>
      <name>Route</name>
      <description>
        <![CDATA[Distance: 18.6?#160;km (about 19 mins)<br/>Map data ?#169;2008 Tele Atlas ]]>
      </description>
      <GeometryCollection>
        <LineString>
        </LineString>
      </GeometryCollection>
      <styleUrl>#roadStyle</styleUrl>
    </Placemark>
  </Document>
</kml>'
                      ),
                   '/kml/Document/Placemark[last()]/description'
                  )
              )
          ) d
/