How to parse the nested Json into a MS SQL Table?-CodePudding

I would like to parse this Json response file coming from a rest api to a MS SQL table in C# as I don't want to loop multiple times as the files could be big. I only need the nested block PolicyPayrolls Policy Details to be stored into a table. Is there any way I can store parse this nested block inserted into a table. How do I approach it?


      {
   "ValidationErrors":[
      
   ],
   "ApplicationExceptions":[
      
   ],
   "PageSize":2,
   "CurrentPageIndex":1,
   "NextPageIndex":2,
   "PolicyStatus":"CANCELLED",
   "PayrollStatus":"OUTSTANDING",
   "PolicyPayrolls":[
      {
         "PolicyDetails":{
            "PolicyName":"Test 1",
            "PolicyHeaderId":1133,
            "GroupIdn":"",
            "PolicyNumber":234,
            "PolicyUnitNumber":16612,
            "PolicyYearDate":"2022",
            "PolicySuffixCode":"",
            "InceptionDate":"2022-01-08T00:00:00",
            "ExpirationnDate":"2023-01-08T00:00:00",
            "BillPlanType":"STIPULATED"
         },
         "PayrollDetails":[
            {
               "PayrollId":1284,
               "PayrollStatus":"Outstanding",
               "StartDate":"2022-01-08T00:00:00",
               "EndDate":"2022-06-06T00:00:00",
               "DueDate":"2022-07-07T18:59:08.0316001"
            }
         ]
      },
      {
         "PolicyDetails":{
            "PolicyName":"TEST POLICY 2313",
            "PolicyHeaderId":625,
            "GroupIdn":"",
            "PolicyNumber":4381,
            "PolicyUnitNumber":193,
            "PolicyYearDate":"2021",
            "PolicySuffixCode":"",
            "InceptionDate":"2021-07-01T00:00:00",
            "ExpirationnDate":"2022-07-01T00:00:00",
            "BillPlanType":"STIPULATED"
         },
         "PayrollDetails":[
            {
               "PayrollId":1200,
               "PayrollStatus":"Outstanding",
               "StartDate":"2022-01-01T00:00:00",
               "EndDate":"2022-06-06T00:00:00",
               "DueDate":"2022-07-07T18:58:06.0948818"
            }
         ]
      }
   ],
   "PolicyUnitNumber":0,
   "PolicyYearDate":null,
   "SucessResult":true,
   "ResultCount":69
}

DDL

 CREATE TABLE [DBO].[POLICY_OUTSTANDING](
  [Id] [bigint] IDENTITY(1,1) NOT NULL,
  [PolicyYear] [varchar](50) NULL,
  [PolicySuffix] [varchar](20) NULL,
  [PolicyUnitNumber] [varchar](50) NULL,
  [PolicyGroupId] [varchar](50) NULL,
  [CreatedBy] [varchar](20) NOT NULL DEFAULT ('DEVDB'),
  [CreatedDate] [datetime2](7) NOT NULL CONSTRAINT [DC_PolicyDue_CreatedDate]  DEFAULT (getdate()),
  [UpdatedBy] [varchar](20) NOT NULL CONSTRAINT [DC_PolicyDue_ModifiedBy]  DEFAULT ('DEVDB'),
  [UpdatedDate] [datetime2](7) NOT NULL CONSTRAINT [DC_PolicyDue_ModifiedDate]  DEFAULT (getdate()),
  [InceptionDate] [datetime2](7) NULL,
  [ExpirationDate] [datetime2](7) NULL,
  [PolicyNumber] INT NULL,
    CONSTRAINT [PK_PolicyDue] PRIMARY KEY CLUSTERED 
     (
   [Id] ASC
     )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, 
     IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS 
     = ON) ON [PRIMARY]
     ) ON [PRIMARY]

     GO

CodePudding user response：

Take a look at