问题描述
我有一个如下表结构:
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')