XML Google Maps

Dúvidas, dicas e truques de PL/SQL. Aqui também vão assuntos relacionados a pacotes, triggers, funções, Java-Stored Procedures, etc
Responder
mauricio_mendes
Rank: Estagiário Pleno
Rank: Estagiário Pleno
Mensagens: 6
Registrado em: Ter, 13 Jan 2009 9:05 am
Localização: Porto Alegre

Bom dia,

estou tendo dificuldade em extrair as informações do XML que retorna da API V3 do google
segue o XML.

Como podem ver não existe nenhum tipo de id e o nome dos campos são os mesmo, então estou tendo dificuldade em coletar essas informações.

Agradeço a ajuda

Selecionar tudo

<?xml version="1.0" encoding="UTF-8"?>
<GeocodeResponse>
 <status>OK</status>
 <result>
  <type>street_address</type>
  <formatted_address>Avenida Farrapos, 6 - Floresta, Porto Alegre - Rio Grande do Sul, Brazil</formatted_address>
  <address_component>
   <long_name>6</long_name>
   <short_name>6</short_name>
   <type>street_number</type>
  </address_component>
  <address_component>
   <long_name>Avenida Farrapos</long_name>
   <short_name>Av. Farrapos</short_name>
   <type>route</type>
  </address_component>
  <address_component>
   <long_name>Floresta</long_name>
   <short_name>Floresta</short_name>
   <type>neighborhood</type>
   <type>political</type>
  </address_component>
  <address_component>
   <long_name>Porto Alegre</long_name>
   <short_name>Porto Alegre</short_name>
   <type>locality</type>
   <type>political</type>
  </address_component>
  <address_component>
   <long_name>Porto Alegre</long_name>
   <short_name>Porto Alegre</short_name>
   <type>administrative_area_level_2</type>
   <type>political</type>
  </address_component>
  <address_component>
   <long_name>Rio Grande do Sul</long_name>
   <short_name>RS</short_name>
   <type>administrative_area_level_1</type>
   <type>political</type>
  </address_component>
  <address_component>
   <long_name>Brazil</long_name>
   <short_name>BR</short_name>
   <type>country</type>
   <type>political</type>
  </address_component>
  <address_component>
   <long_name>90220</long_name>
   <short_name>90220</short_name>
   <type>postal_code_prefix</type>
   <type>postal_code</type>
  </address_component>
  <geometry>
   <location>
    <lat>-29.9959215</lat>
    <lng>-51.1897052</lng>
   </location>
   <location_type>RANGE_INTERPOLATED</location_type>
   <viewport>
    <southwest>
     <lat>-29.9972751</lat>
     <lng>-51.1910467</lng>
    </southwest>
    <northeast>
     <lat>-29.9945772</lat>
     <lng>-51.1883487</lng>
    </northeast>
   </viewport>
   <bounds>
    <southwest>
     <lat>-29.9959308</lat>
     <lng>-51.1897052</lng>
    </southwest>
    <northeast>
     <lat>-29.9959215</lat>
     <lng>-51.1896902</lng>
    </northeast>
   </bounds>
  </geometry>
 </result>
 <result>
  <type>street_address</type>
  <formatted_address>Avenida Farrapos, 6, Rio Grande do Sul, 98480-000, Brazil</formatted_address>
  <address_component>
   <long_name>6</long_name>
   <short_name>6</short_name>
   <type>street_number</type>
  </address_component>
  <address_component>
   <long_name>Avenida Farrapos</long_name>
   <short_name>Av. Farrapos</short_name>
   <type>route</type>
  </address_component>
  <address_component>
   <long_name>Alpestre</long_name>
   <short_name>Alpestre</short_name>
   <type>administrative_area_level_2</type>
   <type>political</type>
  </address_component>
  <address_component>
   <long_name>Rio Grande do Sul</long_name>
   <short_name>RS</short_name>
   <type>administrative_area_level_1</type>
   <type>political</type>
  </address_component>
  <address_component>
   <long_name>Brazil</long_name>
   <short_name>BR</short_name>
   <type>country</type>
   <type>political</type>
  </address_component>
  <address_component>
   <long_name>98480-000</long_name>
   <short_name>98480-000</short_name>
   <type>postal_code</type>
  </address_component>
  <geometry>
   <location>
    <lat>-27.2458980</lat>
    <lng>-53.0381784</lng>
   </location>
   <location_type>ROOFTOP</location_type>
   <viewport>
    <southwest>
     <lat>-27.2472470</lat>
     <lng>-53.0395274</lng>
    </southwest>
    <northeast>
     <lat>-27.2445490</lat>
     <lng>-53.0368294</lng>
    </northeast>
   </viewport>
  </geometry>
 </result>
 <result>
  <type>street_address</type>
  <formatted_address>Avenida Farrapos, 6, Parana, 85955-000, Brazil</formatted_address>
  <address_component>
   <long_name>6</long_name>
   <short_name>6</short_name>
   <type>street_number</type>
  </address_component>
  <address_component>
   <long_name>Avenida Farrapos</long_name>
   <short_name>Av. Farrapos</short_name>
   <type>route</type>
  </address_component>
  <address_component>
   <long_name>Maripá</long_name>
   <short_name>Maripá</short_name>
   <type>administrative_area_level_2</type>
   <type>political</type>
  </address_component>
  <address_component>
   <long_name>Parana</long_name>
   <short_name>PR</short_name>
   <type>administrative_area_level_1</type>
   <type>political</type>
  </address_component>
  <address_component>
   <long_name>Brazil</long_name>
   <short_name>BR</short_name>
   <type>country</type>
   <type>political</type>
  </address_component>
  <address_component>
   <long_name>85955-000</long_name>
   <short_name>85955-000</short_name>
   <type>postal_code</type>
  </address_component>
  <geometry>
   <location>
    <lat>-24.4194115</lat>
    <lng>-53.8241465</lng>
   </location>
   <location_type>RANGE_INTERPOLATED</location_type>
   <viewport>
    <southwest>
     <lat>-24.4207681</lat>
     <lng>-53.8254992</lng>
    </southwest>
    <northeast>
     <lat>-24.4180702</lat>
     <lng>-53.8228012</lng>
    </northeast>
   </viewport>
   <bounds>
    <southwest>
     <lat>-24.4194268</lat>
     <lng>-53.8241539</lng>
    </southwest>
    <northeast>
     <lat>-24.4194115</lat>
     <lng>-53.8241465</lng>
    </northeast>
   </bounds>
  </geometry>
 </result>
 <result>
  <type>street_address</type>
  <formatted_address>Avenida Farrapos, 6 - Bairro Passo da Areia, Rio Grande do Sul, 95860-000, Brazil</formatted_address>
  <address_component>
   <long_name>6</long_name>
   <short_name>6</short_name>
   <type>street_number</type>
  </address_component>
  <address_component>
   <long_name>Avenida Farrapos</long_name>
   <short_name>Av. Farrapos</short_name>
   <type>route</type>
  </address_component>
  <address_component>
   <long_name>Bairro Passo da Areia</long_name>
   <short_name>Bairro Passo da Areia</short_name>
   <type>neighborhood</type>
   <type>political</type>
  </address_component>
  <address_component>
   <long_name>Taquari</long_name>
   <short_name>Taquari</short_name>
   <type>administrative_area_level_2</type>
   <type>political</type>
  </address_component>
  <address_component>
   <long_name>Rio Grande do Sul</long_name>
   <short_name>RS</short_name>
   <type>administrative_area_level_1</type>
   <type>political</type>
  </address_component>
  <address_component>
   <long_name>Brazil</long_name>
   <short_name>BR</short_name>
   <type>country</type>
   <type>political</type>
  </address_component>
  <address_component>
   <long_name>95860-000</long_name>
   <short_name>95860-000</short_name>
   <type>postal_code</type>
  </address_component>
  <geometry>
   <location>
    <lat>-29.8072706</lat>
    <lng>-51.8460101</lng>
   </location>
   <location_type>RANGE_INTERPOLATED</location_type>
   <viewport>
    <southwest>
     <lat>-29.8086182</lat>
     <lng>-51.8473682</lng>
    </southwest>
    <northeast>
     <lat>-29.8059203</lat>
     <lng>-51.8446702</lng>
    </northeast>
   </viewport>
   <bounds>
    <southwest>
     <lat>-29.8072706</lat>
     <lng>-51.8460283</lng>
    </southwest>
    <northeast>
     <lat>-29.8072679</lat>
     <lng>-51.8460101</lng>
    </northeast>
   </bounds>
  </geometry>
 </result>
 <result>
  <type>street_address</type>
  <formatted_address>Avenida Farrapos, 6 - Missões, Rio Grande do Sul, 99300-000, Brazil</formatted_address>
  <address_component>
   <long_name>6</long_name>
   <short_name>6</short_name>
   <type>street_number</type>
  </address_component>
  <address_component>
   <long_name>Avenida Farrapos</long_name>
   <short_name>Av. Farrapos</short_name>
   <type>route</type>
  </address_component>
  <address_component>
   <long_name>Missões</long_name>
   <short_name>Missões</short_name>
   <type>neighborhood</type>
   <type>political</type>
  </address_component>
  <address_component>
   <long_name>Soledade</long_name>
   <short_name>Soledade</short_name>
   <type>administrative_area_level_2</type>
   <type>political</type>
  </address_component>
  <address_component>
   <long_name>Rio Grande do Sul</long_name>
   <short_name>RS</short_name>
   <type>administrative_area_level_1</type>
   <type>political</type>
  </address_component>
  <address_component>
   <long_name>Brazil</long_name>
   <short_name>BR</short_name>
   <type>country</type>
   <type>political</type>
  </address_component>
  <address_component>
   <long_name>99300-000</long_name>
   <short_name>99300-000</short_name>
   <type>postal_code</type>
  </address_component>
  <geometry>
   <location>
    <lat>-28.8258713</lat>
    <lng>-52.5088439</lng>
   </location>
   <location_type>RANGE_INTERPOLATED</location_type>
   <viewport>
    <southwest>
     <lat>-28.8272145</lat>
     <lng>-52.5101993</lng>
    </southwest>
    <northeast>
     <lat>-28.8245165</lat>
     <lng>-52.5075013</lng>
    </northeast>
   </viewport>
   <bounds>
    <southwest>
     <lat>-28.8258713</lat>
     <lng>-52.5088567</lng>
    </southwest>
    <northeast>
     <lat>-28.8258597</lat>
     <lng>-52.5088439</lng>
    </northeast>
   </bounds>
  </geometry>
 </result>
 <result>
  <type>street_address</type>
  <formatted_address>Avenida Farrapos, 6, Rio Grande do Sul, 95585-000, Brazil</formatted_address>
  <address_component>
   <long_name>6</long_name>
   <short_name>6</short_name>
   <type>street_number</type>
  </address_component>
  <address_component>
   <long_name>Avenida Farrapos</long_name>
   <short_name>Av. Farrapos</short_name>
   <type>route</type>
  </address_component>
  <address_component>
   <long_name>Arroio do Sal</long_name>
   <short_name>Arroio do Sal</short_name>
   <type>administrative_area_level_2</type>
   <type>political</type>
  </address_component>
  <address_component>
   <long_name>Rio Grande do Sul</long_name>
   <short_name>RS</short_name>
   <type>administrative_area_level_1</type>
   <type>political</type>
  </address_component>
  <address_component>
   <long_name>Brazil</long_name>
   <short_name>BR</short_name>
   <type>country</type>
   <type>political</type>
  </address_component>
  <address_component>
   <long_name>95585-000</long_name>
   <short_name>95585-000</short_name>
   <type>postal_code</type>
  </address_component>
  <geometry>
   <location>
    <lat>-29.5525064</lat>
    <lng>-49.8883629</lng>
   </location>
   <location_type>RANGE_INTERPOLATED</location_type>
   <viewport>
    <southwest>
     <lat>-29.5538620</lat>
     <lng>-49.8897171</lng>
    </southwest>
    <northeast>
     <lat>-29.5511640</lat>
     <lng>-49.8870192</lng>
    </northeast>
   </viewport>
   <bounds>
    <southwest>
     <lat>-29.5525196</lat>
     <lng>-49.8883734</lng>
    </southwest>
    <northeast>
     <lat>-29.5525064</lat>
     <lng>-49.8883629</lng>
    </northeast>
   </bounds>
  </geometry>
 </result>
 <result>
  <type>street_address</type>
  <formatted_address>Avenida Farrapos, 6 - Triângulo, Erechim - Rio Grande do Sul, 99700-000, Brazil</formatted_address>
  <address_component>
   <long_name>6</long_name>
   <short_name>6</short_name>
   <type>street_number</type>
  </address_component>
  <address_component>
   <long_name>Avenida Farrapos</long_name>
   <short_name>Av. Farrapos</short_name>
   <type>route</type>
  </address_component>
  <address_component>
   <long_name>Triângulo</long_name>
   <short_name>Triângulo</short_name>
   <type>neighborhood</type>
   <type>political</type>
  </address_component>
  <address_component>
   <long_name>Erechim</long_name>
   <short_name>Erechim</short_name>
   <type>locality</type>
   <type>political</type>
  </address_component>
  <address_component>
   <long_name>Erechim</long_name>
   <short_name>Erechim</short_name>
   <type>administrative_area_level_2</type>
   <type>political</type>
  </address_component>
  <address_component>
   <long_name>Rio Grande do Sul</long_name>
   <short_name>RS</short_name>
   <type>administrative_area_level_1</type>
   <type>political</type>
  </address_component>
  <address_component>
   <long_name>Brazil</long_name>
   <short_name>BR</short_name>
   <type>country</type>
   <type>political</type>
  </address_component>
  <address_component>
   <long_name>99700-000</long_name>
   <short_name>99700-000</short_name>
   <type>postal_code</type>
  </address_component>
  <geometry>
   <location>
    <lat>-27.6329396</lat>
    <lng>-52.2798069</lng>
   </location>
   <location_type>RANGE_INTERPOLATED</location_type>
   <viewport>
    <southwest>
     <lat>-27.6342857</lat>
     <lng>-52.2811473</lng>
    </southwest>
    <northeast>
     <lat>-27.6315877</lat>
     <lng>-52.2784493</lng>
    </northeast>
   </viewport>
   <bounds>
    <southwest>
     <lat>-27.6329396</lat>
     <lng>-52.2798069</lng>
    </southwest>
    <northeast>
     <lat>-27.6329338</lat>
     <lng>-52.2797897</lng>
    </northeast>
   </bounds>
  </geometry>
 </result>
 <result>
  <type>street_address</type>
  <formatted_address>Avenida Farrapos, 6, Parana, 85887-000, Brazil</formatted_address>
  <address_component>
   <long_name>6</long_name>
   <short_name>6</short_name>
   <type>street_number</type>
  </address_component>
  <address_component>
   <long_name>Avenida Farrapos</long_name>
   <short_name>Av. Farrapos</short_name>
   <type>route</type>
  </address_component>
  <address_component>
   <long_name>Matelândia</long_name>
   <short_name>Matelândia</short_name>
   <type>administrative_area_level_2</type>
   <type>political</type>
  </address_component>
  <address_component>
   <long_name>Parana</long_name>
   <short_name>PR</short_name>
   <type>administrative_area_level_1</type>
   <type>political</type>
  </address_component>
  <address_component>
   <long_name>Brazil</long_name>
   <short_name>BR</short_name>
   <type>country</type>
   <type>political</type>
  </address_component>
  <address_component>
   <long_name>85887-000</long_name>
   <short_name>85887-000</short_name>
   <type>postal_code</type>
  </address_component>
  <geometry>
   <location>
    <lat>-25.2455589</lat>
    <lng>-53.9825355</lng>
   </location>
   <location_type>RANGE_INTERPOLATED</location_type>
   <viewport>
    <southwest>
     <lat>-25.2468995</lat>
     <lng>-53.9838837</lng>
    </southwest>
    <northeast>
     <lat>-25.2442016</lat>
     <lng>-53.9811858</lng>
    </northeast>
   </viewport>
   <bounds>
    <southwest>
     <lat>-25.2455589</lat>
     <lng>-53.9825355</lng>
    </southwest>
    <northeast>
     <lat>-25.2455422</lat>
     <lng>-53.9825340</lng>
    </northeast>
   </bounds>
  </geometry>
 </result>
 <result>
  <type>street_address</type>
  <formatted_address>Avenida Farrapos, 6, Rio Grande do Sul, 98918-000, Brazil</formatted_address>
  <address_component>
   <long_name>6</long_name>
   <short_name>6</short_name>
   <type>street_number</type>
  </address_component>
  <address_component>
   <long_name>Avenida Farrapos</long_name>
   <short_name>Av. Farrapos</short_name>
   <type>route</type>
  </address_component>
  <address_component>
   <long_name>Boa Vista do Buricá</long_name>
   <short_name>Boa Vista do Buricá</short_name>
   <type>administrative_area_level_2</type>
   <type>political</type>
  </address_component>
  <address_component>
   <long_name>Rio Grande do Sul</long_name>
   <short_name>RS</short_name>
   <type>administrative_area_level_1</type>
   <type>political</type>
  </address_component>
  <address_component>
   <long_name>Brazil</long_name>
   <short_name>BR</short_name>
   <type>country</type>
   <type>political</type>
  </address_component>
  <address_component>
   <long_name>98918-000</long_name>
   <short_name>98918-000</short_name>
   <type>postal_code</type>
  </address_component>
  <geometry>
   <location>
    <lat>-27.6680396</lat>
    <lng>-54.1131358</lng>
   </location>
   <location_type>RANGE_INTERPOLATED</location_type>
   <viewport>
    <southwest>
     <lat>-27.6693862</lat>
     <lng>-54.1144760</lng>
    </southwest>
    <northeast>
     <lat>-27.6666882</lat>
     <lng>-54.1117780</lng>
    </northeast>
   </viewport>
   <bounds>
    <southwest>
     <lat>-27.6680396</lat>
     <lng>-54.1131358</lng>
    </southwest>
    <northeast>
     <lat>-27.6680348</lat>
     <lng>-54.1131182</lng>
    </northeast>
   </bounds>
  </geometry>
 </result>
