Dynamic Xml Parsing TSQL

Question

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

Do you have to return choices as columns rather than rows? You can fairly easily return each choice as a new row, if you could deal with that output you'd be able to deal with any number of choices. Failing that you'd need to use dynamic SQL to do this — GarethD
– GarethD, Commented Oct 17, 2022 at 15:47
The fiddle works, but doesn't give the desired result does it? It creates a new row for each choice, but the question asks for a new column. I think a new row is a better approach, but as I say, I am just not sure it answers the question being asked? — GarethD
– GarethD, Commented Oct 17, 2022 at 16:32
Just as an FYI I think the more robust way of doing this would be using (text())[1] rather than . (I've put the better way in my answer). Using this fiddle should highlight the difference, if a choice node contained further xml rather than a value then text() returns null, whereas .. will concatenate the text from all the child nodes. — GarethD
– GarethD, Commented Oct 17, 2022 at 17:01

GarethD · Accepted Answer · 2022-10-17 16:58:13Z

1

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

answered Oct 17, 2022 at 16:58

GarethD

70k11 gold badges90 silver badges130 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Dynamic Xml Parsing TSQL

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related