Xpath to output cells from the USPS API XML

Support
#1

I'm working with the USPS API to verify addresses that are in an Excel file. API responses look like this:

<?xml version="1.0" encoding="UTF-8"?>
<AddressValidateResponse>
    <Address ID="0">
        <Address2>3131 SPRING GROVE AVE</Address2>
        <City>CINCINNATI</City>
        <State>OH</State>
        <Zip5>45225</Zip5>
        <Zip4>1862</Zip4>
        <ReturnText>Default address: The address you entered was found but more information is needed (such as an apartment, suite, or box number) to match to a specific address.</ReturnText>
    </Address>
</AddressValidateResponse>

=XPathOnUrl(A1,"addressvalidateresponse") returns all the fields under ID=0 concatenated into one cell. I'd like to have this return an array that puts each node in its own cell, and I haven't been able to figure it out. Here's what I've tried:

=XPathOnUrl($A$1,"/addressvalidateresponse[@address='0']")

An added challenge is that the API only responds with nodes that are used. If you submit an address with STE 10040 MAIN ST, the server may respond with no Address1 and 40 MAIN ST STE 100. I'd like to have results in the same column every time, if that's possible. Any ideas?

#2

How about different requests for each field? That would solve the probllem of having the results in the same column. For example,

=XPathOnUrl($A$1,"//*/City"]")

1 Like
#3

That makes sense. The terms of use for the API stipulate that you only make one API call at a time. If I set up 5 xpathonurl formulas, they might not be simultaneous exactly, but it might be close enough to cause trouble.

Here's what I came up with for the xpath portion of my formula:
/addressvalidateresponse/address/zip5 | /addressvalidateresponse/address/zip4 | /addressvalidateresponse/address/address2 | /addressvalidateresponse/address/city | /addressvalidateresponse/address/state | /addressvalidateresponse/address/address1 | /addressvalidateresponse/address/error

Probably there's a more efficient way to write this, but since the field names are already in as column headers, I put "/addressvalidateresponse/address/" in an out-of-the-way cell and then concatenate it all together. Not beautiful, but DUMP and transpose it and it works.

Since I need the zip codes to be in this format, "55555-4444," I've concatenated that string together. The problem is that when I paste in a new address and the xpathonurl formula runs, the concatenation formula doesn't update with it. I have to hit F9 to force it to recalculate. Is there a way to save myself that keystroke?