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