-- Here's the sample data. I was able to get one Metadata line. I was hoping this would be easy to do using the XML functionality of SQL server. I was intending to put this in a stored procedure that returns XML. The stored procedure is consumed by an application that would write the XML to a file, etc... If this gets too complicated in T-SQL then the XML formatting could be moved off to the application.

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>

--,'PROGRAM' 'Metadata/@name'

--,'true' 'Metadata/@global'

--,Level Metadata

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

FROM

@Request

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

)

SELECT @ExportXML AS ExportXML

--Trying to add additional Metadata rows results in

--Msg 6852, Level 16, State 1, Line 18

--Attribute-centric column 'Metadata/@name' must not come after a non-attribute-centric sibling in XML hierarchy in FOR XML PATH.