See if this helps
DECLARE @t TABLE(ID INT,SecID VARCHAR(10),CountryID VARCHAR(10),Currency VARCHAR(3))
INSERT INTO @t(ID,SecID,CountryID,Currency)
SELECT 1,'S42DWER','111344','USD' UNION ALL
SELECT 2,'2TG24GG','251464','EUR';
SELECT 'Schedule2' AS "@ref",
(SELECT 'yes' AS "@key",
(SELECT 'mdrm' AS "@type",
'ID' AS "text()"
FOR XML PATH('rs_id'),TYPE),
ID AS "itemValue"
FOR XML PATH('reportItem'),TYPE),
(SELECT
(SELECT 'mdrm' AS "@type",
'SecID' AS "text()"
FOR XML PATH('rs_id'),TYPE),
SecID AS "itemValue"
FOR XML PATH('reportItem'),TYPE),
(SELECT
(SELECT 'mdrm' AS "@type",
'CountryID' AS "text()"
FOR XML PATH('rs_id'),TYPE),
CountryID AS "itemValue"
FOR XML PATH('reportItem'),TYPE),
(SELECT
(SELECT 'mdrm' AS "@type",
'Currency' AS "text()"
FOR XML PATH('rs_id'),TYPE),
Currency AS "itemValue"
FOR XML PATH('reportItem'),TYPE)
FROM @t
FOR XML PATH('itemGroup');
____________________________________________________
Deja View - The strange feeling that somewhere, sometime you've optimised this query before
How to get the best help on a forum
http://www.sqlservercentral.com/articles/Best+Practices/61537