</GeocodeResponse>
Avatar do usuário
dr_gori
Moderador
Moderador
Mensagens: 5024
Registrado em: Seg, 03 Mai 2004 3:08 pm
Localização: Portland, OR USA
Contato:
Thomas F. G

Você já respondeu a dúvida de alguém hoje?
https://glufke.net/oracle/search.php?search_id=unanswered

Bem, o XML pode ter vários registros, com diversos níveis.
Você precisa de uma informação específica ?

Coloca aqui pra gente a query que você está usando.
mauricio_mendes
Rank: Estagiário Pleno
Rank: Estagiário Pleno
Mensagens: 6
Registrado em: Ter, 13 Jan 2009 9:05 am
Localização: Porto Alegre

Boa Tarde,


Eu preciso de todas as informações do xml, cidade, estado, rua, numero, ceep, latitude e longitude.

A Aplicação consiste em digitar endereço de origem e destino e com a latitude e longitude eu calculo a distância, porém o usuário pode digitar somente a rua e o numero, então tenho que carregar todas as informações do xml em uma lista, até aqui ta tranquilo, meu problema é retirar todas esses informações.

As informações de latitude e longitude fica em outro "node"

Selecionar tudo

begin
      oResultado := ip_xml.getNodeValue(oXmlDoc, 'GeocodeResponse');
    exception
      when ip_xml.errNodoNaoEncontrado then
        null;
    end;
  
    for i in 1 .. oResultado.count loop
    
    oResultado2 := ip_xml.getNodeValue(oXmlDoc,'address_component');
           for j in 1..oResultado2.count loop
    
     
  
            oResultado2(j) := '<root>' || oResultado2(j) || '</root>';
    
     

      oXmlDoc := ip_xml.parser(oResultado2(j), false);
     
      --PEGO A STRING DO NÓ TYPE
      v_valida := ip_xml.getNodeFirstValue(oXmlDoc,'type');
               
          if v_valida = 'street_number' then
           
           vNumero := ip_xml.getNodeFirstValue(oXmlDoc,'long_name');
           
       elsif v_valida = 'route' then
               
           
           v_logadouro := REPLACE(CONVERT(ip_xml.getNodeFirstValue(oXmlDoc,'long_name'),
                                     'AL16UTF16',
                                     'UTF8'),
                             CHR(0));

       
       elsif  v_valida = 'locality' or v_valida= 'administrative_area_level_2' then
       
          v_cidade :=  REPLACE(CONVERT(ip_xml.getNodeFirstValue(oXmlDoc,'long_name'),
                                     'AL16UTF16',
                                     'UTF8'),
                             CHR(0));
       
       elsif  v_valida = 'administrative_area_level_1' then
       
             vEstado :=  REPLACE(CONVERT(ip_xml.getNodeFirstValue(oXmlDoc,'short_name'),
                                     'AL16UTF16',
                                     'UTF8'),
                             CHR(0));
                        
        
       elsif  v_valida = 'postal_code' then      
       
            v_ceep :=  to_char(REPLACE(CONVERT(ip_xml.getNodeFirstValue(oXmlDoc,'long_name'),
                                     'AL16UTF16',
                                     'UTF8'),
                             CHR(0)));
                             
                                
      
    end if;
