JSON Parsing
The output should be with 2 rows:
TVS1111 0 DBSSI ICICI addr1
TVS1111 0 DBSSI ICICI addr2
and we have to do JSON parsing for the below JSON string
declare @pJson NVARCHAR(MAX) = ‘{
“header”: {
“msgId”: “TVS1111”
},
“txnResponses”: [{
“chargesAmount”: “0”,
“senderParty”: {
“swiftBic”: “DBSSI”
},
“receivingParty”: {
“bankName”: “ICICI”,
“bankAddresses”: [{
“address”: “addr1”
},
{
“address”: “addr2”
}]
}
}]
}’
I attempted the following but it does not get the desired output
select
a.msgId,
b.chargesAmount,
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
(
chargesAmount 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;
The output should be with 2 rows: TVS1111 0 DBSSI ICICI addr1TVS1111 0 DBSSI ICICI addr2 and we have to do JSON parsing for the below JSON string declare @pJson NVARCHAR(MAX) = ‘{ “header”: { “msgId”: “TVS1111” }, “txnResponses”: [{ “chargesAmount”: “0”, “senderParty”: { “swiftBic”: “DBSSI” }, “receivingParty”: { “bankName”: “ICICI”, “bankAddresses”: [{ “address”: “addr1” }, { “address”: “addr2” }] } }]}’ I attempted the following but it does not get the desired outputselect a.msgId, b.chargesAmount, 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( chargesAmount 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; Read More
