Parsing a JSON string in sqlserver

Copper Contributor

I am attempting a JSON string. The expected output is

TVSTP45743     0     DBSSINB0XXX     ICICI BANK LIMITED     addr1

TVSTP45743     0     DBSSINB0XXX     ICICI BANK LIMITED     addr2

 

Please help me

 

/*
EXEC [dbo].[API_JCRUD_DBS_RTGS_ACK33]
'{
"header": {
"msgId": "TVSTP45743"
},
"txnResponses": [{
"chargesAmount": "0",
"senderParty": {
"swiftBic": "DBSSINB0XXX"
},
"receivingParty": {
"bankName": "ICICI BANK LIMITED",
"bankAddresses": [{
"address": "addr1"
},

{
"address": "addr2"
}]
}
}]
}'

*/

alter PROCEDURE [dbo].[API_JCRUD_DBS_RTGS_ACK33]
@pJson VARCHAR(MAX)
AS
BEGIN
SET NOCOUNT ON;

BEGIN TRY
BEGIN TRAN
select a.msgId,
b.responseType,
b.senderPartyswiftBic,
b.receivingPartybankName,
c.address1
FROM OPENJSON(@pJson)
WITH
(
msgId VARCHAR(100) N'$.header.msgId',
txnResponses NVARCHAR(MAX) AS JSON
) as a
CROSS APPLY OPENJSON(a.txnResponses)
WITH
(
responseType VARCHAR(100) '$.chargesAmount',
senderPartyswiftBic VARCHAR(100) '$.senderParty.swiftBic',
receivingPartybankName VARCHAR(100) '$.receivingParty.bankName',

bankAddresses NVARCHAR(MAX) AS JSON
) as b
CROSS APPLY OPENJSON(b.bankAddresses)
WITH (
address1 VARCHAR(100) '$.address'
) as c;
COMMIT TRAN
END TRY
BEGIN CATCH

END CATCH
end

1 Reply

@Manoj_Gokhale 

 

SELECT 
	 a.Number 
	,b.responseType
	,b.senderPartyswiftBic
	,b.receivingPartybankName
	,c.address1
FROM OPENJSON ( @pjson )
WITH 
(   
	 Number   VARCHAR(50)   '$.header.msgId' 
	,txnResponses NVARCHAR(MAX) '$.txnResponses' AS JSON
)a
CROSS APPLY
OPENJSON ( txnResponses,  '$')
WITH 
(   
	 responseType     INT       '$.chargesAmount'
	,senderPartyswiftBic VARCHAR(50)   '$.senderParty.swiftBic'
	,receivingPartybankName VARCHAR(100)            '$.receivingParty.bankName'
	,bankAddresses NVARCHAR(MAX) '$.receivingParty.bankAddresses' AS JSON
)b
CROSS APPLY
OPENJSON ( bankAddresses,  '$')
WITH 
(   
	address1  VARCHAR(100) '$.address'
)c