Hi,

 Could someone please tell me the FOR XML Statement I should use to convert data in the below table to generate xml in the given format? Can it be done with a single statement?

Event | Condition   | Group         |Item                 |Answer
=======================================
New      New    GroupA         Item1                    Y
New      New    GroupA         Item2                    Y
New      New    GroupA         Item3                    N
New      New    GroupB         Item6                    N
New      New    GroupB         Item10                  Y
New      New    GroupB         Item30                  N
    
    
New   Cancelled   GroupA        Item1                 N
New   Cancelled   GroupA        Item2                 Y
New   Cancelled   GroupA        Item3                 Y
New   Cancelled   GroupB        Item6                 N
New   Cancelled   GroupB        Item10               Y
New   Cancelled   GroupB        Item30               N
    
Amend   New       GroupA         Item1                N
Amend   New       GroupA         Item2                Y
Amend   New       GroupA         Item3                Y
Amend   New       GroupB         Item6                N
Amend   New       GroupB         Item10              Y
Amend   New       GroupB         Item30              Y

<Groups>
  <Group Name="GroupA">
    <State Event="New" Condition="New">
            <Item Name="Item1" Answer="Y"/>
            <Item Name="Item2" Answer="Y"/>
            <Item Name="Item3" Answer="N"/>     
    </State>
    <State Event="New" Condition="Cancelled">
             <Item Name="Item1" Answer="N"/>
             <Item Name="Item2" Answer="Y"/>
             <Item Name="Item3" Answer="Y"/>     
    </State>
    <State Event="Amend" Condition="New">
             <Item Name="Item1" Answer="N"/>
             <Item Name="Item2" Answer="Y"/>
             <Item Name="Item3" Answer="Y"/>     
    </State>
  <Group>
  <Group Name="GroupB">
      <State Event="New" Condition="New">
           <Item Name="Item6" Answer="N"/>
           <Item Name="Item10" Answer="Y"/>
           <Item Name="Item30" Answer="N"/>     
      </State>
      <State Event="New" Condition="Cancelled">
             <Item Name="Item6" Answer="N"/>
            <Item Name="Item10" Answer="Y"/>
            <Item Name="Item30" Answer="N"/>     
      </State>
      <State Event="Amend" Condition="New">
             <Item Name="Item6" Answer="N"/>
             <Item Name="Item10" Answer="Y"/>
             <Item Name="Item30" Answer="Y"/>     
      </State>
  <Group>
 </Groups>

Thanks

Zest4Quest

• Edited by Zest4Quest 11 hours 32 minutes ago

You would have to use several subqueries. Take a look at this thread and the blog referenced in it to figure this out. If it will be too complex, then post your data as declare @t table with insert statements so we can create the query with ease.

https://social.msdn.microsoft.com/Forums/en-US/68ec1a45-6bab-4402-8dd6-1a0a824e4796/use-for-xml-in-sql-to-return-multiple-child-records-within-each-parent-record?forum=transactsql