Parsing an XML response given by Bing API in SQL server 2014

Question

I am using SQL server 2014 and i am calling the Bing distance matrix API to get the distance between 2 lat-long coordinates and I was able to receive an XML Output from the API. The issue is that I am not able to parse the XML to retrieve the value in the TravelDistance tag. How do i parse the response from the API?

I have tried using the Built-in OPENXML function. I got a NULL response.

I cast the response as XML and tried:

Set @XML=CAST(@response AS XML)

Set @[email protected]('(Response/ResourceSets/ResourceSet/Resources/Resource/Result/Distance/TravelDistance)[1]', 'varchar(20)')

resulting in a NULL response.

But selecting @XML will give me the full response

Sample XML Response is in this link: https://learn.microsoft.com/en-us/bingmaps/rest-services/examples/distance-matrix-example

Answer 1

The XML uses namespaces so you need to do that as well.

with xmlnamespaces(default 'http://schemas.microsoft.com/search/local/ws/rest/v1')
select @XML.value('(Response/ResourceSets/ResourceSet/Resources/Resource/Results/Distance/TravelDistance)[1]', 'varchar(20)');

And you had a typo in the XQuery expression.

declare @XML xml = '<?xml version="1.0" encoding="utf-8"?>
<Response xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns="http://schemas.microsoft.com/search/local/ws/rest/v1">
  <Copyright>Copyright © 2017 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>9eaa7048e9cc457c804c15bac083d8b7|BN20220145|7.7.0.0|</TraceId>
  <ResourceSets>
    <ResourceSet>
      <EstimatedTotal>1</EstimatedTotal>
      <Resources>
        <Resource xsi:type="DistanceMatrix">
          <Origins>
            <Coordinate>
              <Latitude>47.6044</Latitude>
              <Longitude>-122.3345</Longitude>
            </Coordinate>
            <Coordinate>
              <Latitude>47.6731</Latitude>
              <Longitude>-122.1185</Longitude>
            </Coordinate>
            <Coordinate>
              <Latitude>47.6149</Latitude>
              <Longitude>-122.1936</Longitude>
            </Coordinate>
          </Origins>
          <Destinations>
            <Coordinate>
              <Latitude>45.5347</Latitude>
              <Longitude>-122.6231</Longitude>
            </Coordinate>
            <Coordinate>
              <Latitude>47.4747</Latitude>
              <Longitude>-122.2057</Longitude>
            </Coordinate>
          </Destinations>
          <Results>
            <Distance>
              <DepartureTime xsi:nil="true" />
              <OriginIndex>0</OriginIndex>
              <DestinationIndex>0</DestinationIndex>
              <TravelDistance>281.261777777778</TravelDistance>
              <TravelDuration>9560.7</TravelDuration>
              <TotalWalkDuration>0</TotalWalkDuration>
            </Distance>
            <Distance>
              <DepartureTime xsi:nil="true" />
              <OriginIndex>0</OriginIndex>
              <DestinationIndex>1</DestinationIndex>
              <TravelDistance>23.284</TravelDistance>
              <TravelDuration>931.5</TravelDuration>
              <TotalWalkDuration>0</TotalWalkDuration>
            </Distance>
            <Distance>
              <DepartureTime xsi:nil="true" />
              <OriginIndex>1</OriginIndex>
              <DestinationIndex>0</DestinationIndex>
              <TravelDistance>296.074722222222</TravelDistance>
              <TravelDuration>10203.1</TravelDuration>
              <TotalWalkDuration>0</TotalWalkDuration>
            </Distance>
            <Distance>
              <DepartureTime xsi:nil="true" />
              <OriginIndex>1</OriginIndex>
              <DestinationIndex>1</DestinationIndex>
              <TravelDistance>28.4669444444444</TravelDistance>
              <TravelDuration>1155.6</TravelDuration>
              <TotalWalkDuration>0</TotalWalkDuration>
            </Distance>
            <Distance>
              <DepartureTime xsi:nil="true" />
              <OriginIndex>2</OriginIndex>
              <DestinationIndex>0</DestinationIndex>
              <TravelDistance>285.752194444444</TravelDistance>
              <TravelDuration>9818.4</TravelDuration>
              <TotalWalkDuration>0</TotalWalkDuration>
            </Distance>
            <Distance>
              <DepartureTime xsi:nil="true" />
              <OriginIndex>2</OriginIndex>
              <DestinationIndex>1</DestinationIndex>
              <TravelDistance>18.1444166666667</TravelDistance>
              <TravelDuration>770.9</TravelDuration>
              <TotalWalkDuration>0</TotalWalkDuration>
            </Distance>
          </Results>
        </Resource>
      </Resources>
    </ResourceSet>
  </ResourceSets>
</Response>';


with xmlnamespaces(default 'http://schemas.microsoft.com/search/local/ws/rest/v1')
select @XML.value('(Response/ResourceSets/ResourceSet/Resources/Resource/Results/Distance/TravelDistance)[1]', 'varchar(20)');