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

I have a problem about saving data from a table that is in the XML below

  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 the above data In an XML condition

The person ID 1234 has 3 rows of data with three different types (X, wise), so all these three rows should be saved in an XML datatype> different person With ID 2222, the column should be stored in the next line, it has only one type (X), so it will be only once.

Required XML Example

  & lt; Data & gt; & Lt; PersonID & gt; 1234 & lt; / PersonID & gt; & Lt; SpecifiedType & gt; & Lt; Type & gt; Xtype & lt; / Type & gt; & Lt; Name & gt; John & lt; / Name & gt; & Lt; Category & gt; ABC & lt; / Category & gt; & Lt; Price & gt; 200 & lt; / Pricing & gt; & Lt; / SpecifiedType & gt; & Lt; SpecifiedType & gt; & Lt; Type & gt; Ytype & lt; / Type & gt; & Lt; Name & gt; John & lt; / Name & gt; & Lt; Category & gt; Xyz & lt; / Category & gt; & Lt; Price & gt; 230 & lt; / Pricing & gt; & Lt; / SpecifiedType & gt; & Lt; SpecifiedType & gt; & Lt; Type & gt; Ztype & lt; / Type & gt; & Lt; Name & gt; John & lt; / Name & gt; & Lt; Category & gt; CCC & lt; / Category & gt; & Lt; Price & gt; 220 & lt; / Pricing & gt; & Lt; / SpecifiedType & gt; & Lt; / Data & gt;  

This group should be based on type, sometimes the person will have only one type of ID

I am able to generate a line of data in XML But I'm unable to store it in the format above.

You can do this by using it. There are some layers to achieve the group and structure but this is not very difficult;

  declare @t table (id int, personID int, type varchar (10), name varchar (10), category varchar (10), value int) @t value (1, 1234) , 'Xtype', 'John', 'ABC', 200), (2, 1234, 'ytype', 'john', 'xyz', 230), (3, 1234, 'ztepe', 'john', ' CCC ', 220), (4, 2222,' xtype ',' gym ',' ABC ', 200); With CT (Choose Separate Individual IDID) (Select Individual Identity, (Select Type, Name, Category, Value @ TT, where T. PSCNID = CTEner. PSCIDID for XML Path ('Specified Type') , CtyInner.PersonID = cteOuter.PersonID xml path (''), type, root ('data') from CTE CTaner  

)