E Não consigo vincular a latitude correta para a cidade correta
mauricio_mendes
Rank: Estagiário Pleno
Rank: Estagiário Pleno
Mensagens: 6
Registrado em: Ter, 13 Jan 2009 9:05 am
Localização: Porto Alegre

Alguém para dar uma Luz?


Obrigado
Avatar do usuário
dr_gori
Moderador
Moderador
Mensagens: 5024
Registrado em: Seg, 03 Mai 2004 3:08 pm
Localização: Portland, OR USA
Contato:
Thomas F. G

Você já respondeu a dúvida de alguém hoje?
https://glufke.net/oracle/search.php?search_id=unanswered

Eu coloquei o seu XML dentro de um campo CLOB nesta tabela:

Selecionar tudo

SQL> desc XXXM_HEDGE_XML_TEMP
Name      Type         Nullable Default Comments 
--------- ------------ -------- ------- -------- 
REQUESTID VARCHAR2(20) Y                         
XMLDATA   CLOB         Y   
Depois eu fiz essa query:

Selecionar tudo

SQL> SELECT
  2    --value(des)
  3    extractValue(value(des), 'address_component/long_name')
  4  , extractValue(value(des), 'address_component/short_name')
  5    FROM XXXM_HEDGE_XML_TEMP p,
  6         table(XMLSequence(extract(xmltype(p.xmldata), '/GeocodeResponse/result/address_component'))) des
  7    where requestid        = -1;
 
