1 Reply Latest reply: May 24, 2013 8:33 AM by odie_63 RSS

    How to get element data longitude and latitude from the xml output.

    1007728
      Hi,

      I need to get the longitude and lattitude data from the following xml phrase at point level.

      <?xml version="1.0" encoding="utf-8" ?>
      - <Response xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns="http://schemas.microsoft.com/search/local/ws/rest/v1">
      <Copyright>Copyright © 2013 Microsoft and its suppliers. All rights reserved. This API cannot be accessed and the content and any results may not be used, reproduced or transmitted in any manner without express written permission from Microsoft Corporation.</Copyright>
      <BrandLogoUri>http://dev.virtualearth.net/Branding/logo_powered_by.png</BrandLogoUri>
      <StatusCode>200</StatusCode>
      <StatusDescription>OK</StatusDescription>
      <AuthenticationResultCode>ValidCredentials</AuthenticationResultCode>
      <TraceId>6b663bb2576443e48a3b11d8ebb74680|CH1M001463|02.00.139.700|CH1MSNVM001399, 77.77, CH1IPEVM000052</TraceId>
      - <ResourceSets>
      - <ResourceSet>
      <EstimatedTotal>1</EstimatedTotal>
      - <Resources>
      - <Location>
      <Name>Sardar Patel Marg, Civil Lines, Allahabad 211011, India</Name>
      - <Point>
      <Latitude>25.448946</Latitude>
      <Longitude>81.8343201</Longitude>
      </Point>
      - <BoundingBox>
      <SouthLatitude>25.4477005</SouthLatitude>
      <WestLongitude>81.8340988</WestLongitude>
      <NorthLatitude>25.4598007</NorthLatitude>
      <EastLongitude>81.8358002</EastLongitude>
      </BoundingBox>
      <EntityType>RoadBlock</EntityType>
      - <Address>
      <AddressLine>Sardar Patel Marg</AddressLine>
      <AdminDistrict>Uttar Pradesh</AdminDistrict>
      <AdminDistrict2>Allahabad</AdminDistrict2>
      <CountryRegion>India</CountryRegion>
      <FormattedAddress>Sardar Patel Marg, Civil Lines, Allahabad 211011, India</FormattedAddress>
      <Locality>Allahabad</Locality>
      <PostalCode>211011</PostalCode>
      </Address>
      <Confidence>Medium</Confidence>
      <MatchCode>UpHierarchy</MatchCode>
      - <GeocodePoint>
      <Latitude>25.448946</Latitude>
      <Longitude>81.8343201</Longitude>
      <CalculationMethod>InterpolationOffset</CalculationMethod>
      <UsageType>Display</UsageType>
      </GeocodePoint>
      - <GeocodePoint>
      <Latitude>25.448946</Latitude>
      <Longitude>81.8343201</Longitude>
      <CalculationMethod>Interpolation</CalculationMethod>
      <UsageType>Route</UsageType>
      </GeocodePoint>
      </Location>
      </Resources>
      </ResourceSet>
      </ResourceSets>
      </Response>


      I had been using

      DECLARE
      v_url1 varchar2(200) := 'http://dev.virtualearth.net/REST/v1/Locations/'; --address is to be appended in the middle of URL.So I have split URL in 2 parts
      v_url2 varchar2(200) := '?o=xml&&key=API_KEY'; ---api key
      v_address varchar2(200) := 'LOCATION_ADDRESS';----any location address details
      v_req utl_http.req;
      v_rsp utl_http.resp;
      v_xml varchar2(32767);
      v_lng number;
      v_lat number;
      BEGIN
      utl_http.set_proxy('CLIENT_PROXY', 'PROXY_URL');---Database client proxy details to be provided here
      v_req := utl_http.begin_request(v_url1 || utl_url.escape(v_address) || v_url2, 'GET', utl_http.HTTP_VERSION_1_1);
      v_rsp := utl_http.get_response(v_req);
      utl_http.read_text(v_rsp, v_xml);
      utl_http.end_response(v_rsp);
      select longitude, latitude
      into v_lng, v_lat
      from xmltable(
      '/Response xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns="http://schemas.microsoft.com/search/local/ws/rest/v1/ResourceSets/ResourceSet/Resources/Location/Point'
      passing xmlparse(document v_xml)
      columns longitude number path 'Longitude'
      , latitude number path 'Latitude'
      );
      dbms_output.put_line('Longitude = ' || v_lng);
      dbms_output.put_line('Latitude = ' || v_lat);
      END;

      i am getting xml phrase error.

      please guide me on this.

      Thanks,
      bhanu.
        • 1. Re: How to get element data longitude and latitude from the xml output.
          odie_63
          1004725 wrote:
          i am getting xml phrase error.
          How about giving that error message too?

          Anyway, this one is quite obvious, the namespace mapping is specified incorrectly.
          Use XMLNamespaces clause :
          select longitude, latitude
          into v_lng, v_lat
          from xmltable(
                 xmlnamespaces(default 'http://schemas.microsoft.com/search/local/ws/rest/v1')
               , '/Response/ResourceSets/ResourceSet/Resources/Location/Point'
                 passing xmlparse(document v_xml)
                 columns longitude number path 'Longitude'
                       , latitude  number path 'Latitude'
               ) ;