How to create Json according to below Schema

Question

Hi

 

I have 2 tables . I want to convert data into Json format according to the below Schema. What should be the Models. Since DispDetls are blank in Database it should be Null.

 

I am using MVC

{

"Version": "1.1",

"TranDtls": { -

"TaxSch": "GST",

"SupTyp": "B2B",

"RegRev": "Y",

"EcmGstin": null,

"IgstOnIntra": "N"

},

"DocDtls": {

"Typ": "INV",

"No": "DOC/001",

"Dt": "18/08/2020"

},

"SellerDtls": {

"Gstin": "37ARZPT4384Q1MT",

"LglNm": "NIC company pvt ltd",

"TrdNm": "NIC Industries",

"Addr1": "5th block, kuvempu layout",

"Addr2": "kuvempu layout",

"Loc": "GANDHINAGAR",

"Pin": 518001,

"Stcd": "37",

"Ph": "9000000000",

"Em": "[email protected]"

},

"BuyerDtls": {

"Gstin": "29AWGPV7107B1Z1",

"LglNm": "XYZ company pvt ltd",

"TrdNm": "XYZ Industries",

"Pos": "12",

"Addr1": "7th block, kuvempu layout",

"Addr2": "kuvempu layout",

"Loc": "GANDHINAGAR",

"Pin": 562160,

"Stcd": "29",

"Ph": "91111111111",

"Em": "[email protected]"

},

"DispDtls": {

"Nm": "ABC company pvt ltd",

"Addr1": "7th block, kuvempu layout",

"Addr2": "kuvempu layout",

"Loc": "Banagalore",

"Pin": 562160,

"Stcd": "29"

},

"ItemList": [

{

"SlNo": "1",

"PrdDesc": "Rice",

"IsServc": "N",

"HsnCd": "1001",

"Barcde": "123456",

"Qty": 100.345,

"FreeQty": 10,

"Unit": "BAG",

"UnitPrice": 99.545,

"TotAmt": 9988.84,

"Discount": 10,

"PreTaxVal": 1,

"AssAmt": 9978.84,

"GstRt": 12.0,

"IgstAmt": 1197.46,

"CgstAmt": 0,

"SgstAmt": 0,

"CesRt": 5,

"CesAmt": 498.94,

"CesNonAdvlAmt": 10,

"StateCesRt": 12,

"StateCesAmt": 1197.46,

"StateCesNonAdvlAmt": 5,

"OthChrg": 10,

"TotItemVal": 12897.7,

"OrdLineRef": "3256",

"OrgCntry": "AG",

"PrdSlNo": "12345",

"BchDtls": {

"Nm": "123456",

"ExpDt": "01/08/2020",

"WrDt": "01/09/2020"

},

"AttribDtls": [

{

"Nm": "Rice",

"Val": "10000"

}

]

}

],

}

--------------------------------------------------------------------------------------------

USE [Test]

GO

