See if this helps


WITH Data AS (
SELECT 'Your user id' AS UserName,
   'Your password' AS Password,
   '25/01/2017' AS TransDate,
   '09:00:00' AS TransTime,
   'test1234' AS TransRef,
   'N' AS DocsReqdInd
)
SELECT UserName AS 'Header_UserName/@Val',
   Password AS 'Header_Password/@Val',
   TransDate AS 'Header_TransDate/@Val',
   TransTime AS 'Header_TransTime/@Val',
   TransRef AS 'Header_TransRef/@Val',
   DocsReqdInd AS 'Header_DocsReqdInd/@Val'
FROM Data
FOR XML PATH('Header');

____________________________________________________

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