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

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

Hi Naomi,

  Please find the script below. Thanks a lot.

declare @t table (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')

Below is an example using a CTE and XML AUTO:

WITH
	  [Group] AS (
		SELECT DISTINCT GroupName FROM @t
	)
	, [State] AS (
		SELECT DISTINCT GroupName, Event, Condition FROM @t
	)
	, [Item] AS (
		SELECT GroupName, Event, Condition, Item, Answer FROM @t
	)
SELECT
	  [Group].GroupName AS Name
	, [State].Event
	, [State].Condition
	, [Item].Item AS Name
	, [Item].Answer
FROM [Group]
JOIN [State] ON [State].GroupName = [Group].GroupName
JOIN [Item] ON [Item].GroupName = [State].GroupName
	AND [Item].Event = [State].Event
	AND [Item].Condition = [State].Condition
FOR XML AUTO, ROOT('Groups');

Try

declare @t table (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')

;WITH
	  [Group] AS (
		SELECT DISTINCT GroupName FROM @t
	)
	, [State] AS (
		SELECT DISTINCT GroupName, Event, Condition FROM @t
	)
	, [Item] AS (
		SELECT GroupName, Event, Condition, Item, Answer FROM @t
	)
SELECT
	  [Group].GroupName AS Name, (select
	 [State].Event as [Event]
	, [State].Condition as [Condition]
	, [Item].Item AS Name
	, [Item].Answer as Answer
    FROM [State] 
JOIN [Item] ON [Item].GroupName = [State].GroupName
	AND [Item].Event = [State].Event
	AND [Item].Condition = [State].Condition
    WHERE [State].GroupName = [Group].GroupName
    FOR XML AUTO, type) 
FROM [Group]

FOR XML AUTO, ROOT('Groups');