为SQL中的值创建XML节点

Question

我有一个问题要创建XML节点。帮助是非常感谢的！

这是一个示例代码

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')

上面的代码创建了这个输出：

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

我希望将"paymentFrequency“值作为节点。有办法这样做吗？

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

Answer 1

您可以对每种可能性使用一个CASE条件，在需要该节点时返回一个空字符串，否则返回空字符串。

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;

您也可以在嵌套的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;

注意，<Monthly></Monthly>和<Monthly />在语义上是等价的。

Answer 2

请尝试以下解决方案。

它使用XQuery的FLWOR表达式来组成所需的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>');

输出

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