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>
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;
Note that <Monthly></Monthly>
and <Monthly />
are semantically equivalent.