SQL Server / ShipEngine Walkthrough / Validate when creating a shipment
Back to Collection Items
-- Important: See this note about string length limitations for strings returned by sp_OAMethod calls.
--
CREATE PROCEDURE ChilkatSample
AS
BEGIN
DECLARE @hr int
DECLARE @iTmp0 int
-- Important: Do not use nvarchar(max). See the warning about using nvarchar(max).
DECLARE @sTmp0 nvarchar(4000)
-- This example assumes the Chilkat API to have been previously unlocked.
-- See Global Unlock Sample for sample code.
DECLARE @http int
-- Use "Chilkat_9_5_0.Http" for versions of Chilkat < 10.0.0
EXEC @hr = sp_OACreate 'Chilkat.Http', @http OUT
IF @hr <> 0
BEGIN
PRINT 'Failed to create ActiveX component'
RETURN
END
DECLARE @success int
-- Use this online tool to generate code from sample JSON: Generate Code to Create JSON
-- The following JSON is sent in the request body.
-- {
-- "shipments": [
-- {
-- "validate_address": "validate_and_clean",
-- "service_code": "usps_priority_mail",
-- "ship_from": {
-- "name": "John Doe",
-- "company_name": "Example Corp.",
-- "address_line1": "4009 Marathon Blvd",
-- "city_locality": "Austin",
-- "state_province": "TX",
-- "postal_code": "78756",
-- "country_code": "US",
-- "phone": "512-555-5555"
-- },
-- "ship_to": {
-- "name": "Amanda Miller",
-- "address_line1": "525 Winchester",
-- "postal_code": "95128",
-- "country_code": "US"
-- },
-- "packages": [
-- {
-- "weight": {
-- "value": 17,
-- "unit": "pound"
-- },
-- "dimensions": {
-- "length": 36,
-- "width": 12,
-- "height": 24,
-- "unit": "inch"
-- }
-- }
-- ]
-- }
-- ]
-- }
DECLARE @json int
-- Use "Chilkat_9_5_0.JsonObject" for versions of Chilkat < 10.0.0
EXEC @hr = sp_OACreate 'Chilkat.JsonObject', @json OUT
EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'shipments[0].validate_address', 'validate_and_clean'
EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'shipments[0].service_code', 'usps_priority_mail'
EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'shipments[0].ship_from.name', 'John Doe'
EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'shipments[0].ship_from.company_name', 'Example Corp.'
EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'shipments[0].ship_from.address_line1', '4009 Marathon Blvd'
EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'shipments[0].ship_from.city_locality', 'Austin'
EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'shipments[0].ship_from.state_province', 'TX'
EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'shipments[0].ship_from.postal_code', '78756'
EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'shipments[0].ship_from.country_code', 'US'
EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'shipments[0].ship_from.phone', '512-555-5555'
EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'shipments[0].ship_to.name', 'Amanda Miller'
EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'shipments[0].ship_to.address_line1', '525 Winchester'
EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'shipments[0].ship_to.postal_code', '95128'
EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'shipments[0].ship_to.country_code', 'US'
EXEC sp_OAMethod @json, 'UpdateInt', @success OUT, 'shipments[0].packages[0].weight.value', 17
EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'shipments[0].packages[0].weight.unit', 'pound'
EXEC sp_OAMethod @json, 'UpdateInt', @success OUT, 'shipments[0].packages[0].dimensions.length', 36
EXEC sp_OAMethod @json, 'UpdateInt', @success OUT, 'shipments[0].packages[0].dimensions.width', 12
EXEC sp_OAMethod @json, 'UpdateInt', @success OUT, 'shipments[0].packages[0].dimensions.height', 24
EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'shipments[0].packages[0].dimensions.unit', 'inch'
EXEC sp_OAMethod @http, 'SetRequestHeader', NULL, 'API-Key', '{{API_KEY}}'
EXEC sp_OAMethod @http, 'SetRequestHeader', NULL, 'Content-Type', 'application/json'
DECLARE @resp int
EXEC sp_OAMethod @http, 'PostJson3', @resp OUT, 'https://api.shipengine.com/v1/shipments', 'application/json', @json
EXEC sp_OAGetProperty @http, 'LastMethodSuccess', @iTmp0 OUT
IF @iTmp0 = 0
BEGIN
EXEC sp_OAGetProperty @http, 'LastErrorText', @sTmp0 OUT
PRINT @sTmp0
EXEC @hr = sp_OADestroy @http
EXEC @hr = sp_OADestroy @json
RETURN
END
DECLARE @sbResponseBody int
-- Use "Chilkat_9_5_0.StringBuilder" for versions of Chilkat < 10.0.0
EXEC @hr = sp_OACreate 'Chilkat.StringBuilder', @sbResponseBody OUT
EXEC sp_OAMethod @resp, 'GetBodySb', @success OUT, @sbResponseBody
DECLARE @jResp int
-- Use "Chilkat_9_5_0.JsonObject" for versions of Chilkat < 10.0.0
EXEC @hr = sp_OACreate 'Chilkat.JsonObject', @jResp OUT
EXEC sp_OAMethod @jResp, 'LoadSb', @success OUT, @sbResponseBody
EXEC sp_OASetProperty @jResp, 'EmitCompact', 0
PRINT 'Response Body:'
EXEC sp_OAMethod @jResp, 'Emit', @sTmp0 OUT
PRINT @sTmp0
DECLARE @respStatusCode int
EXEC sp_OAGetProperty @resp, 'StatusCode', @respStatusCode OUT
PRINT 'Response Status Code = ' + @respStatusCode
IF @respStatusCode >= 400
BEGIN
PRINT 'Response Header:'
EXEC sp_OAGetProperty @resp, 'Header', @sTmp0 OUT
PRINT @sTmp0
PRINT 'Failed.'
EXEC @hr = sp_OADestroy @resp
EXEC @hr = sp_OADestroy @http
EXEC @hr = sp_OADestroy @json
EXEC @hr = sp_OADestroy @sbResponseBody
EXEC @hr = sp_OADestroy @jResp
RETURN
END
EXEC @hr = sp_OADestroy @resp
-- Sample JSON response:
-- (Sample code for parsing the JSON response is shown below)
-- {
-- "has_errors": false,
-- "shipments": [
-- {
-- "errors": [
-- ],
-- "address_validation": {
-- "status": "verified",
-- "original_address": {
-- "name": "Amanda Miller",
-- "phone": null,
-- "company_name": null,
-- "address_line1": "525 Winchester",
-- "address_line2": null,
-- "address_line3": null,
-- "city_locality": null,
-- "state_province": null,
-- "postal_code": "95128",
-- "country_code": "US",
-- "address_residential_indicator": "unknown"
-- },
-- "matched_address": {
-- "name": "AMANDA MILLER",
-- "phone": null,
-- "company_name": null,
-- "address_line1": "525 S WINCHESTER BLVD",
-- "address_line2": "",
-- "address_line3": null,
-- "city_locality": "SAN JOSE",
-- "state_province": "CA",
-- "postal_code": "95128-2537",
-- "country_code": "US",
-- "address_residential_indicator": "no"
-- },
-- "messages": [
-- ]
-- },
-- "shipment_id": "se-4159801",
-- "carrier_id": "se-121861",
-- "service_code": "usps_priority_mail",
-- "external_shipment_id": null,
-- "ship_date": "2019-09-18T00:00:00Z",
-- "created_at": "2019-09-18T16:01:54.9171198Z",
-- "modified_at": "2019-09-18T16:01:54.9171198Z",
-- "shipment_status": "pending",
-- "ship_to": {
-- "name": "AMANDA MILLER",
-- "phone": null,
-- "company_name": null,
-- "address_line1": "525 S WINCHESTER BLVD",
-- "address_line2": "",
-- "address_line3": null,
-- "city_locality": "SAN JOSE",
-- "state_province": "CA",
-- "postal_code": "95128-2537",
-- "country_code": "US",
-- "address_residential_indicator": "no"
-- },
-- "ship_from": {
-- "name": "John Doe",
-- "phone": "512-555-5555",
-- "company_name": "Example Corp.",
-- "address_line1": "4009 Marathon Blvd",
-- "address_line2": null,
-- "address_line3": null,
-- "city_locality": "Austin",
-- "state_province": "TX",
-- "postal_code": "78756",
-- "country_code": "US",
-- "address_residential_indicator": "unknown"
-- },
-- "warehouse_id": null,
-- "return_to": {
-- "name": "John Doe",
-- "phone": "512-555-5555",
-- "company_name": "Example Corp.",
-- "address_line1": "4009 Marathon Blvd",
-- "address_line2": null,
-- "address_line3": null,
-- "city_locality": "Austin",
-- "state_province": "TX",
-- "postal_code": "78756",
-- "country_code": "US",
-- "address_residential_indicator": "unknown"
-- },
-- "confirmation": "none",
-- "customs": null,
-- "external_order_id": null,
-- "order_source_code": null,
-- "advanced_options": {
-- "bill_to_account": null,
-- "bill_to_country_code": null,
-- "bill_to_party": null,
-- "bill_to_postal_code": null,
-- "contains_alcohol": false,
-- "delivered_duty_paid": false,
-- "non_machinable": false,
-- "saturday_delivery": false,
-- "dry_ice": false,
-- "dry_ice_weight": null,
-- "freight_class": null,
-- "custom_field1": null,
-- "custom_field2": null,
-- "custom_field3": null
-- },
-- "insurance_provider": "none",
-- "tags": [
-- ],
-- "packages": [
-- {
-- "package_code": "package",
-- "weight": {
-- "value": 17,
-- "unit": "pound"
-- },
-- "dimensions": {
-- "unit": "inch",
-- "length": 36,
-- "width": 12,
-- "height": 24
-- },
-- "insured_value": {
-- "currency": "usd",
-- "amount": 0
-- },
-- "label_messages": {
-- "reference1": null,
-- "reference2": null,
-- "reference3": null
-- },
-- "external_package_id": null
-- }
-- ],
-- "total_weight": {
-- "value": 17,
-- "unit": "pound"
-- },
-- "items": [
-- ]
-- }
-- ]
-- }
-- Sample code for parsing the JSON response...
-- Use this online tool to generate parsing code from sample JSON: Generate JSON Parsing Code
DECLARE @Status nvarchar(4000)
DECLARE @Name nvarchar(4000)
DECLARE @Phone nvarchar(4000)
DECLARE @Company_name nvarchar(4000)
DECLARE @Address_line1 nvarchar(4000)
DECLARE @Address_line2 nvarchar(4000)
DECLARE @Address_line3 nvarchar(4000)
DECLARE @City_locality nvarchar(4000)
DECLARE @State_province nvarchar(4000)
DECLARE @Postal_code nvarchar(4000)
DECLARE @Country_code nvarchar(4000)
DECLARE @Address_residential_indicator nvarchar(4000)
DECLARE @Matched_addressName nvarchar(4000)
DECLARE @Matched_addressPhone nvarchar(4000)
DECLARE @Matched_addressCompany_name nvarchar(4000)
DECLARE @Matched_addressAddress_line1 nvarchar(4000)
DECLARE @Matched_addressAddress_line2 nvarchar(4000)
DECLARE @Matched_addressAddress_line3 nvarchar(4000)
DECLARE @Matched_addressCity_locality nvarchar(4000)
DECLARE @Matched_addressState_province nvarchar(4000)
DECLARE @Matched_addressPostal_code nvarchar(4000)
DECLARE @Matched_addressCountry_code nvarchar(4000)
DECLARE @Matched_addressAddress_residential_indicator nvarchar(4000)
DECLARE @shipment_id nvarchar(4000)
DECLARE @carrier_id nvarchar(4000)
DECLARE @service_code nvarchar(4000)
DECLARE @external_shipment_id nvarchar(4000)
DECLARE @ship_date nvarchar(4000)
DECLARE @created_at nvarchar(4000)
DECLARE @modified_at nvarchar(4000)
DECLARE @shipment_status nvarchar(4000)
DECLARE @ship_toName nvarchar(4000)
DECLARE @ship_toPhone nvarchar(4000)
DECLARE @ship_toCompany_name nvarchar(4000)
DECLARE @ship_toAddress_line1 nvarchar(4000)
DECLARE @ship_toAddress_line2 nvarchar(4000)
DECLARE @ship_toAddress_line3 nvarchar(4000)
DECLARE @ship_toCity_locality nvarchar(4000)
DECLARE @ship_toState_province nvarchar(4000)
DECLARE @ship_toPostal_code nvarchar(4000)
DECLARE @ship_toCountry_code nvarchar(4000)
DECLARE @ship_toAddress_residential_indicator nvarchar(4000)
DECLARE @ship_fromName nvarchar(4000)
DECLARE @ship_fromPhone nvarchar(4000)
DECLARE @ship_fromCompany_name nvarchar(4000)
DECLARE @ship_fromAddress_line1 nvarchar(4000)
DECLARE @ship_fromAddress_line2 nvarchar(4000)
DECLARE @ship_fromAddress_line3 nvarchar(4000)
DECLARE @ship_fromCity_locality nvarchar(4000)
DECLARE @ship_fromState_province nvarchar(4000)
DECLARE @ship_fromPostal_code nvarchar(4000)
DECLARE @ship_fromCountry_code nvarchar(4000)
DECLARE @ship_fromAddress_residential_indicator nvarchar(4000)
DECLARE @warehouse_id nvarchar(4000)
DECLARE @return_toName nvarchar(4000)
DECLARE @return_toPhone nvarchar(4000)
DECLARE @return_toCompany_name nvarchar(4000)
DECLARE @return_toAddress_line1 nvarchar(4000)
DECLARE @return_toAddress_line2 nvarchar(4000)
DECLARE @return_toAddress_line3 nvarchar(4000)
DECLARE @return_toCity_locality nvarchar(4000)
DECLARE @return_toState_province nvarchar(4000)
DECLARE @return_toPostal_code nvarchar(4000)
DECLARE @return_toCountry_code nvarchar(4000)
DECLARE @return_toAddress_residential_indicator nvarchar(4000)
DECLARE @confirmation nvarchar(4000)
DECLARE @customs nvarchar(4000)
DECLARE @external_order_id nvarchar(4000)
DECLARE @order_source_code nvarchar(4000)
DECLARE @Bill_to_account nvarchar(4000)
DECLARE @Bill_to_country_code nvarchar(4000)
DECLARE @Bill_to_party nvarchar(4000)
DECLARE @Bill_to_postal_code nvarchar(4000)
DECLARE @Contains_alcohol int
DECLARE @Delivered_duty_paid int
DECLARE @Non_machinable int
DECLARE @Saturday_delivery int
DECLARE @Dry_ice int
DECLARE @Dry_ice_weight nvarchar(4000)
DECLARE @Freight_class nvarchar(4000)
DECLARE @Custom_field1 nvarchar(4000)
DECLARE @Custom_field2 nvarchar(4000)
DECLARE @Custom_field3 nvarchar(4000)
DECLARE @insurance_provider nvarchar(4000)
DECLARE @Value int
DECLARE @Unit nvarchar(4000)
DECLARE @j int
DECLARE @count_j int
DECLARE @package_code nvarchar(4000)
DECLARE @weightValue int
DECLARE @weightUnit nvarchar(4000)
DECLARE @dimensionsUnit nvarchar(4000)
DECLARE @Length int
DECLARE @Width int
DECLARE @Height int
DECLARE @v_Currency nvarchar(4000)
DECLARE @Amount int
DECLARE @Reference1 nvarchar(4000)
DECLARE @Reference2 nvarchar(4000)
DECLARE @Reference3 nvarchar(4000)
DECLARE @external_package_id nvarchar(4000)
DECLARE @has_errors int
EXEC sp_OAMethod @jResp, 'BoolOf', @has_errors OUT, 'has_errors'
DECLARE @i int
SELECT @i = 0
DECLARE @count_i int
EXEC sp_OAMethod @jResp, 'SizeOfArray', @count_i OUT, 'shipments'
WHILE @i < @count_i
BEGIN
EXEC sp_OASetProperty @jResp, 'I', @i
EXEC sp_OAMethod @jResp, 'StringOf', @Status OUT, 'shipments[i].address_validation.status'
EXEC sp_OAMethod @jResp, 'StringOf', @Name OUT, 'shipments[i].address_validation.original_address.name'
EXEC sp_OAMethod @jResp, 'StringOf', @Phone OUT, 'shipments[i].address_validation.original_address.phone'
EXEC sp_OAMethod @jResp, 'StringOf', @Company_name OUT, 'shipments[i].address_validation.original_address.company_name'
EXEC sp_OAMethod @jResp, 'StringOf', @Address_line1 OUT, 'shipments[i].address_validation.original_address.address_line1'
EXEC sp_OAMethod @jResp, 'StringOf', @Address_line2 OUT, 'shipments[i].address_validation.original_address.address_line2'
EXEC sp_OAMethod @jResp, 'StringOf', @Address_line3 OUT, 'shipments[i].address_validation.original_address.address_line3'
EXEC sp_OAMethod @jResp, 'StringOf', @City_locality OUT, 'shipments[i].address_validation.original_address.city_locality'
EXEC sp_OAMethod @jResp, 'StringOf', @State_province OUT, 'shipments[i].address_validation.original_address.state_province'
EXEC sp_OAMethod @jResp, 'StringOf', @Postal_code OUT, 'shipments[i].address_validation.original_address.postal_code'
EXEC sp_OAMethod @jResp, 'StringOf', @Country_code OUT, 'shipments[i].address_validation.original_address.country_code'
EXEC sp_OAMethod @jResp, 'StringOf', @Address_residential_indicator OUT, 'shipments[i].address_validation.original_address.address_residential_indicator'
EXEC sp_OAMethod @jResp, 'StringOf', @Matched_addressName OUT, 'shipments[i].address_validation.matched_address.name'
EXEC sp_OAMethod @jResp, 'StringOf', @Matched_addressPhone OUT, 'shipments[i].address_validation.matched_address.phone'
EXEC sp_OAMethod @jResp, 'StringOf', @Matched_addressCompany_name OUT, 'shipments[i].address_validation.matched_address.company_name'
EXEC sp_OAMethod @jResp, 'StringOf', @Matched_addressAddress_line1 OUT, 'shipments[i].address_validation.matched_address.address_line1'
EXEC sp_OAMethod @jResp, 'StringOf', @Matched_addressAddress_line2 OUT, 'shipments[i].address_validation.matched_address.address_line2'
EXEC sp_OAMethod @jResp, 'StringOf', @Matched_addressAddress_line3 OUT, 'shipments[i].address_validation.matched_address.address_line3'
EXEC sp_OAMethod @jResp, 'StringOf', @Matched_addressCity_locality OUT, 'shipments[i].address_validation.matched_address.city_locality'
EXEC sp_OAMethod @jResp, 'StringOf', @Matched_addressState_province OUT, 'shipments[i].address_validation.matched_address.state_province'
EXEC sp_OAMethod @jResp, 'StringOf', @Matched_addressPostal_code OUT, 'shipments[i].address_validation.matched_address.postal_code'
EXEC sp_OAMethod @jResp, 'StringOf', @Matched_addressCountry_code OUT, 'shipments[i].address_validation.matched_address.country_code'
EXEC sp_OAMethod @jResp, 'StringOf', @Matched_addressAddress_residential_indicator OUT, 'shipments[i].address_validation.matched_address.address_residential_indicator'
EXEC sp_OAMethod @jResp, 'StringOf', @shipment_id OUT, 'shipments[i].shipment_id'
EXEC sp_OAMethod @jResp, 'StringOf', @carrier_id OUT, 'shipments[i].carrier_id'
EXEC sp_OAMethod @jResp, 'StringOf', @service_code OUT, 'shipments[i].service_code'
EXEC sp_OAMethod @jResp, 'StringOf', @external_shipment_id OUT, 'shipments[i].external_shipment_id'
EXEC sp_OAMethod @jResp, 'StringOf', @ship_date OUT, 'shipments[i].ship_date'
EXEC sp_OAMethod @jResp, 'StringOf', @created_at OUT, 'shipments[i].created_at'
EXEC sp_OAMethod @jResp, 'StringOf', @modified_at OUT, 'shipments[i].modified_at'
EXEC sp_OAMethod @jResp, 'StringOf', @shipment_status OUT, 'shipments[i].shipment_status'
EXEC sp_OAMethod @jResp, 'StringOf', @ship_toName OUT, 'shipments[i].ship_to.name'
EXEC sp_OAMethod @jResp, 'StringOf', @ship_toPhone OUT, 'shipments[i].ship_to.phone'
EXEC sp_OAMethod @jResp, 'StringOf', @ship_toCompany_name OUT, 'shipments[i].ship_to.company_name'
EXEC sp_OAMethod @jResp, 'StringOf', @ship_toAddress_line1 OUT, 'shipments[i].ship_to.address_line1'
EXEC sp_OAMethod @jResp, 'StringOf', @ship_toAddress_line2 OUT, 'shipments[i].ship_to.address_line2'
EXEC sp_OAMethod @jResp, 'StringOf', @ship_toAddress_line3 OUT, 'shipments[i].ship_to.address_line3'
EXEC sp_OAMethod @jResp, 'StringOf', @ship_toCity_locality OUT, 'shipments[i].ship_to.city_locality'
EXEC sp_OAMethod @jResp, 'StringOf', @ship_toState_province OUT, 'shipments[i].ship_to.state_province'
EXEC sp_OAMethod @jResp, 'StringOf', @ship_toPostal_code OUT, 'shipments[i].ship_to.postal_code'
EXEC sp_OAMethod @jResp, 'StringOf', @ship_toCountry_code OUT, 'shipments[i].ship_to.country_code'
EXEC sp_OAMethod @jResp, 'StringOf', @ship_toAddress_residential_indicator OUT, 'shipments[i].ship_to.address_residential_indicator'
EXEC sp_OAMethod @jResp, 'StringOf', @ship_fromName OUT, 'shipments[i].ship_from.name'
EXEC sp_OAMethod @jResp, 'StringOf', @ship_fromPhone OUT, 'shipments[i].ship_from.phone'
EXEC sp_OAMethod @jResp, 'StringOf', @ship_fromCompany_name OUT, 'shipments[i].ship_from.company_name'
EXEC sp_OAMethod @jResp, 'StringOf', @ship_fromAddress_line1 OUT, 'shipments[i].ship_from.address_line1'
EXEC sp_OAMethod @jResp, 'StringOf', @ship_fromAddress_line2 OUT, 'shipments[i].ship_from.address_line2'
EXEC sp_OAMethod @jResp, 'StringOf', @ship_fromAddress_line3 OUT, 'shipments[i].ship_from.address_line3'
EXEC sp_OAMethod @jResp, 'StringOf', @ship_fromCity_locality OUT, 'shipments[i].ship_from.city_locality'
EXEC sp_OAMethod @jResp, 'StringOf', @ship_fromState_province OUT, 'shipments[i].ship_from.state_province'
EXEC sp_OAMethod @jResp, 'StringOf', @ship_fromPostal_code OUT, 'shipments[i].ship_from.postal_code'
EXEC sp_OAMethod @jResp, 'StringOf', @ship_fromCountry_code OUT, 'shipments[i].ship_from.country_code'
EXEC sp_OAMethod @jResp, 'StringOf', @ship_fromAddress_residential_indicator OUT, 'shipments[i].ship_from.address_residential_indicator'
EXEC sp_OAMethod @jResp, 'StringOf', @warehouse_id OUT, 'shipments[i].warehouse_id'
EXEC sp_OAMethod @jResp, 'StringOf', @return_toName OUT, 'shipments[i].return_to.name'
EXEC sp_OAMethod @jResp, 'StringOf', @return_toPhone OUT, 'shipments[i].return_to.phone'
EXEC sp_OAMethod @jResp, 'StringOf', @return_toCompany_name OUT, 'shipments[i].return_to.company_name'
EXEC sp_OAMethod @jResp, 'StringOf', @return_toAddress_line1 OUT, 'shipments[i].return_to.address_line1'
EXEC sp_OAMethod @jResp, 'StringOf', @return_toAddress_line2 OUT, 'shipments[i].return_to.address_line2'
EXEC sp_OAMethod @jResp, 'StringOf', @return_toAddress_line3 OUT, 'shipments[i].return_to.address_line3'
EXEC sp_OAMethod @jResp, 'StringOf', @return_toCity_locality OUT, 'shipments[i].return_to.city_locality'
EXEC sp_OAMethod @jResp, 'StringOf', @return_toState_province OUT, 'shipments[i].return_to.state_province'
EXEC sp_OAMethod @jResp, 'StringOf', @return_toPostal_code OUT, 'shipments[i].return_to.postal_code'
EXEC sp_OAMethod @jResp, 'StringOf', @return_toCountry_code OUT, 'shipments[i].return_to.country_code'
EXEC sp_OAMethod @jResp, 'StringOf', @return_toAddress_residential_indicator OUT, 'shipments[i].return_to.address_residential_indicator'
EXEC sp_OAMethod @jResp, 'StringOf', @confirmation OUT, 'shipments[i].confirmation'
EXEC sp_OAMethod @jResp, 'StringOf', @customs OUT, 'shipments[i].customs'
EXEC sp_OAMethod @jResp, 'StringOf', @external_order_id OUT, 'shipments[i].external_order_id'
EXEC sp_OAMethod @jResp, 'StringOf', @order_source_code OUT, 'shipments[i].order_source_code'
EXEC sp_OAMethod @jResp, 'StringOf', @Bill_to_account OUT, 'shipments[i].advanced_options.bill_to_account'
EXEC sp_OAMethod @jResp, 'StringOf', @Bill_to_country_code OUT, 'shipments[i].advanced_options.bill_to_country_code'
EXEC sp_OAMethod @jResp, 'StringOf', @Bill_to_party OUT, 'shipments[i].advanced_options.bill_to_party'
EXEC sp_OAMethod @jResp, 'StringOf', @Bill_to_postal_code OUT, 'shipments[i].advanced_options.bill_to_postal_code'
EXEC sp_OAMethod @jResp, 'BoolOf', @Contains_alcohol OUT, 'shipments[i].advanced_options.contains_alcohol'
EXEC sp_OAMethod @jResp, 'BoolOf', @Delivered_duty_paid OUT, 'shipments[i].advanced_options.delivered_duty_paid'
EXEC sp_OAMethod @jResp, 'BoolOf', @Non_machinable OUT, 'shipments[i].advanced_options.non_machinable'
EXEC sp_OAMethod @jResp, 'BoolOf', @Saturday_delivery OUT, 'shipments[i].advanced_options.saturday_delivery'
EXEC sp_OAMethod @jResp, 'BoolOf', @Dry_ice OUT, 'shipments[i].advanced_options.dry_ice'
EXEC sp_OAMethod @jResp, 'StringOf', @Dry_ice_weight OUT, 'shipments[i].advanced_options.dry_ice_weight'
EXEC sp_OAMethod @jResp, 'StringOf', @Freight_class OUT, 'shipments[i].advanced_options.freight_class'
EXEC sp_OAMethod @jResp, 'StringOf', @Custom_field1 OUT, 'shipments[i].advanced_options.custom_field1'
EXEC sp_OAMethod @jResp, 'StringOf', @Custom_field2 OUT, 'shipments[i].advanced_options.custom_field2'
EXEC sp_OAMethod @jResp, 'StringOf', @Custom_field3 OUT, 'shipments[i].advanced_options.custom_field3'
EXEC sp_OAMethod @jResp, 'StringOf', @insurance_provider OUT, 'shipments[i].insurance_provider'
EXEC sp_OAMethod @jResp, 'IntOf', @Value OUT, 'shipments[i].total_weight.value'
EXEC sp_OAMethod @jResp, 'StringOf', @Unit OUT, 'shipments[i].total_weight.unit'
SELECT @j = 0
EXEC sp_OAMethod @jResp, 'SizeOfArray', @count_j OUT, 'shipments[i].errors'
WHILE @j < @count_j
BEGIN
EXEC sp_OASetProperty @jResp, 'J', @j
SELECT @j = @j + 1
END
SELECT @j = 0
EXEC sp_OAMethod @jResp, 'SizeOfArray', @count_j OUT, 'shipments[i].address_validation.messages'
WHILE @j < @count_j
BEGIN
EXEC sp_OASetProperty @jResp, 'J', @j
SELECT @j = @j + 1
END
SELECT @j = 0
EXEC sp_OAMethod @jResp, 'SizeOfArray', @count_j OUT, 'shipments[i].tags'
WHILE @j < @count_j
BEGIN
EXEC sp_OASetProperty @jResp, 'J', @j
SELECT @j = @j + 1
END
SELECT @j = 0
EXEC sp_OAMethod @jResp, 'SizeOfArray', @count_j OUT, 'shipments[i].packages'
WHILE @j < @count_j
BEGIN
EXEC sp_OASetProperty @jResp, 'J', @j
EXEC sp_OAMethod @jResp, 'StringOf', @package_code OUT, 'shipments[i].packages[j].package_code'
EXEC sp_OAMethod @jResp, 'IntOf', @weightValue OUT, 'shipments[i].packages[j].weight.value'
EXEC sp_OAMethod @jResp, 'StringOf', @weightUnit OUT, 'shipments[i].packages[j].weight.unit'
EXEC sp_OAMethod @jResp, 'StringOf', @dimensionsUnit OUT, 'shipments[i].packages[j].dimensions.unit'
EXEC sp_OAMethod @jResp, 'IntOf', @Length OUT, 'shipments[i].packages[j].dimensions.length'
EXEC sp_OAMethod @jResp, 'IntOf', @Width OUT, 'shipments[i].packages[j].dimensions.width'
EXEC sp_OAMethod @jResp, 'IntOf', @Height OUT, 'shipments[i].packages[j].dimensions.height'
EXEC sp_OAMethod @jResp, 'StringOf', @v_Currency OUT, 'shipments[i].packages[j].insured_value.currency'
EXEC sp_OAMethod @jResp, 'IntOf', @Amount OUT, 'shipments[i].packages[j].insured_value.amount'
EXEC sp_OAMethod @jResp, 'StringOf', @Reference1 OUT, 'shipments[i].packages[j].label_messages.reference1'
EXEC sp_OAMethod @jResp, 'StringOf', @Reference2 OUT, 'shipments[i].packages[j].label_messages.reference2'
EXEC sp_OAMethod @jResp, 'StringOf', @Reference3 OUT, 'shipments[i].packages[j].label_messages.reference3'
EXEC sp_OAMethod @jResp, 'StringOf', @external_package_id OUT, 'shipments[i].packages[j].external_package_id'
SELECT @j = @j + 1
END
SELECT @j = 0
EXEC sp_OAMethod @jResp, 'SizeOfArray', @count_j OUT, 'shipments[i].items'
WHILE @j < @count_j
BEGIN
EXEC sp_OASetProperty @jResp, 'J', @j
SELECT @j = @j + 1
END
SELECT @i = @i + 1
END
EXEC @hr = sp_OADestroy @http
EXEC @hr = sp_OADestroy @json
EXEC @hr = sp_OADestroy @sbResponseBody
EXEC @hr = sp_OADestroy @jResp
END
GO
Curl Command
curl -X POST
-H "API-Key: {{API_KEY}}"
-H "Content-Type: application/json"
-d '{
"shipments": [{
"validate_address": "validate_and_clean",
"service_code": "usps_priority_mail",
"ship_from": {
"name": "John Doe",
"company_name": "Example Corp.",
"address_line1": "4009 Marathon Blvd",
"city_locality": "Austin",
"state_province": "TX",
"postal_code": "78756",
"country_code": "US",
"phone": "512-555-5555"
},
"ship_to": {
"name": "Amanda Miller",
"address_line1": "525 Winchester",
"postal_code": "95128",
"country_code": "US"
},
"packages": [
{
"weight": {
"value": 17,
"unit": "pound"
},
"dimensions": {
"length": 36,
"width": 12,
"height": 24,
"unit": "inch"
}
}
]
}]
}'
https://api.shipengine.com/v1/shipments
Postman Collection Item JSON
{
"name": "Validate when creating a shipment",
"event": [
{
"listen": "test",
"script": {
"exec": [
"let response = pm.response.json();",
"",
"if (response.shipments.length > 0) {",
" pm.collectionVariables.set(\"shipment_id\", response.shipments[0].shipment_id);",
"}"
],
"type": "text/javascript"
}
}
],
"request": {
"method": "POST",
"header": [
{
"key": "Content-Type",
"name": "Content-Type",
"type": "text",
"value": "application/json"
}
],
"body": {
"mode": "raw",
"raw": "{\n\t\"shipments\": [{\n\t\t\"validate_address\": \"validate_and_clean\",\n\t\t\"service_code\": \"usps_priority_mail\",\n\t\t\"ship_from\": {\n\t\t\t\"name\": \"John Doe\",\n\t\t\t\"company_name\": \"Example Corp.\",\n\t\t\t\"address_line1\": \"4009 Marathon Blvd\",\n\t\t\t\"city_locality\": \"Austin\",\n\t\t\t\"state_province\": \"TX\",\n\t\t\t\"postal_code\": \"78756\",\n\t\t\t\"country_code\": \"US\",\n\t\t\t\"phone\": \"512-555-5555\"\n\t\t},\n\t\t\"ship_to\": {\n\t\t\t\"name\": \"Amanda Miller\",\n\t\t\t\"address_line1\": \"525 Winchester\",\n\t\t\t\"postal_code\": \"95128\",\n\t\t\t\"country_code\": \"US\"\n\t\t},\n\t\t\"packages\": [\n\t\t\t{\n\t\t\t\t\"weight\": {\n\t\t\t\t\t\"value\": 17,\n\t\t\t\t\t\"unit\": \"pound\"\n\t\t\t\t},\n\t\t\t\t\"dimensions\": {\n\t\t\t\t\t\"length\": 36,\n\t\t\t\t\t\"width\": 12,\n\t\t\t\t\t\"height\": 24,\n\t\t\t\t\t\"unit\": \"inch\"\n\t\t\t\t}\n\t\t\t}\n\t\t]\n\t}]\n}"
},
"url": {
"raw": "https://api.shipengine.com/v1/shipments",
"protocol": "https",
"host": [
"api",
"shipengine",
"com"
],
"path": [
"v1",
"shipments"
]
},
"description": "You can validate the address at the same time as creating a shipment in ShipEngine by setting the `validate_address` field to either `validate_only` or `validate_and_clean`."
},
"response": [
{
"name": "Validate when creating a shipment",
"originalRequest": {
"method": "POST",
"header": [
{
"key": "Content-Type",
"name": "Content-Type",
"type": "text",
"value": "application/json"
}
],
"body": {
"mode": "raw",
"raw": "{\n\t\"shipments\": [{\n\t\t\"validate_address\": \"validate_and_clean\",\n\t\t\"service_code\": \"usps_priority_mail\",\n\t\t\"ship_from\": {\n\t\t\t\"name\": \"John Doe\",\n\t\t\t\"company_name\": \"Example Corp.\",\n\t\t\t\"address_line1\": \"4009 Marathon Blvd\",\n\t\t\t\"city_locality\": \"Austin\",\n\t\t\t\"state_province\": \"TX\",\n\t\t\t\"postal_code\": \"78756\",\n\t\t\t\"country_code\": \"US\",\n\t\t\t\"phone\": \"512-555-5555\"\n\t\t},\n\t\t\"ship_to\": {\n\t\t\t\"name\": \"Amanda Miller\",\n\t\t\t\"address_line1\": \"525 Winchester\",\n\t\t\t\"postal_code\": \"95128\",\n\t\t\t\"country_code\": \"US\"\n\t\t},\n\t\t\"packages\": [\n\t\t\t{\n\t\t\t\t\"weight\": {\n\t\t\t\t\t\"value\": 17,\n\t\t\t\t\t\"unit\": \"pound\"\n\t\t\t\t},\n\t\t\t\t\"dimensions\": {\n\t\t\t\t\t\"length\": 36,\n\t\t\t\t\t\"width\": 12,\n\t\t\t\t\t\"height\": 24,\n\t\t\t\t\t\"unit\": \"inch\"\n\t\t\t\t}\n\t\t\t}\n\t\t]\n\t}]\n}",
"options": {
"raw": {
"language": "json"
}
}
},
"url": {
"raw": "https://api.shipengine.com/v1/shipments",
"protocol": "https",
"host": [
"api",
"shipengine",
"com"
],
"path": [
"v1",
"shipments"
]
}
},
"status": "OK",
"code": 200,
"_postman_previewlanguage": "json",
"header": [
{
"key": "Date",
"value": "Wed, 18 Sep 2019 16:01:54 GMT"
},
{
"key": "Content-Type",
"value": "application/json; charset=utf-8"
},
{
"key": "Content-Length",
"value": "4156"
},
{
"key": "Connection",
"value": "keep-alive"
},
{
"key": "Access-Control-Allow-Origin",
"value": "https://www.shipengine.com"
},
{
"key": "Vary",
"value": "Origin"
},
{
"key": "x-shipengine-requestid",
"value": "9b4734c7-f7d7-4ce9-8597-e9e6696d0053"
}
],
"cookie": [
],
"body": "{\n \"has_errors\": false,\n \"shipments\": [\n {\n \"errors\": [],\n \"address_validation\": {\n \"status\": \"verified\",\n \"original_address\": {\n \"name\": \"Amanda Miller\",\n \"phone\": null,\n \"company_name\": null,\n \"address_line1\": \"525 Winchester\",\n \"address_line2\": null,\n \"address_line3\": null,\n \"city_locality\": null,\n \"state_province\": null,\n \"postal_code\": \"95128\",\n \"country_code\": \"US\",\n \"address_residential_indicator\": \"unknown\"\n },\n \"matched_address\": {\n \"name\": \"AMANDA MILLER\",\n \"phone\": null,\n \"company_name\": null,\n \"address_line1\": \"525 S WINCHESTER BLVD\",\n \"address_line2\": \"\",\n \"address_line3\": null,\n \"city_locality\": \"SAN JOSE\",\n \"state_province\": \"CA\",\n \"postal_code\": \"95128-2537\",\n \"country_code\": \"US\",\n \"address_residential_indicator\": \"no\"\n },\n \"messages\": []\n },\n \"shipment_id\": \"se-4159801\",\n \"carrier_id\": \"se-121861\",\n \"service_code\": \"usps_priority_mail\",\n \"external_shipment_id\": null,\n \"ship_date\": \"2019-09-18T00:00:00Z\",\n \"created_at\": \"2019-09-18T16:01:54.9171198Z\",\n \"modified_at\": \"2019-09-18T16:01:54.9171198Z\",\n \"shipment_status\": \"pending\",\n \"ship_to\": {\n \"name\": \"AMANDA MILLER\",\n \"phone\": null,\n \"company_name\": null,\n \"address_line1\": \"525 S WINCHESTER BLVD\",\n \"address_line2\": \"\",\n \"address_line3\": null,\n \"city_locality\": \"SAN JOSE\",\n \"state_province\": \"CA\",\n \"postal_code\": \"95128-2537\",\n \"country_code\": \"US\",\n \"address_residential_indicator\": \"no\"\n },\n \"ship_from\": {\n \"name\": \"John Doe\",\n \"phone\": \"512-555-5555\",\n \"company_name\": \"Example Corp.\",\n \"address_line1\": \"4009 Marathon Blvd\",\n \"address_line2\": null,\n \"address_line3\": null,\n \"city_locality\": \"Austin\",\n \"state_province\": \"TX\",\n \"postal_code\": \"78756\",\n \"country_code\": \"US\",\n \"address_residential_indicator\": \"unknown\"\n },\n \"warehouse_id\": null,\n \"return_to\": {\n \"name\": \"John Doe\",\n \"phone\": \"512-555-5555\",\n \"company_name\": \"Example Corp.\",\n \"address_line1\": \"4009 Marathon Blvd\",\n \"address_line2\": null,\n \"address_line3\": null,\n \"city_locality\": \"Austin\",\n \"state_province\": \"TX\",\n \"postal_code\": \"78756\",\n \"country_code\": \"US\",\n \"address_residential_indicator\": \"unknown\"\n },\n \"confirmation\": \"none\",\n \"customs\": null,\n \"external_order_id\": null,\n \"order_source_code\": null,\n \"advanced_options\": {\n \"bill_to_account\": null,\n \"bill_to_country_code\": null,\n \"bill_to_party\": null,\n \"bill_to_postal_code\": null,\n \"contains_alcohol\": false,\n \"delivered_duty_paid\": false,\n \"non_machinable\": false,\n \"saturday_delivery\": false,\n \"dry_ice\": false,\n \"dry_ice_weight\": null,\n \"freight_class\": null,\n \"custom_field1\": null,\n \"custom_field2\": null,\n \"custom_field3\": null\n },\n \"insurance_provider\": \"none\",\n \"tags\": [],\n \"packages\": [\n {\n \"package_code\": \"package\",\n \"weight\": {\n \"value\": 17,\n \"unit\": \"pound\"\n },\n \"dimensions\": {\n \"unit\": \"inch\",\n \"length\": 36,\n \"width\": 12,\n \"height\": 24\n },\n \"insured_value\": {\n \"currency\": \"usd\",\n \"amount\": 0\n },\n \"label_messages\": {\n \"reference1\": null,\n \"reference2\": null,\n \"reference3\": null\n },\n \"external_package_id\": null\n }\n ],\n \"total_weight\": {\n \"value\": 17,\n \"unit\": \"pound\"\n },\n \"items\": []\n }\n ]\n}"
}
]
}
