Parsing XML SQL and making a table

See more:

XML

SQL

JSON

Following is the code

I already have parsed it in SQL

TimePeriod |AmountType| Amount| TimePeriod |PlanDescription |ServiceTypesCode |ServiceTypesName |inPlanNetwork coverageLevel
calendar_year |Deductible| 3000 | calendar_year |OPTIONS PPO |30 |health_benefit_plan_coverage |No - Applies to out of network providers


But i want to create a table and stack them in a table, such as make a field call time period, amounttype, Amount

timePeriod amountType amount planDescription serviceTypes inPlanNetwork
calendar_year Deductible 3000 OPTIONS PPO health_benefit_plan_coverage , 30 No - Applies to out of network providers
remaining Deductible 0 OPTIONS PPO health_benefit_plan_coverage , 30 Yes - Applies to in network providers
remaining Deductible 1500 OPTIONS PPO health_benefit_plan_coverage , 30 No - Applies to out of network providers

XML:

<deductibles>
        <eligibilityDate />
        <insuranceType />
        <timeperiod>calendar_year
        <amounttype>Deductible
        <amount>
          <amount>3000
          <currency>USD
        
        <delivery />
        <plandescription>OPTIONS PPO
        <description />
        <messages />
        <servicetypes>
          <name>health_benefit_plan_coverage
          <codex12>30
        
        <procedureId />
        <procedureIdScope />
        <coverageLevel />
        <inplannetwork>No - Applies to out of network providers
      
      <deductibles>
        <eligibilityDate />
        <insuranceType />
        <timeperiod>remaining
        <amounttype>Deductible
        <amount>
          <amount>0
          <currency>USD
        
        <delivery />
        <plandescription>OPTIONS PPO
        <description />
        <messages />
        <servicetypes>
          <name>health_benefit_plan_coverage
          <codex12>30
        
        <procedureId />
        <procedureIdScope />
        <coverageLevel />
        <inplannetwork>Yes - Applies to in network providers
      
      <deductibles>
        <eligibilityDate />
        <insuranceType />
        <timeperiod>remaining
        <amounttype>Deductible
        <amount>
          <amount>1500
          <currency>USD
        
        <delivery />
        <plandescription>OPTIONS PPO
        <description />
        <messages />
        <servicetypes>
          <name>health_benefit_plan_coverage
          <codex12>30
        
        <procedureId />
        <procedureIdScope />
        <coverageLevel />
        <inplannetwork>

No - Applies to out of network providers

What I have tried:

declare @response nvarchar(max)
declare @responseXML XML 

BEGIN TRY 

	SELECT @responseXML = (select [responseXML] from TABLE where id = 1)

END TRY 
BEGIN CATCH 
	SELECT ERROR_MESSAGE()
END CATCH
select
X.Node.query('./child::node()') As FileList,
X.Node.value('Response[1]/coverage[1]/deductibles[1]/timePeriod[1]','VARCHAR(MAX)') as TimePeriod,
X.Node.value('Response[1]/coverage[1]/deductibles[1]/amountType[1]','VARCHAR(MAX)') as AmountType,
X.Node.value('Response[1]/coverage[1]/deductibles[1]/amount[1]/amount[1]','VARCHAR(MAX)') as Amount,
X.Node.value('Response[1]/coverage[1]/deductibles[1]/timePeriod[1]','VARCHAR(MAX)') as TimePeriod,
X.Node.value('Response[1]/coverage[1]/deductibles[1]/planDescription[1]','VARCHAR(MAX)') as PlanDescription,
X.Node.value('Response[1]/coverage[1]/deductibles[1]/serviceTypes[1]/codeX12[1]','VARCHAR(MAX)') as ServiceTypesCode,
X.Node.value('Response[1]/coverage[1]/deductibles[1]/serviceTypes[1]/name[1]','VARCHAR(MAX)') as ServiceTypesName,
X.Node.value('Response[1]/coverage[1]/deductibles[1]/inPlanNetwork[1]','VARCHAR(MAX)') as inPlanNetwork,
X.Node.value('Response[1]/coverage[1]/deductibles[1]/coverageLevel[1]','VARCHAR(MAX)') as coverageLevel
FROM @responseXML.nodes('Root') X(Node)