如何将SQL Server数据表数据转换为指定结构的XML？

数据表内容 #

以上为SQL Server数据表中的数据。

目标XML格式 #

需要生成如下结构的XML输出（已修正原示例中的语法错误）：

<Employees>
  <Employee username="jdoe" email="jd@gmail.com" firstname="John" lastname="Doe">
    <Role role="acct"/>
    <Timezone timezone="est"/>
    <Job job="j001" />
    <Job job="j002" />
  </Employee>
</Employees>

SQL实现方案 #

使用SQL Server的FOR XML PATH语法可以实现该需求，通过子查询构造嵌套节点，同时对用户信息去重避免重复生成Employee节点：

-- 替换YourTableName为实际数据表名
SELECT 
    DISTINCT t1.username AS '@username',
    t1.email AS '@email',
    t1.firstname AS '@firstname',
    t1.lastname AS '@lastname',
    -- 生成Role子节点
    (SELECT t2.role AS '@role' 
     FROM YourTableName t2 
     WHERE t2.username = t1.username 
     FOR XML PATH('Role'), TYPE),
    -- 生成Timezone子节点
    (SELECT t2.timezone AS '@timezone' 
     FROM YourTableName t2 
     WHERE t2.username = t1.username 
     FOR XML PATH('Timezone'), TYPE),
    -- 生成多个Job子节点
    (SELECT t2.jobcode AS '@job' 
     FROM YourTableName t2 
     WHERE t2.username = t1.username 
     FOR XML PATH('Job'), TYPE)
FROM YourTableName t1
FOR XML PATH('Employee'), ROOT('Employees')

说明 #

1. DISTINCT用于确保同一用户只生成一个Employee节点；
2. 子查询通过FOR XML PATH('节点名'), TYPE生成嵌套的子节点，TYPE参数保证子节点以XML类型而非字符串形式嵌入；
3. @符号用于指定属性，比如username AS '@username'会生成Employee节点的username属性。

内容的提问来源于stack exchange，提问作者user1879961