sql-serverxmlt-sqlxqueryfor-xml-path

Create XML nodes for values in SQL


I'm having an issue to create XML nodes. Help is much appreciated !

This is a sample code

declare @tbl as table 
(
    employeeName    nvarchar(50),
    payFrequency    nvarchar(50)
)

insert into @tbl
select 'John', 'Monthly'
union
select 'Carl', 'Biweekly'

select 
employeeName        AS 'Company/Employee',
payFrequency        AS 'Company/PayFrequency'
from @tbl
for xml path ('employees'), root('paySchedule')

above code creates this output:

<paySchedule>
  <employees>
    <Company>
      <Employee>John</Employee>
      <PayFrequency>Monthly</PayFrequency>
    </Company>
  </employees>
  <employees>
    <Company>
      <Employee>Carl</Employee>
      <PayFrequency>Biweekly</PayFrequency>
    </Company>
  </employees>
</paySchedule>

I want to get the "paymentFrequency" values as a node. Is there a way to do this?

<paySchedule>
  <employees>
    <Company>
      <Employee>John</Employee>
      <PayFrequency>
          <Monthly/>
      </PayFrequency>
    </Company>
  </employees>
  <employees>
    <Company>
      <Employee>Carl</Employee>
      <PayFrequency>
          <Biweekly/>
      </PayFrequency>
    </Company>
  </employees>
</paySchedule>

Solution

  • You can use a CASE conditional for each possibility, returning an empty string when you want that node, and null otherwise.

    SELECT
      t.employeeName AS [Company/Employee],
      CASE WHEN t.payFrequency = 'Monthly' THEN '' END AS [Company/PayFrequency/Monthly],
      CASE WHEN t.payFrequency = 'Biweekly' THEN '' END AS [Company/PayFrequency/Biweekly]
    FROM @tbl t
    FOR XML PATH('employees'), ROOT('paySchedule'), TYPE;
    

    You can do this also in a nested FOR XML.

    SELECT
      t.employeeName AS [Company/Employee],
      (
          SELECT
            CASE WHEN t.payFrequency = 'Monthly' THEN '' END AS Monthly,
            CASE WHEN t.payFrequency = 'Biweekly' THEN '' END AS Biweekly
          FOR XML PATH(''), TYPE
      ) AS [Company/PayFrequency]
    FROM @tbl t
    FOR XML PATH('employees'), ROOT('paySchedule'), TYPE;
    

    db<>fiddle

    Note that <Monthly></Monthly> and <Monthly /> are semantically equivalent.