Hello.
Есть вот такая хранимая процедура. При ее выполнении все элементы с тегами 2 и 3 оказаываюстя в последнем родительским элементе (тег 1), а идущих перед ним элементах ничего кроме атрибутов нет, т.е.

<GET_AGRS />
<GET_AGRS />
<GET_AGRS>
<PROD />
<PROD />
<PROD />
<PROD />
<AGR />
<AGR />
<AGR />
<AGR />
</GET_AGRS>

Как с этим бороться?

@cp1 int, @cp2 int
AS
(
SELECT 1 as tag,
0 as parent,

a.cp_id1 as 'GET_AGRS!1!cp_id1',
a.cp_id2 as 'GET_AGRS!1!cp_id2',
a.base_rate as 'GET_AGRS!1!base_rate',
l.cur_id as 'GET_AGRS!1!cur_id',
NULL as 'PROD!2!prod_id',
NULL as 'PROD!2!prod_name',
NULL as 'AGR!3!agr_name',
NULL as 'AGR!3!date_in'

FROM Agrs as a, links as l, Prods as p
WHERE ((a.cp_id1 = @cp1 AND a.cp_id2 = @cp2) OR (a.cp_id1 = @cp2 AND a.cp_id2 = @cp1)) AND
(l.cp_id1 = a.cp_id1 AND l.cp_id2 = a.cp_id2 AND l.agr_id = a.agr_id) AND
p.id = l.prod_id
)
UNION ALL
(
SELECT 2 as tag,
1 as parent,
NULL,
NULL,
NULL,
NULL,
l.prod_id,
RTRIM (p.name),
NULL,
NULL

FROM Agrs as a, links as l, Prods as p
WHERE ((a.cp_id1 = @cp1 AND a.cp_id2 = @cp2) OR (a.cp_id1 = @cp2 AND a.cp_id2 = @cp1)) AND
(l.cp_id1 = a.cp_id1 AND l.cp_id2 = a.cp_id2 AND l.agr_id = a.agr_id) AND
p.id = l.prod_id
)
UNION ALL
(
SELECT 3 as tag,
1 as parent,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
RTRIM (a.agr_name),
a.date_in

FROM Agrs as a, links as l, Prods as p
WHERE ((a.cp_id1 = @cp1 AND a.cp_id2 = @cp2) OR (a.cp_id1 = @cp2 AND a.cp_id2 = @cp1)) AND
(l.cp_id1 = a.cp_id1 AND l.cp_id2 = a.cp_id2 AND l.agr_id = a.agr_id) AND
p.id = l.prod_id
)
FOR XML EXPLICIT