解析XML SQL并制作表格 [英] Parsing XML SQL and making a table

问题描述

以下是代码



我已经用SQL解析了它



TimePeriod | AmountType |量| TimePeriod | PlanDescription | ServiceTypesCode | ServiceTypesName | inPlanNetwork coverageLevel

calendar_year | Deductible | 3000 | calendar_year | OPTIONS PPO | 30 | health_benefit_plan_coverage | No - 适用于网络外提供商





但我想创建一个表和堆栈他们在一张桌子上，比如拨打一个现场通话时间段，金额类型，金额



timePeriod amountType amount planDescription serviceTypes inPlanNetwork

calendar_year Deductible 3000选项PPO health_benefit_plan_coverage，30否 - 适用于网络外提供者

剩余免赔额0选项PPO health_benefit_plan_coverage，30是 - 适用于网络提供商

剩余免赔额1500选项PPO health_benefit_plan_coverage ，30否 - 适用于网络外提供商



XML：

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)

推荐答案

为什么不使用 DataTable.ReadXML（）。



此时，将数据放入SQL Server数据库会更容易。有关信息，请点击此处：



谷歌 [ ^ ]

Why don't you just use DataTable.ReadXML().

At that point, it would be MUCH easier to get the data into a SQL Server database. For info, on that, go here:

Google[^]

这篇关于解析XML SQL并制作表格的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！