Forum Discussion

Manoj_Gokhale's avatar
Manoj_Gokhale
Copper Contributor
Sep 14, 2024

Parsing a JSON string in sqlserver

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

  • rodgerkong's avatar
    rodgerkong
    Iron Contributor
    Sep 14, 2024

    Manoj_Gokhale 

     

    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) N'$.txnResponses' AS JSON
			)a
			CROSS APPLY
			OPENJSON ( a.txnResponses, N'$')
			WITH 
			(   
				 responseType VARCHAR(100) N'$.chargesAmount'
				,senderPartyswiftBic VARCHAR(100) N'$.senderParty.swiftBic'
				,receivingPartybankName VARCHAR(100) N'$.receivingParty.bankName'
				,bankAddresses NVARCHAR(MAX) N'$.receivingParty.bankAddresses' AS JSON
			)b
			CROSS APPLY
			OPENJSON ( b.bankAddresses, N'$')
			WITH 
			(   
				address1  VARCHAR(100) N'$.address'
			)c
		COMMIT TRAN
	END TRY
	BEGIN CATCH
	END CATCH
END

     

Resources