
-----------------------------------------------------------------------------
-- Requete 1
-----------------------------------------------------------------------------
USE TEST_XML

SELECT
R.id_region as regcode,
R.intitule as regint,
(
SELECT 
	C1.id_centre as centre_code,
	C1.intitule as centre_int
FROM CENTRE C1
WHERE C1.id_centre = '00001'
UNION
SELECT 
	C2.id_centre as centre_code,
	C2.intitule as centre_int
FROM CENTRE C2
WHERE C2.id_centre = '00002'
ORDER BY centre_code
FOR XML AUTO
) as XML_centre
FROM REGION R 
-----------------------------------------------------------------------------
-- Requete 2
-----------------------------------------------------------------------------
USE TEST_XML

SELECT 
	C1.id_centre as centre_code,
	C1.intitule as centre_int
FROM CENTRE C1
WHERE C1.id_centre = '00001'
UNION
SELECT 
	C2.id_centre as centre_code,
	C2.intitule as centre_int
FROM CENTRE C2
WHERE C2.id_centre = '00002'
ORDER BY centre_code
FOR XML AUTO
-----------------------------------------------------------------------------