(SQL Server) JSON Parsing with Sample Data for a Merchant/Payment Transaction

Demonstrates how to load the following JSON into a JSON object and access the values for this document:

{
"id":"8a829449561d9dcb01571dbee3b275b1",
"paymentType":"DB",
"paymentBrand":"VISA",
"amount":"156.00",
"currency":"EUR",
"merchantTransactionId":"E8A39B31-6FA3-4014-A195-3074DF5BF7A1",
"result":{
	"code":"000.100.110",
	"description":"Request successfully processed in 'Merchant in Integrator Test Mode'"
},
"resultDetails":{
	"ConnectorTxID3":"12311312",
	"ConnectorTxID1":"717473"
},
"card":{
	"bin":"420000",
	"last4Digits":"0000",
	"holder":"Andreas",
	"expiryMonth":"12",
	"expiryYear":"2018"
},
"risk":{
	"score":"100"
},
"buildNumber":"4b471ea5366e5e9c9a21392a39769f8d7b40b4e8@2016-09-08 13:31:54 +0000",
"timestamp":"2016-09-12 09:33:52+0000",
}

Chilkat ActiveX Downloads ActiveX for 32-bit and 64-bit Windows

-- Important: See this note about string length limitations for strings returned by sp_OAMethod calls.
--
CREATE PROCEDURE ChilkatSample
AS
BEGIN
    DECLARE @hr int
    -- Important: Do not use nvarchar(max).  See the warning about using nvarchar(max).
    DECLARE @sTmp0 nvarchar(4000)
    DECLARE @json int
    -- Use "Chilkat_9_5_0.JsonObject" for versions of Chilkat < 10.0.0
    EXEC @hr = sp_OACreate 'Chilkat.JsonObject', @json OUT
    IF @hr <> 0
    BEGIN
        PRINT 'Failed to create ActiveX component'
        RETURN
    END

    -- Load the JSON into the object.
    -- Call json.Load to load from a string rather than a file...
    DECLARE @success int
    EXEC sp_OAMethod @json, 'LoadFile', @success OUT, 'qa_data/json/merchantPayment.json'
    -- We are assuming success..

    -- Get the easy stuff:

    EXEC sp_OAMethod @json, 'StringOf', @sTmp0 OUT, 'id'
    PRINT 'id: ' + @sTmp0

    EXEC sp_OAMethod @json, 'StringOf', @sTmp0 OUT, 'paymentType'
    PRINT 'paymentType: ' + @sTmp0

    EXEC sp_OAMethod @json, 'StringOf', @sTmp0 OUT, 'currency'
    PRINT 'currency: ' + @sTmp0

    EXEC sp_OAMethod @json, 'StringOf', @sTmp0 OUT, 'buildNumber'
    PRINT 'buildNumber: ' + @sTmp0

    -- Get information that's nested within a sub-object:

    EXEC sp_OAMethod @json, 'StringOf', @sTmp0 OUT, 'result.code'
    PRINT 'result: code: ' + @sTmp0

    EXEC sp_OAMethod @json, 'StringOf', @sTmp0 OUT, 'result.description'
    PRINT 'result: description: ' + @sTmp0


    EXEC sp_OAMethod @json, 'StringOf', @sTmp0 OUT, 'card.bin'
    PRINT 'card: bin: ' + @sTmp0

    EXEC sp_OAMethod @json, 'StringOf', @sTmp0 OUT, 'card.last4Digits'
    PRINT 'card: last4Digits: ' + @sTmp0

    EXEC @hr = sp_OADestroy @json


END
GO