如何使用具有多个子节点的sql将表数据转换为xml格式

问题描述

我有一个如下表结构:

SELECT  
    [EmpID], [EmpName],
    [DeptName],
    [BirthDate] 
FROM
    [dbo].[Employees]

我想将此表数据转换为 XML，最终输出将如下所示:

I want to convert this table data into XML and the final output will be like below:

<Employees>
    <Department DeptName="ABC"> 
        <Employee EmpID="1">
            <EmpName>Davolio</EmpName>
            <BirthDate>10/12/1989</BirthDate>
        </Employee>
        <Employee EmpID="2">
            <EmpName>Andrew</EmpName>    
            <BirthDate>05/02/1985</BirthDate>
        </Employee>
    </Department>
    <Department DeptName="DEF"> 
        <Employee EmpID="3">
            <EmpName>David</EmpName>
            <BirthDate>11/09/1982</BirthDate>
        </Employee> 
    </Department>`enter code here
</Employees>

推荐答案

试试这个

SELECT  [DeptName] 
        ,(  SELECT  [EmpID],
                    [EmpName],
                    [BirthDate] 
            FROM @table E
            WHERE   E.DeptName = D.DeptName
            FOR XML PATH ('Employee'),TYPE
        )
FROM    @table D
GROUP BY [DEPTNAME]
FOR XML PATH ('Department'),type,ROOT('Employees')