Dynamic Xml Parsing TSQL-CodePudding

Suppose, we have below xml

declare @x xml ='<auditElement>
  <field id="12" type="5" name="test" formatstring="">
    <choice>45</choice>
  </field>
  <field id="13" type="5" name="test2" formatstring="">
 <choice>100</choice>
  </field>
   <field id="74" type="8" name="somestring" formatstring="">
    <choice>14</choice>
    <choice>16</choice>
  </field>
</auditElement>

To Get data from choice,i can write like below

select
 field.value('@id','nvarchar(50)') as id,
 field.value('@type','nvarchar(50)') as type,
 field.value('@name','nvarchar(100)') as name,
 field.value('@formatstring','nvarchar(50)') as formatstring,

 field.value('choice[1]','nvarchar(50)') as setChoice,
 field.value('choice[2]','nvarchar(50)') as setChoice2

from @x.nodes('/auditElement/field') as XMLtable1(field)

But what if there is one more choice..like below

declare @x xml ='<auditElement>
      <field id="12" type="5" name="test" formatstring="">
        <choice>45</choice>
      </field>
      <field id="13" type="5" name="test2" formatstring="">
     <choice>100</choice>
      </field>
       <field id="74" type="8" name="somestring" formatstring="">
        <choice>14</choice>
        <choice>16</choice>
       <choice>1656</choice>
      </field>
    </auditElement>

I can get data ,by adding one more field in my select..

select
 field.value('@id','nvarchar(50)') as id,
 field.value('@type','nvarchar(50)') as type,
 field.value('@name','nvarchar(100)') as name,
 field.value('@formatstring','nvarchar(50)') as formatstring,

 field.value('choice[1]','nvarchar(50)') as setChoice,
 field.value('choice[2]','nvarchar(50)') as setChoice2
 field.value('choice[3]','nvarchar(50)') as setChoice3
from @x.nodes('/auditElement/field') as XMLtable1(field)

Is there any other way,other than adding field.value('choice[3]','nvarchar(50)') as setChoice3 whenever we get a new choice

CodePudding user response：

One option would be to return additional rows instead of additional columns for each choice:

SELECT  id = XMLtable1.field.value('@id', 'nvarchar(50)'),
        type = XMLtable1.field.value('@type', 'nvarchar(50)'),
        name = XMLtable1.field.value('@name', 'nvarchar(100)'),
        formatstring = XMLtable1.field.value('@formatstring', 'nvarchar(50)'),
        Choice = c.choice.value('(text())[1]', 'int'),
        ChoiceNo = ROW_NUMBER() OVER(PARTITION BY XMLtable1.field.value('@id', 'nvarchar(50)') ORDER BY c.choice.value('(text())[1]', 'int'))
FROM    @x.nodes('/auditElement/field') AS XMLtable1(field)
        OUTER APPLY XMLtable1.field.nodes('choice') AS c(Choice);

Example on db<>fiddle