Parsing XML with t-sql for multiple sub values

Question

I have XML documents being sent that I have no control over the structure of. I'm trying to normalize the data as best as I can and am running into an issue when I have a node that has multiple values for a child.

Ideally I'd split the "ARM_Groups" and "Other_Name" into separate tables and reference the main intervention, however there are no "id" fields to use. I need to be able to separate the data, but maintain the relationship. I have another section that has nodes within nodes that could be repeated and is more complicated, but I think I can figure that out if I can get the basic grasp with this part.

Here's a sample of the data that I'm working with:

  <intervention>
    <intervention_type>Drug</intervention_type>
    <intervention_name>Anti-hyperglycemic Agents</intervention_name>
    <description>Multiple drugs including</description>
    <arm_group_label>Glycemia Trial: intensive control</arm_group_label>
    <arm_group_label>Glycemia Trial: standard control</arm_group_label>
    <other_name>glimepiride (Amaryl)</other_name>
    <other_name>metformin (Glucophage)</other_name>
    <other_name>repaglinide (Gluconorm, Prandin)</other_name>
    <other_name>rosiglitazone (Avandia)</other_name>
    <other_name>pioglitazone (Actos)</other_name>
    <other_name>human regular insulin (Novolin ge Toronto)</other_name>
    <other_name>human NPH (Novolin N)</other_name>
    <other_name>human mixed (Novolin 70/30)</other_name>
    <other_name>human isophane (Novolin ge NPH)</other_name>
    <other_name>human 30/70 (Novolin ge 30/70)</other_name>
    <other_name>insulin aspart (NovoRapid, NovoLog)</other_name>
    <other_name>insulin detemir (Levemir)</other_name>
    <other_name>human regular insulin (Novolin R)</other_name>
    <other_name>insulin glargine (Lantus)</other_name>
    <other_name>Acarbose</other_name>
  </intervention>
  <intervention>
    <intervention_type>Drug</intervention_type>
    <intervention_name>Anti-hypertensive Agents</intervention_name>
    <description>Multiple anti-hypertensive agents</description>
    <arm_group_label>BP Trial: intensive control</arm_group_label>
    <arm_group_label>BP Trial: standard control</arm_group_label>
    <other_name>benazepril (Lotensin, Zestril, Altace)</other_name>
    <other_name>chlorthalidone (Thalitone)</other_name>
    <other_name>metoprolol (Toprol XL)</other_name>
    <other_name>diltiazem (Tiazac)</other_name>
    <other_name>plendil (Felodipine)</other_name>
    <other_name>terazosin (Hytrin)</other_name>
    <other_name>candesartan (Atacand)</other_name>
    <other_name>valsartan (Diovan)</other_name>
    <other_name>furosemide</other_name>
    <other_name>reserpine</other_name>
    <other_name>hydralazine</other_name>
    <other_name>carvedilol (Coreg)</other_name>
    <other_name>triamterene / hydrochlorothiazide (Dyazide)</other_name>
    <other_name>metoprolol / hydrochlorothiazide(Lopressor HCT)</other_name>
    <other_name>benazepril / hydrochlorothiazide (Lotensin HCT)</other_name>
    <other_name>lisinopril / hydrochlorothiazide (Zestoretic)</other_name>
    <other_name>candesartan / hydrochlorothiazide (Atacand HCT)</other_name>
    <other_name>valsartan / hydrochlorothiazide (Diovan HCT)</other_name>
    <other_name>amlodipine / benazepril (Lotrel)</other_name>
  </intervention>
  <intervention>
    <intervention_type>Drug</intervention_type>
    <intervention_name>Blinded fenofibrate or placebo plus simvastatin</intervention_name>
    <description>Double blind administration</description>
    <arm_group_label>Lipid Trial: fenofibrate</arm_group_label>
    <arm_group_label>Lipid Trial: placebo</arm_group_label>
    <other_name>fenofibrate (Tricor)</other_name>
  </intervention>

Answer 1

If they have the same parent and you want to group them by corresponding value i.e. intervention_type in this example, you can create a temp table and just select them separately:

  declare @doc xml
  set @doc = '<intervention>
    <intervention_type>Drug</intervention_type>
    <intervention_name>Anti-hyperglycemic Agents</intervention_name>
    <description>Multiple drugs including</description>
    <arm_group_label>Glycemia Trial: intensive control</arm_group_label>
    <arm_group_label>Glycemia Trial: standard control</arm_group_label>
    <other_name>glimepiride (Amaryl)</other_name>
    <other_name>metformin (Glucophage)</other_name>
    <other_name>repaglinide (Gluconorm, Prandin)</other_name>
    <other_name>rosiglitazone (Avandia)</other_name>
    <other_name>pioglitazone (Actos)</other_name>
    <other_name>human regular insulin (Novolin ge Toronto)</other_name>
    <other_name>human NPH (Novolin N)</other_name>
    <other_name>human mixed (Novolin 70/30)</other_name>
    <other_name>human isophane (Novolin ge NPH)</other_name>
    <other_name>human 30/70 (Novolin ge 30/70)</other_name>
    <other_name>insulin aspart (NovoRapid, NovoLog)</other_name>
    <other_name>insulin detemir (Levemir)</other_name>
    <other_name>human regular insulin (Novolin R)</other_name>
    <other_name>insulin glargine (Lantus)</other_name>
    <other_name>Acarbose</other_name>
  </intervention>
