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
I am attempting a JSON string. The expected output isTVSTP45743 0 DBSSINB0XXX ICICI BANK LIMITED addr1TVSTP45743 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)ASBEGINSET NOCOUNT ON;BEGIN TRYBEGIN TRANselect a.msgId,b.responseType,b.senderPartyswiftBic,b.receivingPartybankName,c.address1FROM OPENJSON(@pJson)WITH(msgId VARCHAR(100) N’$.header.msgId’,txnResponses NVARCHAR(MAX) AS JSON) as aCROSS 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 bCROSS APPLY OPENJSON(b.bankAddresses)WITH (address1 VARCHAR(100) ‘$.address’) as c;COMMIT TRANEND TRYBEGIN CATCHEND CATCHend Read More
