Search code examples
xmlapixmlhttprequestpowerqueryfedex

Using Fedex API with Power Query to post an XML Track Request

I've spent the larger portion of a week trying to figure this thing out and still haven't done it. I'm trying to use the Track API of Fedex within MS Excel Power Query using the XML method. I've gone through all the process of getting TEST credentials and Productions credentials as well.

Thanks to @DiegoColantoni amazing feedback to other users I've managed to come up with the following code:

     <?xml version="1.0" encoding="UTF-8"?>
     <TrackRequest xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns="http://fedex.com/ws/track/v19">
           <WebAuthenticationDetail>
                <UserCredential>
                    <Key>MYKEY</Key>
                    <Password>MYPWD</Password>
                </UserCredential>
            </WebAuthenticationDetail>
            <ClientDetail>
                <AccountNumber>MYACCOUNT</AccountNumber>
                <MeterNumber>MYMETER</MeterNumber>
            </ClientDetail>
            <TransactionDetail>
                <CustomerTransactionId>TestTest</CustomerTransactionId>
            </TransactionDetail>
            <Version>
                <ServiceId>trck</ServiceId>
                <Major>19</Major>
                <Intermediate>0</Intermediate>
                <Minor>0</Minor>
            </Version>
            <SelectionDetails>
                <PackageIdentifier>
                    <Type>TRACKING_NUMBER_OR_DOORTAG</Type>
                    <Value>785459309647</Value>
                </PackageIdentifier>
            </SelectionDetails>
        </TrackRequest>

I have tried this code with Postman and have had successful responses, however when I try it within Power Query it doesn't work. I get this message in both the test and production environments

>DataSource.Error: Web.Contents failed to get contents from 'https://ws.fedex.com/xml' (500): Internal Server Error
Details:
    DataSourceKind=Web
    DataSourcePath=https://ws.fedex.com/xml
    Url=https://ws.fedex.com/xml
 code

Since it has worked with Postman I think it's something to do with the request itself but I don't really understand what is wrong.

Here's the full Excel Power Query

let
   url = "https://ws.fedex.com:443/xml",
   Body = Text.ToBinary("
        <?xml version=""1.0"" encoding=""UTF-8""?>
        <TrackRequest xmlns:xsi=""http://www.w3.org/2001/XMLSchema-instance"" xmlns:xsd=""http://www.w3.org/2001/XMLSchema"" xmlns=""http://fedex.com/ws/track/v19"">
            <WebAuthenticationDetail>
                <UserCredential>
                    <Key>MYKEY</Key>
                    <Password>MYPWD</Password>
                </UserCredential>
            </WebAuthenticationDetail>
            <ClientDetail>
                <AccountNumber>MYACCT</AccountNumber>
                <MeterNumber>MYMETER</MeterNumber>
            </ClientDetail>
            <TransactionDetail>
                <CustomerTransactionId>PruebaPrueba</CustomerTransactionId>
            </TransactionDetail>
            <Version>
                <ServiceId>trck</ServiceId>
                <Major>19</Major>
                <Intermediate>0</Intermediate>
                <Minor>0</Minor>
            </Version>
            <SelectionDetails>
                <PackageIdentifier>
                    <Type>TRACKING_NUMBER_OR_DOORTAG</Type>
                    <Value>785459309647</Value>
                </PackageIdentifier>
            </SelectionDetails>
        </TrackRequest>
    "),
    Source = Web.Contents(url, [Headers=[Accept="image/gif, image/jpeg, image/pjpeg, text/plain, text/html, */*", #"Content-Type"="text/xml"], Content = Body])
in
    Source
Solution

  • FedEx XML Plain Web Services are quite specific as far as the body of the request is concerned: empty lines at the beginning of the xml might result in a 500 response.

    This is what's happening with your Excel Power Query, see the new lines before and after the actual xml. Removing them should do the trick. I.e. this should work:

    let
   url = "https://ws.fedex.com:443/xml",
   Body = Text.ToBinary("<?xml version=""1.0"" encoding=""UTF-8""?>
        <TrackRequest xmlns:xsi=""http://www.w3.org/2001/XMLSchema-instance"" xmlns:xsd=""http://www.w3.org/2001/XMLSchema"" xmlns=""http://fedex.com/ws/track/v19"">
            <WebAuthenticationDetail>
                <UserCredential>
                    <Key>MYKEY</Key>
                    <Password>MYPWD</Password>
                </UserCredential>
            </WebAuthenticationDetail>
            <ClientDetail>
                <AccountNumber>MYACCT</AccountNumber>
                <MeterNumber>MYMETER</MeterNumber>
            </ClientDetail>
            <TransactionDetail>
                <CustomerTransactionId>PruebaPrueba</CustomerTransactionId>
            </TransactionDetail>
            <Version>
                <ServiceId>trck</ServiceId>
                <Major>19</Major>
                <Intermediate>0</Intermediate>
                <Minor>0</Minor>
            </Version>
            <SelectionDetails>
                <PackageIdentifier>
                    <Type>TRACKING_NUMBER_OR_DOORTAG</Type>
                    <Value>785459309647</Value>
                </PackageIdentifier>
            </SelectionDetails>
        </TrackRequest>"),
    Source = Web.Contents(url, [Headers=[Accept="image/gif, image/jpeg, image/pjpeg, text/plain, text/html, */*", #"Content-Type"="text/xml"], Content = Body])
in
    Source