Sep 13 2024 10:56 PM
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
Sep 14 2024 08:27 AM - edited Sep 14 2024 08:28 AM
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