QSYS2.HTTP_GET, USPS, XMLPARSE Solution

Final result from the "QSYS2.HTTP_GET problem with the USPS APIs" thread.

This does the HTTP_GET and then uses XMLPARSE to access the returmed XML.

with
retXML as (
values QSYS2.HTTP_GET(
'http://production.shippingapis.com/ShippingAPI.dll'
concat '?API=Verify&XML=' concat
url_encode(
'<AddressValidateRequest ' concat
'USERID="????????????">' concat
'<Revision>1</Revision>' concat
'<Address ID="0">' concat
'<Address1>Suite 2</Address1>' concat
'<Address2>8 Wildwood Drive</Address2>' concat
'<City>Old Lyme</City>' concat
'<State>CT</State>' concat
'<Zip5/>' concat
'<Zip4/>' concat
'</Address>' concat
'</AddressValidateRequest>'
)
)
),
xmltbl AS (
select x.* from xmltable
('AddressValidateResponse/Address'
passing xmlparse(document (select * from retXML))
columns
address1 char(30) path 'Address1',
address2 char(30) path 'Address2',
city Char(30) path 'City',
St char(2) path 'State',
zip5 char(5) path 'Zip5',
Zip4 char(4) path 'Zip4'
) as x
)
select * from xmltbl;


My thanks to:
Jack Woehr for his assistance with HTTP_GET.
Birgitta Hauser for posting an immensely helpful XMLPARSE example.

If you want to see this in an RPG program, I have an example on GITHUB.
https://github.com/SJLennon/IBM-i-RPG-Free-CLP-Code