sql-serverxmlt-sqlsql-server-2016select-for-xml

TSQL: Return elements of a group as XML


I try to aggregate values per group into one XML. I am on SQL Server 2016.

My raw-data looks like this (here strictly stiped down to the problem. My real values are not generic.):

create table #data (group_id int, value1 char(10), value2 char(10), value3 char(10));

insert #data values 
(1,'a1', 'b1', 'c1'),
(1,'a2', 'b2', 'c2'),
(1,'a3', 'b3', 'c3'),
(2,'a4', 'b4', 'c4');

I am looking for an aggregate function that would return one XML (per group)

select 
  group_id,
  **my_XML_aggregation** as [XML-values]
from #data
group by group_id

The expected result XML for the first group (group_id = 1) should look like (names of the elements are not relevant to the problem)

<group>
   <row>
     <value1>a1<value1>
     <value2>b1<value1>
     <value3>c1<value1>
  </row>
   <row>
     <value1>a2<value1>
     <value2>b2<value1>
     <value3>c2<value1>
  </row>
   <row>
     <value1>a3<value1>
     <value2>b3<value1>
     <value3>c3<value1>
  </row>
</group>

I know how to aggregate a pattern-separated string. This would not do the job. Even to put the pattern-aggregated string into one XML element is no alternative. I am looking for the structured information within the XML.


Solution

  • Try like this:

    drop table if exists #data ;
    
    create table #data (group_id int, value1 varchar(10), value2 varchar(10), value3 varchar(10));
    
    insert #data values 
    (1,'a1', 'b1', 'c1'),
    (1,'a2', 'b2', 'c2'),
    (1,'a3', 'b3', 'c3'),
    (2,'a4', 'b4', 'c4');
    
    with groups as
    (
      select distinct group_id from #data 
    )
    select group_id,
           (
             select value1, value2, value3
             from #data
             where group_id = groups.group_id
             for xml path, root('group'), type
           ) group_doc
    from groups 
    

    outputs

    group_id    group_doc
    ----------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
    1           <group><row><value1>a1</value1><value2>b1</value2><value3>c1</value3></row><row><value1>a2</value1><value2>b2</value2><value3>c2</value3></row><row><value1>a3</value1><value2>b3</value2><value3>c3</value3></row></group>
    2           <group><row><value1>a4</value1><value2>b4</value2><value3>c4</value3></row></group>