SQL Server stored procedure - Table to XML

Question

I created an XML Object from an SQL table but still need to insert a tag and hardcode a value for each of my columns.

Here is my query and result

SELECT 
EmployeeName, RequestStatus

FROM K2.SmartBoxData.Akin_LeaveRequest_Header_SMO
WHERE ID =32


FOR XML PATH ('Message')

    <Message>
        <EmployeeName>Developer</EmployeeName>
        <RequestStatus>Line Manager Approval</RequestStatus>
    </Message>

Here is my desired result

<Message>
  <tag>
     <hardcode> my value </hardcode>
     <EmployeeName>Developer</EmployeeName>
  </tag> 

  <tag>
     <hardcode> my value 2 </hardcode>
     <RequestStatus>Line Manager Approval</RequestStatus>
  </tag>
</Message>

Answer 1

You can use nested FOR XML subqueries to do this. Make sure to add ,TYPE to the nested FOR XML otherwise it will try escape it.

Do not specify column names for the subqueries

SELECT 
  (
    SELECT
      hardcode = ' my value ',
      lrh.EmployeeName
    FOR XML PATH('tag'), TYPE
  ),
  (
    SELECT
      hardcode = ' my value 2 ',
      lrh.RequestStatus
    FOR XML PATH('tag'), TYPE
  )
FROM SmartBoxData.Akin_LeaveRequest_Header_SMO lrh
WHERE ID =32
FOR XML PATH ('Message'), TYPE;

Alternatively specify column names, but an empty PATH

SELECT 
  tag = (
    SELECT
      hardcode = ' my value ',
      lrh.EmployeeName
    FOR XML PATH(''), TYPE
  ),
  tag = (
    SELECT
      hardcode = ' my value 2 ',
      lrh.RequestStatus
    FOR XML PATH(''), TYPE
  )
FROM SmartBoxData.Akin_LeaveRequest_Header_SMO lrh
WHERE ID =32
FOR XML PATH ('Message'), TYPE;

db<>fiddle