sql server - SQL save data into XML column with grouping and nesting -

i having issue regarding saving data table below in 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 save above data in xml condition.

data of personid 1234 having 3 rows of data 3 different types(x,yz) these 3 rows of data should saved in 1 xml datatype, > column different personid 2222 should store in next row, has 1 type(x) have 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 should group, personid have 1 type.

i able generate single row of data xml unable store in above format.

you can using for xml. in order grouping , structure want involves few layers it's not 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)  ; cte (     select distinct personid @t ) select     (select         personid,         (select              type,              name,              category,              value                       @t t                       t.personid = cteinner.personid          xml path('specifiedtype'), type )              cte cteinner             cteinner.personid = cteouter.personid     xml path(''), type, root('data') )      cte cteouter