How to create Json according to below Schema RSS

6 replies

Last post Feb 08, 2021 03:35 PM by bruce (sqlwork.com)

‹ Previous Thread|Next Thread ›

Shortcuts

Related Links

jagjit saini

Member

45 Points

174 Posts

How to create Json according to below Schema

Feb 07, 2021 02:37 PM|jagjit saini|LINK

 
  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": "abc@gmail.com"

},

"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": "xyz@yahoo.com"

},

"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

- Reply
bruce (sqlwo...
All-Star

58144 Points

15641 Posts

Re: How to create Json according to below Schema

Feb 07, 2021 05:05 PM|bruce (sqlwork.com)|LINK

You can use vs2019 special paste of json as class or

https://json2csharp.com

bruce (sqlwork.com)
- Reply
jagjit saini
Member

45 Points

174 Posts

Re: How to create Json according to below Schema

Feb 08, 2021 05:41 AM|jagjit saini|LINK

Hi

That's ok. I have 2 tables . One is Master & other is for Item Details. From those tables i want that Json should be created according to the above Schema

Thanks
- Reply
YihuiSun
Contributor

2690 Points

774 Posts
Re: How to create Json according to below Schema

Feb 08, 2021 06:57 AM|YihuiSun|LINK
Hi jagjit saini,

Do you want to convert the object to JSON?
```
string output = JsonConvert.SerializeObject(test); 
```
You can click this link to see more explanation.

Best Regards,

YihuiSun
.NET forums are moving to a new home on Microsoft Q&A, we encourage you to go to Microsoft Q&A for .NET for posting new questions and get involved today.
- Reply
jagjit saini
Member

45 Points

174 Posts

Re: How to create Json according to below Schema

Feb 08, 2021 08:36 AM|jagjit saini|LINK

Hi YihuiSun

I want to know how to pass values from Database to Object & then convert to Json

Thanks
- Reply
jagjit saini
Member

45 Points

174 Posts

Re: How to create Json according to below Schema

Feb 08, 2021 08:37 AM|jagjit saini|LINK

Hi Bruce

I want to know how to pass values from Database table to Object & then convert to Json. I have already created Objects.

Thanks
- Reply
bruce (sqlwo...
All-Star

58144 Points

15641 Posts

Re: How to create Json according to below Schema

Feb 08, 2021 03:35 PM|bruce (sqlwork.com)|LINK

Use the tool of your choice to create the class objects for the json schema. Then map the database rows to the new objects. Your sql will return the tables as a product, which you will need to remap to a hierarchy. You could also use Sqlserver json support to build the json string,

bruce (sqlwork.com)

‹ Previous Thread|Next Thread ›

How to create Json according to below Schema RSS

6 replies

Shortcuts

Related Links

How to create Json according to below Schema

Re: How to create Json according to below Schema

Re: How to create Json according to below Schema

Re: How to create Json according to below Schema

Re: How to create Json according to below Schema

Re: How to create Json according to below Schema

Re: How to create Json according to below Schema