Create XML nodes for values in SQL-CodePudding

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>

CodePudding user response：

Please try the following solution.

It is using XQuery's FLWOR expression to compose the desired XML.

SQL

-- DDL and sample data population, start
DECLARE @tbl as table (employeeName NVARCHAR(50), payFrequency NVARCHAR(50));
INSERT INTO @tbl VALUES
('John', 'Monthly'),
('Carl', 'Biweekly');
-- DDL and sample data population, end

SELECT (
    SELECT * FROM @tbl
    FOR XML PATH('r'), TYPE, ROOT('root')
).query('<paySchedule>
{
    for $r in /root/r
    return <employees>
        <Company>
        <Employee>{data($r/employeeName)}</Employee>
        <PayFrequency>
                {
                    if ($r/payFrequency/text()="Monthly") then <Monthly/>
                    else <Biweekly/>
                }
            </PayFrequency>
        </Company>
    </employees>
}
</paySchedule>');

Output

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

CodePudding user response：

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.