sql - 如何使用sql将属性添加到xml中的根节点

Question

我的表结构如下:

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

我想将这个表格数据转换为 XML，最终输出如下:

<Employees 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>    
  <Employee EmpID="3">
    <EmpName>David</EmpName>
    <BirthDate>11/09/1982</BirthDate>
  </Employee>
</Employees>

Answer 1

这应该可以满足您的需求:

CREATE TABLE dbo.Employees(EmpID INT,EmpName VARCHAR(100),DeptName VARCHAR(100),BirthDate DATE);
INSERT INTO dbo.Employees VALUES
 (1,'Test1','Dep1',{d'1991-01-01'})
,(2,'Test2','Dep1',{d'1992-02-02'})
,(3,'Test3','Dep2',{d'1993-03-03'});
GO

WITH DistinctDepartments AS
(
    SELECT DISTINCT DeptName FROM dbo.Employees
)
SELECT DeptName AS [@DeptName]
      ,(
        SELECT  
         innerEmp.[EmpID] AS [@EmpId]
        ,innerEmp.[EmpName]
        ,innerEmp.[BirthDate] 
        FROM [dbo].Employees AS innerEmp
        WHERE innerEmp.DeptName=DistinctDepartments.DeptName
        FOR XML PATH('Employee'),TYPE
       )
FROM DistinctDepartments
FOR XML PATH('Employees'),ROOT('Departments');

结果

<Departments>
  <Employees DeptName="Dep1">
    <Employee EmpId="1">
      <EmpName>Test1</EmpName>
      <BirthDate>1991-01-01</BirthDate>
    </Employee>
    <Employee EmpId="2">
      <EmpName>Test2</EmpName>
      <BirthDate>1992-02-02</BirthDate>
    </Employee>
  </Employees>
  <Employees DeptName="Dep2">
    <Employee EmpId="3">
      <EmpName>Test3</EmpName>
      <BirthDate>1993-03-03</BirthDate>
    </Employee>
  </Employees>
</Departments>