T SQL для XML PATH Сгруппировать как атрибут или элемент
Я работал над T-SQL FOR XML с режимом PATH, чтобы создать иерархию на основе группы по полю. Ниже мой запрос и вывод. Пожалуйста, помогите мне с вашими ценными предложениями. Спасибо. Хорошего дня!!!
select e.department_id AS [@DepartmentID],
d.DEPARTMENT_NAME AS [@DepartmentName],
e.EMPLOYEE_ID AS [EmployeeInfo/EmployeeID],
e.FIRST_NAME AS [EmployeeInfo/FirstName],
e.LAST_NAME AS [EmployeeInfo/LastName]
from employees e
JOIN departments d
ON e.department_id = d.department_id
GROUP BY e.department_id,d.DEPARTMENT_NAME,
e.EMPLOYEE_ID,e.FIRST_NAME,e.LAST_NAME
FOR XML PATH ('Department'), ROOT ('Departments')
Выход:
<Departments>
<Department DepartmentID="10">
<EmployeeInfo>
<EmployeeID>111</EmployeeID>
<FirstName>John</FirstName>
<LastName>Chen</LastName>
</EmployeeInfo>
</Department>
<Department DepartmentID="10">
<EmployeeInfo>
<EmployeeID>201</EmployeeID>
<FirstName>steven</FirstName>
<LastName>Whalen</LastName>
</EmployeeInfo>
</Department>
<Department DepartmentID="30">
<EmployeeInfo>
<EmployeeID>105</EmployeeID>
<FirstName>ANIRUDH</FirstName>
<LastName>RAMESH</LastName>
</EmployeeInfo>
</Department>
<Department DepartmentID="30">
<EmployeeInfo>
<EmployeeID>115</EmployeeID>
<FirstName>Den</FirstName>
<LastName>Raphaely</LastName>
</EmployeeInfo>
</Department>
<Departments>
Желаемый результат:
<Departments>
<Department DepartmentID="10">
<EmployeeInfo>
<EmployeeID>111</EmployeeID>
<FirstName>John</FirstName>
<LastName>Chen</LastName>
</EmployeeInfo>
<EmployeeInfo>
<EmployeeID>201</EmployeeID>
<FirstName>steven</FirstName>
<LastName>Whalen</LastName>
</EmployeeInfo>
</Department>
<Department DepartmentID="30">
<EmployeeInfo>
<EmployeeID>105</EmployeeID>
<FirstName>ANIRUDH</FirstName>
<LastName>RAMESH</LastName>
</EmployeeInfo>
<EmployeeInfo>
<EmployeeID>115</EmployeeID>
<FirstName>Den</FirstName>
<LastName>Raphaely</LastName>
</EmployeeInfo>
</Department>
<Departments>
2 ответа
Решение
Вы можете использовать TYPE для вложенного XML
SELECT
d.department_id AS [@DepartmentID],
d.DEPARTMENT_NAME AS [@DepartmentName],
(
SELECT
e.EMPLOYEE_ID AS EmployeeID,
e.FIRST_NAME AS [FirstName],
e.LAST_NAME AS [LastName]
FROM employees e
WHERE e.department_id = d.department_id
FOR XML PATH ('EmployeeInfo'), TYPE
)
FROM departments d
FOR XML PATH ('Department'), ROOT ('Departments')
Не уверен, сможем ли мы ответить на наш собственный вопрос. Я и один из моих коллег нашли другое решение для этого запроса, но с режимом AUTO.
select d.DEPARTMENT_ID as [DepartmentID],e.EMPLOYEE_ID as
[EmployeeID],e.first_name as [EmployeeName],e.SALARY as [Salary]
from [departments] d
inner join [employees] e
on e.DEPARTMENT_ID = d.DEPARTMENT_ID
order by 1,4
for xml AUTO, Root ('Employees'), ELEMENTS