EXTRACTVALUE(VALUE(DES),'ADDRE                                                   EXTRACTVALUE(VALUE(DES),'ADDRE
-------------------------------------------------------------------------------- --------------------------------------------------------------------------------
6                                                                                6
Avenida Farrapos                                                                 Av. Farrapos
Floresta                                                                         Floresta
Porto Alegre                                                                     Porto Alegre
Porto Alegre                                                                     Porto Alegre
Rio Grande do Sul                                                                RS
Brazil                                                                           BR
90220                                                                            90220
6                                                                                6
Avenida Farrapos                                                                 Av. Farrapos
Alpestre                                                                         Alpestre
Rio Grande do Sul                                                                RS
Brazil                                                                           BR
98480-000                                                                        98480-000
6                                                                                6
Avenida Farrapos                                                                 Av. Farrapos
Maripá                                                                          Maripá
Parana                                                                           PR
Brazil                                                                           BR
85955-000                                                                        85955-000
 
EXTRACTVALUE(VALUE(DES),'ADDRE                                                   EXTRACTVALUE(VALUE(DES),'ADDRE
-------------------------------------------------------------------------------- --------------------------------------------------------------------------------
6                                                                                6
Avenida Farrapos                                                                 Av. Farrapos
Bairro Passo da Areia                                                            Bairro Passo da Areia
Taquari                                                                          Taquari
Rio Grande do Sul                                                                RS
Brazil                                                                           BR
95860-000                                                                        95860-000
6                                                                                6
Avenida Farrapos                                                                 Av. Farrapos
Missões                                                                         Missões
Soledade                                                                         Soledade
Rio Grande do Sul                                                                RS
Brazil                                                                           BR
99300-000                                                                        99300-000
6                                                                                6
Avenida Farrapos                                                                 Av. Farrapos
Arroio do Sal                                                                    Arroio do Sal
Rio Grande do Sul                                                                RS
Brazil                                                                           BR
95585-000                                                                        95585-000
6                                                                                6
 
EXTRACTVALUE(VALUE(DES),'ADDRE                                                   EXTRACTVALUE(VALUE(DES),'ADDRE
-------------------------------------------------------------------------------- --------------------------------------------------------------------------------
Avenida Farrapos                                                                 Av. Farrapos
Triângulo                                                                       Triângulo
Erechim                                                                          Erechim
Erechim                                                                          Erechim
Rio Grande do Sul                                                                RS
Brazil                                                                           BR
99700-000                                                                        99700-000
6                                                                                6
Avenida Farrapos                                                                 Av. Farrapos
Matelândia                                                                      Matelândia
Parana                                                                           PR
Brazil                                                                           BR
85887-000                                                                        85887-000
6                                                                                6
Avenida Farrapos                                                                 Av. Farrapos
Boa Vista do Buricá                                                             Boa Vista do Buricá
Rio Grande do Sul                                                                RS
Brazil                                                                           BR
98918-000                                                                        98918-000
 
60 rows selected
 
SQL> 
Eu tentei colocar o TYPE no lado, mas veja que o seu XML possui 2 vezes a mesma tag TYPE, daí da erro:

Selecionar tudo

  <address_component>
   <long_name>Matelândia</long_name>
   <short_name>Matelândia</short_name>
   <type>administrative_area_level_2</type>
   <type>political</type>
  </address_component>
Outra query:

Selecionar tudo

SQL> SELECT
  2    --value(des)
  3    extractValue(value(des), 'geometry/location/lat') lat
  4  , extractValue(value(des), 'geometry/location/lng') lng
  5  , extractValue(value(des), 'geometry/location_type') tipo
  6  , extractValue(value(des), 'geometry/viewport/southwest/lat') vlat
  7  , extractValue(value(des), 'geometry/viewport/southwest/lng') vlng
  8    FROM XXXM_HEDGE_XML_TEMP p,
  9         table(XMLSequence(extract(xmltype(p.xmldata), '/GeocodeResponse/result/geometry'))) des
 10    where requestid        = -1;
 
LAT                                                                              LNG                                                                              TIPO                                                                             VLAT                                                                             VLNG
-------------------------------------------------------------------------------- -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- --------------------------------------------------------------------------------
-29.9959215                                                                      -51.1897052                                                                      RANGE_INTERPOLATED                                                               -29.9972751                                                                      -51.1910467
-27.2458980                                                                      -53.0381784                                                                      ROOFTOP                                                                          -27.2472470                                                                      -53.0395274
-24.4194115                                                                      -53.8241465                                                                      RANGE_INTERPOLATED                                                               -24.4207681                                                                      -53.8254992
-29.8072706                                                                      -51.8460101                                                                      RANGE_INTERPOLATED                                                               -29.8086182                                                                      -51.8473682
-28.8258713                                                                      -52.5088439                                                                      RANGE_INTERPOLATED                                                               -28.8272145                                                                      -52.5101993
-29.5525064                                                                      -49.8883629                                                                      RANGE_INTERPOLATED                                                               -29.5538620                                                                      -49.8897171
-27.6329396                                                                      -52.2798069                                                                      RANGE_INTERPOLATED                                                               -27.6342857                                                                      -52.2811473
-25.2455589                                                                      -53.9825355                                                                      RANGE_INTERPOLATED                                                               -25.2468995                                                                      -53.9838837
-27.6680396                                                                      -54.1131358                                                                      RANGE_INTERPOLATED                                                               -27.6693862                                                                      -54.1144760
 
9 rows selected
 
SQL> 
mauricio_mendes
Rank: Estagiário Pleno
Rank: Estagiário Pleno
Mensagens: 6
Registrado em: Ter, 13 Jan 2009 9:05 am
Localização: Porto Alegre

Obrigado, o problema é justamente esse type.
Mas fiz uma gambiarra, aqui e deu certo, mas a performance, ficou uma m... Por enquanto vai assim. Até conseguir melhorar o código. Querem tudo para ontem, saco.


Obrigado
Responder
  • Informação
  • Quem está online

    Usuários navegando neste fórum: Nenhum usuário registrado e 11 visitantes