If I understood what you want, then you can use sub queries that create XML. Notice that when you do it you have to use the directive type (If you'll play with it a bit, you'll see why). Bellow is code that does it:

declare @Request table

(

[RequestType] varchar(10)

,[CompanyName] varchar(50)

,[CompanyID] int

,[Country] varchar(2)

,[PostalCode] varchar(9)

--

,[Program] char(1)

,[Level] int

,[Promotion] varchar(10)

);

insert into @Request values ('NEW', 'Main St. Bistro', 1234567890, 'US', '60602', 'A', 2, 'HAPPYMEAL');

DECLARE @ExportXML XML

SET @ExportXML =

(

SELECT

[RequestType]

,[CompanyName]

,[CompanyID]

,[Country]

,[PostalCode]

--<Metadata name="PROGRAM" global="true">A</Metadata>

,'PROGRAM' 'Metadata/@name'

,'true' 'Metadata/@global'

,Program Metadata

-- Need the Level and Promotion as additonal Metada rows like this...

--<Metadata name="LEVEL" global="true">2</Metadata>

,(select 'LEVEL' as 'Metadata/@name'

,'true' 'Metadata/@global' for xml path (''), type)

--<Metadata name=”PROMOTION”>HAPPYMEAL</Metadata>

,(select 'PROMOTION' as 'Metadata/@name' for xml path(''), type)

FROM

@Request

FOR XML PATH(''), ROOT('Request')

)

select @ExportXML

Adi