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 Friday, September 04, 2015 7:37 PM

declare @t table (ID int identity(1,1),Event varchar(100), Condition varchar(100), GroupName varchar(100), Item varchar(100), Answer varchar(100))

insert into @t values ('New', 'New', 'GroupA', 'Item1', 'Y')
insert into @t values ('New', 'New','GroupA', 'Item2', 'Y')
insert into @t values ('New', 'New','GroupA', 'Item3', 'N')
                                        
insert into @t values ('New','New','GroupB', 'Item6', 'N')                                        
insert into @t values ('New','New','GroupB', 'Item10', 'Y')                                      
insert into @t values ('New','New','GroupB', 'Item30', 'N')
                                      
insert into @t values ('New','Cancelled','GroupA', 'Item1', 'N')                                
insert into @t values ('New','Cancelled','GroupA', 'Item2', 'Y')                                
insert into @t values ('New','Cancelled','GroupA', 'Item3', 'Y')
                                
insert into @t values ('New','Cancelled','GroupB', 'Item6', 'N')                                
insert into @t values ('New','Cancelled','GroupB', 'Item10', 'Y')                              
insert into @t values ('New','Cancelled','GroupB', 'Item30', 'N')


                              
insert into @t values ('Amend','New','GroupA', 'Item1', 'N')                                    
insert into @t values ('Amend','New','GroupA', 'Item2', 'Y')                                    
insert into @t values ('Amend','New','GroupA', 'Item3', 'Y')
                                    
insert into @t values ('Amend','New','GroupB', 'Item6', 'N')                                    
insert into @t values ('Amend','New','GroupB', 'Item10', 'Y')                                  
insert into @t values ('Amend','New','GroupB', 'Item30', 'Y')


SELECT GroupName AS  [@Name],
(SELECT [Event] AS [@Event],
Condition AS [@Condition],
(SELECT DISTINCT [Item] AS [@Name],
Answer AS [@Answer]
FROM @t
WHERE GroupName = t.GroupName
AND Event = t1.Event
AND Condition = t1.Condition
FOR XML PATH('Item'),TYPE) AS [*]
FROM (SELECT DISTINCT Event,Condition
FROM @t 
WHERE GroupName = t.GroupName)t1
FOR XML PATH('State'),TYPE) AS [*]
FROM (SELECT DISTINCT GroupName  FROM @t)t
FOR XML PATH('Group'),ROOT('Groups')