SQL save data into XML column with grouping and nesting

Question

I am having a issue regarding saving a data from a table which is like below in to xml

ID  personID  Type   Name   category  value
1     1234    xtype   John     abc      200
2     1234    ytype   John     xyz      230
3     1234    ztype   John     ccc      220
4     2222    xtype   Jim      abc      200

I need to save above data in an xml condition.

Data of personId 1234 is having 3 rows of data with three different types(x,yz) so all these three rows of data should be saved in one xml datatype, > column with different personID 2222 should store in next row, it only has one type(x) so it will have only once.

required xml example

<Data>
<PersonID>1234</PersonID>
<SpecifiedType>
<Type>xtype</Type>
<Name>John</Name>
<category>abc</category>
<value>200</Value>
</SpecifiedType>
<SpecifiedType>
<Type>Ytype</Type>
<Name>John</Name>
<category>xyz</category>
<value>230</Value>
</SpecifiedType>
 <SpecifiedType>
 <Type>Ztype</Type>
 <Name>John</Name>
 <category>ccc</category>
 <value>220</Value>
 </SpecifiedType>
 </Data>

Depending upon types it should group, sometimes personID will have only one Type.

I am able to generate a single row of data into xml but unable to store it in above format.

Answer 1

You can do this using FOR XML. In order to get the grouping and structure you want involves a few layers but it's not too difficult;

declare @t table (ID int, PersonID int, Type varchar(10), Name varchar(10), category varchar(10), value int)
insert @t values
    (1, 1234, 'xtype', 'John', 'abc', 200),
    (2, 1234, 'ytype', 'John', 'xyz', 230),
    (3, 1234, 'ztype', 'John', 'ccc', 220),
    (4, 2222, 'xtype', 'Jim', 'abc', 200)

; with cte
as (
    select distinct PersonID from @t
)
select
    (select
        PersonID,
        (select
             Type,
             Name,
             category,
             value
         from
             @t t
         where
             t.PersonID = cteInner.PersonID
         for xml path('SpecifiedType'), type ) 
    from
        cte cteInner
    where
        cteInner.PersonID = cteOuter.PersonID
    for xml path(''), type, root('data') )
from 
    cte cteOuter