SQL Server - select for xml

create table tq84_tab (
  id  integer,
  txt varchar(10)
);

insert into tq84_tab values (5, 'five' );
insert into tq84_tab values (8, 'eight');
insert into tq84_tab values (2, 'two'  );
insert into tq84_tab values (4, 'four' );
insert into tq84_tab values (9, 'nine' );

select * from tq84_tab for xml auto;

-- The query produces:
--
--    <tq84_tab id="5" txt="five" />
--    <tq84_tab id="8" txt="eight" />
--    <tq84_tab id="2" txt="two" />
--    <tq84_tab id="4" txt="four" />
--    <tq84_tab id="9" txt="nine" />
--

The path('RRR') specifies the XML tag with which each returned record is enclosed:

create table tq84_tab (
  id  integer,
  txt varchar(10)
);

insert into tq84_tab values (5, 'five' );
insert into tq84_tab values (8, 'eight');
insert into tq84_tab values (2, 'two'  );
insert into tq84_tab values (4, 'four' );
insert into tq84_tab values (9, 'nine' );

select * from tq84_tab for xml path('RRR')

--  The select statement produces
--
--     <RRR>
--       <id>5</id>
--       <txt>five</txt>
--     </RRR>
--     <RRR>
--       <id>8</id>
--       <txt>eight</txt>
--     </RRR>
--     <RRR>
--       <id>2</id>
--       <txt>two</txt>
--     </RRR>
--     <RRR>
--       <id>4</id>
--       <txt>four</txt>
--     </RRR>
--     <RRR>
--       <id>9</id>
--       <txt>nine</txt>
--     </RRR>

for xml path is useful to concatenate the »children« of a parent table.

drop table tq84_b;
drop table tq84_a;

create table tq84_a (
  id integer primary key,
  tx varchar(10)
);

insert into tq84_a values (1, 'one'  );
insert into tq84_a values (2, 'two'  );
insert into tq84_a values (3, 'three');

create table tq84_b (
  id_a integer not null references tq84_a,
  itm  varchar(10)
);

insert into tq84_b values (1, 'foo' );
insert into tq84_b values (1, 'bar' );
insert into tq84_b values (1, 'baz' );

insert into tq84_b values (2, 'eggs');
insert into tq84_b values (2, 'why' );

--
--  Use a subquery and xml for to concatenate the children
--  for a particular row of the parent table;
--
select distinct
    a.tx,
   (select ' - ' + b.itm
      from tq84_b b
	 where b.id_a = a.id
	  for xml path (''))  concat_itm
from
    tq84_a a                  join
	tq84_b b on a.id = b.id_a;
--
-- Unfortunately, the concatenation is prefixed with a ` - `:
-- 
--    two	 - eggs - why
--    one	 - foo - bar - baz
-- 

--
-- Remove the first ' - ' of the concatenation with stuff:
--
select distinct
    a.tx,
	stuff (
   (select ' - ' + b.itm
      from tq84_b b
	 where b.id_a = a.id
	  for xml path ('')) ,
	 1, 3, ''  -- Replace characters 1 through 3 with nothing (that is: remove them)
	) concat_itm
from
    tq84_a a                  join
	tq84_b b on a.id = b.id_a;

--
--
--
-- one	foo - bar - baz
-- two	eggs - why