'


select DENSE_RANK () OVER (ORDER BY b.value('.','varchar(max)')) AS ID
,b.value('(intervention_type)[1]','varchar(max)') as intervention_type
,x.value('.','varchar(max)') as arm_group_label
, y.value('.','varchar(max)') as other_name
into #temp
from @doc.nodes('/intervention') as A(b)
cross apply b.nodes('/intervention/arm_group_label') as N(x)
cross apply b.nodes('/intervention/other_name') as M(y)

    select ID,arm_group_name INTO table1 from #temp

select ID,other_name INTO table2 from #temp

Answer 2

As I understand, you need to identify your nodes for creating foreign keys. You can do it like this:

declare @xml xml = N'
<intervention>
    <intervention_type>Drug</intervention_type>
    <intervention_name>Anti-hyperglycemic Agents</intervention_name>
    <description>Multiple drugs including</description>
    <arm_group_label>Glycemia Trial: intensive control</arm_group_label>
    <arm_group_label>Glycemia Trial: standard control</arm_group_label>
    <other_name>glimepiride (Amaryl)</other_name>
    <other_name>metformin (Glucophage)</other_name>
    <other_name>repaglinide (Gluconorm, Prandin)</other_name>
    <other_name>rosiglitazone (Avandia)</other_name>
    <other_name>pioglitazone (Actos)</other_name>
    <other_name>human regular insulin (Novolin ge Toronto)</other_name>
    <other_name>human NPH (Novolin N)</other_name>
    <other_name>human mixed (Novolin 70/30)</other_name>
    <other_name>human isophane (Novolin ge NPH)</other_name>
    <other_name>human 30/70 (Novolin ge 30/70)</other_name>
    <other_name>insulin aspart (NovoRapid, NovoLog)</other_name>
    <other_name>insulin detemir (Levemir)</other_name>
    <other_name>human regular insulin (Novolin R)</other_name>
    <other_name>insulin glargine (Lantus)</other_name>
    <other_name>Acarbose</other_name>
  </intervention>
  <intervention>
    <intervention_type>Drug</intervention_type>
    <intervention_name>Anti-hypertensive Agents</intervention_name>
    <description>Multiple anti-hypertensive agents</description>
    <arm_group_label>BP Trial: intensive control</arm_group_label>
    <arm_group_label>BP Trial: standard control</arm_group_label>
    <other_name>benazepril (Lotensin, Zestril, Altace)</other_name>
    <other_name>chlorthalidone (Thalitone)</other_name>
    <other_name>metoprolol (Toprol XL)</other_name>
    <other_name>diltiazem (Tiazac)</other_name>
    <other_name>plendil (Felodipine)</other_name>
    <other_name>terazosin (Hytrin)</other_name>
    <other_name>candesartan (Atacand)</other_name>
    <other_name>valsartan (Diovan)</other_name>
    <other_name>furosemide</other_name>
    <other_name>reserpine</other_name>
    <other_name>hydralazine</other_name>
    <other_name>carvedilol (Coreg)</other_name>
    <other_name>triamterene / hydrochlorothiazide (Dyazide)</other_name>
    <other_name>metoprolol / hydrochlorothiazide(Lopressor HCT)</other_name>
    <other_name>benazepril / hydrochlorothiazide (Lotensin HCT)</other_name>
    <other_name>lisinopril / hydrochlorothiazide (Zestoretic)</other_name>
    <other_name>candesartan / hydrochlorothiazide (Atacand HCT)</other_name>
    <other_name>valsartan / hydrochlorothiazide (Diovan HCT)</other_name>
    <other_name>amlodipine / benazepril (Lotrel)</other_name>
  </intervention>
  <intervention>
    <intervention_type>Drug</intervention_type>
    <intervention_name>Blinded fenofibrate or placebo plus simvastatin</intervention_name>
    <description>Double blind administration</description>
    <arm_group_label>Lipid Trial: fenofibrate</arm_group_label>
    <arm_group_label>Lipid Trial: placebo</arm_group_label>
    <other_name>fenofibrate (Tricor)</other_name>
  </intervention>'
SELECT 
    t.value('.','nvarchar(max)'),
    t.value('for $a in .. return 1+count($a/../*[. << $a])', 'int') AS intervention_id
FROM 
    @xml.nodes('/intervention/arm_group_label') as t(t)

You will get unique id for each intervention and then, you can query arm_group_label and other_name and join them with the same intervention_id.