/****** Object: Table [dbo].[Master0] Script Date: 07-02-2021 19:49:15 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

SET ANSI_PADDING ON

GO

CREATE TABLE [dbo].[Master0](

[TaxSch] [varchar](10) NULL,

[SubTyp] [varchar](10) NULL,

[RegRev] [varchar](1) NULL,

[EcmGstin] [varchar](15) NULL,

[IgstOnIntra] [varchar](1) NULL,

[Typ] [varchar](3) NULL,

[No] [varchar](16) NULL,

[Dt] [varchar](10) NULL,

[GSTIN] [varchar](15) NULL,

[LglNm] [varchar](100) NULL,

[TrdNm] [varchar](100) NULL,

[Addr1] [varchar](100) NULL,

[Addr2] [varchar](100) NULL,

[Loc] [varchar](50) NULL,

[Pin] [int] NULL,

[Stcd] [varchar](2) NULL,

[Ph] [varchar](12) NULL,

[Em] [varchar](100) NULL,

[BuyerGSTIN] [varchar](15) NULL,

[BuyerLglNm] [varchar](100) NULL,

[BuyerTrdNm] [varchar](100) NULL,

[BuyerPos] [varchar](2) NULL,

[BuyerAddr1] [varchar](100) NULL,

[BuyerAddr2] [varchar](100) NULL,

[BuyerLoc] [varchar](50) NULL,

[BuyerPin] [int] NULL,

[BuyerStcd] [varchar](2) NULL,

[BuyerPhone] [varchar](12) NULL,

[BuyerEmail] [varchar](100) NULL,

[DispatchNm] [varchar](100) NULL,

[DispatchAddr1] [varchar](100) NULL,

[DispatchAddr2] [varchar](100) NULL,

[DispatchLoc] [varchar](50) NULL,

[DispatchPin] [int] NULL,

[DispatchStcd] [varchar](2) NULL,

[ShipGSTIN] [varchar](15) NULL,

[ShipLglNm] [varchar](100) NULL,

[ShipTrdNm] [varchar](100) NULL,

[ShipAddr1] [varchar](100) NULL,

[ShipAddr2] [varchar](100) NULL,

[ShipLoc] [varchar](50) NULL,

[ShipPin] [int] NULL,

[ShipStcd] [varchar](2) NULL,

[AssVal] [decimal](18, 2) NULL,

[CgstVal] [decimal](18, 2) NULL,

[IgstVal] [decimal](18, 2) NULL,

[CesVal] [decimal](18, 2) NULL,

[StCesVal] [decimal](18, 2) NULL,

[Discount] [decimal](18, 2) NULL,

[OthChg] [decimal](18, 2) NULL,

[RndOfAmount] [decimal](18, 2) NULL,

[TotalInvVal] [decimal](18, 2) NULL,

[TotalInvValFc] [decimal](18, 2) NULL,

[PayeeName] [nvarchar](100) NULL,

[PayeeAccDet] [nvarchar](18) NULL,

[PayeeMode] [nvarchar](18) NULL,

[PayeeBranch] [nvarchar](11) NULL,

[PayeePayTerm] [nvarchar](100) NULL,

[PayeePaymentInstruction] [nvarchar](100) NULL,

[PayeeCreditTfr] [nvarchar](100) NULL,

[PayeeDirectDebit] [nvarchar](100) NULL,

[PayeeCreditDays] [nvarchar](100) NULL,

[PayeePaidAmt] [decimal](18, 2) NULL,

[PayeePaymentDue] [decimal](18, 2) NULL,

[RefRemarksNote] [nvarchar](100) NULL,

[InvStDt] [nvarchar](10) NULL,

[InvEndDt] [nvarchar](10) NULL,

[InvNo] [nvarchar](16) NULL,

[InvDt] [nvarchar](10) NULL,

[OtherRefNo] [nvarchar](20) NULL,

[AdviceNo] [nvarchar](20) NULL,

[ReceiptAdvicetDt] [nvarchar](10) NULL,

[BatchRef] [nvarchar](20) NULL,

[ContractRef] [nvarchar](20) NULL,

[OtherRef] [nvarchar](20) NULL,

[ProjRef] [nvarchar](20) NULL,

[RefNo] [nvarchar](16) NULL,

[RefDate] [nvarchar](10) NULL,

[DocumentUrl] [nvarchar](100) NULL,

[Docs] [nvarchar](1000) NULL,

[Info] [nvarchar](1000) NULL,

[ShipBillNo] [nvarchar](20) NULL,

[ShipBillDate] [nvarchar](10) NULL,

[Port] [nvarchar](10) NULL,

[RefundClaim] [nvarchar](1) NULL,

[ForCurrency] [nvarchar](16) NULL,

[CountryCode] [nvarchar](2) NULL,

[ExpDuty] [int] NULL,

[TransId] [nvarchar](15) NULL,

[TransName] [nvarchar](100) NULL,

[TransMode] [nvarchar](1) NULL,

[Distance] [int] NULL,

[TransDocNo] [nvarchar](15) NULL,

[TransDocDate] [nvarchar](10) NULL,

[VehicleNo] [nvarchar](20) NULL,

[VehType] [nvarchar](1) NULL

) ON [PRIMARY]

GO

SET ANSI_PADDING OFF

GO

/****** Object: Table [dbo].[Trans0] Script Date: 07-02-2021 19:49:15 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

SET ANSI_PADDING ON

GO

CREATE TABLE [dbo].[Trans0](

[No] [nvarchar](16) NULL,

[SlNo] [varchar](6) NULL,

[ProductDesc] [varchar](100) NULL,

[IsServ] [varchar](1) NULL,

[HsnCode] [varchar](8) NULL,

[BarCode] [varchar](30) NULL,

[Qty] [decimal](18, 2) NULL,

[FreeQty] [decimal](18, 2) NULL,

[Unit] [varchar](8) NULL,

[UnitPrice] [decimal](18, 2) NULL,

[TotAmount] [decimal](18, 2) NULL,

[Discount] [decimal](18, 2) NULL,

[PreTaxVal] [decimal](18, 2) NULL,

[AssAmt] [decimal](18, 2) NULL,

[GstRate] [decimal](18, 2) NULL,

[IgstAmt] [decimal](18, 2) NULL,

[CgstAmt] [decimal](18, 2) NULL,

[SgstAmt] [decimal](18, 2) NULL,

[CessRate] [decimal](18, 2) NULL,

[CessAmount] [decimal](18, 2) NULL,

[CessNonAdvolAmt] [decimal](18, 2) NULL,

[StateCessRate] [decimal](18, 2) NULL,

[StateCessNonAdvolAmt] [decimal](18, 2) NULL,

[OtherCharges] [decimal](18, 2) NULL,

[TotItemVal] [decimal](18, 2) NULL,

[OrdLineRef] [varchar](50) NULL,

[OriginCountry] [varchar](2) NULL,

[ProductSrNo] [varchar](20) NULL,

[BatchNo] [varchar](20) NULL,

[BatchExpDate] [varchar](10) NULL,

[BatchWarrantDate] [varchar](10) NULL,

[AttributeName] [varchar](100) NULL,

[AttributeVal] [varchar](100) NULL

) ON [PRIMARY]

GO

SET ANSI_PADDING OFF

GO

INSERT [dbo].[Master0] ([TaxSch], [SubTyp], [RegRev], [EcmGstin], [IgstOnIntra], [Typ], [No], [Dt], [GSTIN], [LglNm], [TrdNm], [Addr1], [Addr2], [Loc], [Pin], [Stcd], [Ph], [Em], [BuyerGSTIN], [BuyerLglNm], [BuyerTrdNm], [BuyerPos], [BuyerAddr1], [BuyerAddr2], [BuyerLoc], [BuyerPin], [BuyerStcd], [BuyerPhone], [BuyerEmail], [DispatchNm], [DispatchAddr1], [DispatchAddr2], [DispatchLoc], [DispatchPin], [DispatchStcd], [ShipGSTIN], [ShipLglNm], [ShipTrdNm], [ShipAddr1], [ShipAddr2], [ShipLoc], [ShipPin], [ShipStcd], [AssVal], [CgstVal], [IgstVal], [CesVal], [StCesVal], [Discount], [OthChg], [RndOfAmount], [TotalInvVal], [TotalInvValFc], [PayeeName], [PayeeAccDet], [PayeeMode], [PayeeBranch], [PayeePayTerm], [PayeePaymentInstruction], [PayeeCreditTfr], [PayeeDirectDebit], [PayeeCreditDays], [PayeePaidAmt], [PayeePaymentDue], [RefRemarksNote], [InvStDt], [InvEndDt], [InvNo], [InvDt], [OtherRefNo], [AdviceNo], [ReceiptAdvicetDt], [BatchRef], [ContractRef], [OtherRef], [ProjRef], [RefNo], [RefDate], [DocumentUrl], [Docs], [Info], [ShipBillNo], [ShipBillDate], [Port], [RefundClaim], [ForCurrency], [CountryCode], [ExpDuty], [TransId], [TransName], [TransMode], [Distance], [TransDocNo], [TransDocDate], [VehicleNo], [VehType]) VALUES (N'GST', N'B2B', N'N', N'', N'N', N'INV', N'INV/00001', N'2020/08/06', N'24AAAAA1116A1AA', N'Traders', N'', N'675', N'', N'BNG', 506060, N'01', N'', N'', N'24AAAAA1116A1AA', N'Traders', N'', N'14', N'879', N'', N'Delhi', 160087, N'14', N'', N'', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL)

INSERT [dbo].[Master0] ([TaxSch], [SubTyp], [RegRev], [EcmGstin], [IgstOnIntra], [Typ], [No], [Dt], [GSTIN], [LglNm], [TrdNm], [Addr1], [Addr2], [Loc], [Pin], [Stcd], [Ph], [Em], [BuyerGSTIN], [BuyerLglNm], [BuyerTrdNm], [BuyerPos], [BuyerAddr1], [BuyerAddr2], [BuyerLoc], [BuyerPin], [BuyerStcd], [BuyerPhone], [BuyerEmail], [DispatchNm], [DispatchAddr1], [DispatchAddr2], [DispatchLoc], [DispatchPin], [DispatchStcd], [ShipGSTIN], [ShipLglNm], [ShipTrdNm], [ShipAddr1], [ShipAddr2], [ShipLoc], [ShipPin], [ShipStcd], [AssVal], [CgstVal], [IgstVal], [CesVal], [StCesVal], [Discount], [OthChg], [RndOfAmount], [TotalInvVal], [TotalInvValFc], [PayeeName], [PayeeAccDet], [PayeeMode], [PayeeBranch], [PayeePayTerm], [PayeePaymentInstruction], [PayeeCreditTfr], [PayeeDirectDebit], [PayeeCreditDays], [PayeePaidAmt], [PayeePaymentDue], [RefRemarksNote], [InvStDt], [InvEndDt], [InvNo], [InvDt], [OtherRefNo], [AdviceNo], [ReceiptAdvicetDt], [BatchRef], [ContractRef], [OtherRef], [ProjRef], [RefNo], [RefDate], [DocumentUrl], [Docs], [Info], [ShipBillNo], [ShipBillDate], [Port], [RefundClaim], [ForCurrency], [CountryCode], [ExpDuty], [TransId], [TransName], [TransMode], [Distance], [TransDocNo], [TransDocDate], [VehicleNo], [VehType]) VALUES (N'GST', N'B2B', N'N', N'', N'N', N'INV', N'INV/00002', N'2020/09/15', N'14AAAAA1116A1AA', N'Traders', N'', N'675', N'', N'BNG', 506060, N'14', N'', N'', N'17AAAAA1116A1AA', N'Traders', N'', N'17', N'879', N'', N'XYZ', 160087, N'17', N'', N'', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL)

INSERT [dbo].[Trans0] ([No], [SlNo], [ProductDesc], [IsServ], [HsnCode], [BarCode], [Qty], [FreeQty], [Unit], [UnitPrice], [TotAmount], [Discount], [PreTaxVal], [AssAmt], [GstRate], [IgstAmt], [CgstAmt], [SgstAmt], [CessRate], [CessAmount], [CessNonAdvolAmt], [StateCessRate], [StateCessNonAdvolAmt], [OtherCharges], [TotItemVal], [OrdLineRef], [OriginCountry], [ProductSrNo], [BatchNo], [BatchExpDate], [BatchWarrantDate], [AttributeName], [AttributeVal]) VALUES (N'INV/00001', N'1', N'Pr01', N'N', N'1001', N'', CAST(10.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), N'BAGS', CAST(200.00 AS Decimal(18, 2)), CAST(2000.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(5.00 AS Decimal(18, 2)), CAST(1000.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(3000.00 AS Decimal(18, 2)), N'', N'', N'', N'', N'', N'', N'', N'')

INSERT [dbo].[Trans0] ([No], [SlNo], [ProductDesc], [IsServ], [HsnCode], [BarCode], [Qty], [FreeQty], [Unit], [UnitPrice], [TotAmount], [Discount], [PreTaxVal], [AssAmt], [GstRate], [IgstAmt], [CgstAmt], [SgstAmt], [CessRate], [CessAmount], [CessNonAdvolAmt], [StateCessRate], [StateCessNonAdvolAmt], [OtherCharges], [TotItemVal], [OrdLineRef], [OriginCountry], [ProductSrNo], [BatchNo], [BatchExpDate], [BatchWarrantDate], [AttributeName], [AttributeVal]) VALUES (N'INV/00002', N'1', N'Pr02', N'N', N'1002', N'', CAST(10.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), N'BAGS', CAST(200.00 AS Decimal(18, 2)), CAST(2000.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(5.00 AS Decimal(18, 2)), CAST(1000.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(3000.00 AS Decimal(18, 2)), N'', N'', N'', N'', N'', N'', N'', N'')

INSERT [dbo].[Trans0] ([No], [SlNo], [ProductDesc], [IsServ], [HsnCode], [BarCode], [Qty], [FreeQty], [Unit], [UnitPrice], [TotAmount], [Discount], [PreTaxVal], [AssAmt], [GstRate], [IgstAmt], [CgstAmt], [SgstAmt], [CessRate], [CessAmount], [CessNonAdvolAmt], [StateCessRate], [StateCessNonAdvolAmt], [OtherCharges], [TotItemVal], [OrdLineRef], [OriginCountry], [ProductSrNo], [BatchNo], [BatchExpDate], [BatchWarrantDate], [AttributeName], [AttributeVal]) VALUES (N'INV/00002', N'1', N'Pr03', N'N', N'1003', N'', CAST(10.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), N'BAGS', CAST(200.00 AS Decimal(18, 2)), CAST(2000.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(5.00 AS Decimal(18, 2)), CAST(1000.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(3000.00 AS Decimal(18, 2)), N'', N'', N'', N'', N'', N'', N'', N'')

Thanks