Forum Discussion
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;
- rodgerkongIron Contributor
See the discussion you post before
https://techcommunity.microsoft.com/t5/sql-server/parsing-a-json-string-in-sqlserver/td-p/4245342
- Manoj_GokhaleCopper ContributorThanks a lot sir ... Thanks a lot ... Thanks