SQL Server - 对于XML路径(嵌套元素,多条记录)
问题描述:
努力编写可以在XML中生成嵌套元素的查询。谁能帮SQL Server - 对于XML路径(嵌套元素,多条记录)
输入:
SELECT EmpName AS [Name],
EmpSalary AS [WageAmount],
Add1 AS [Address1],
Add2 AS [Address2],
Mobile AS [Mobile]
FROM Employee
FOR XML PATH
表数据(2条记录地址不同emoployee存在)
Conrad Jagger 12345 London UK 9191919191
Conrad Jagger 12345 Coventry UK 9191919191
预期输出:(这额外的信息作为嵌套元素)
<Employee>
<Name>Conrad</Name>
<WageAmount>10000</WageAmount>
<AdditionalInfo>
<PersonAddress>
<Address1>London</Address1>
<Address2>UK</Address2>
<Mobile>919191919191</Mobile>
</PersonAddress>
<PersonAddress>
<Address1>Coventry</Address1> --->Second address
<Address2>UK</Address2>
<Mobile>919191919191</Mobile>
</PersonAddress>
</AdditionalInfo>
</Employee>
如果你有单条记录,我不知道该怎么办e这项工作为多个记录即时获取
SELECT EmpName AS [Name],
EmpSalary AS [WageAmount],
Add1 AS [AdditionalInfo/Address1],
Add2 AS [AdditionalInfo/Address2],
Mobile AS [AdditionalInfo/Mobile]
FROM Employee
FOR XML PATH ('Employee')
答
我希望有一个uniqe id列在表中。由于您尚未提供该列,因此我将加入EmpName列作为例子。
试试这个
SELECT EmpName AS [Name],
EmpSalary AS [WageAmount],
(SELECT
Add1 AS [PersonAddress/Address1],
Add2 AS [PersonAddress/Address2],
Mobile AS [PersonAddress/Mobile] FROM Employee B WHERE EmpName = A.EmpName
FOR XML PATH('AdditionalInfo'),type)
FROM Employee A
GROUP BY EmpName,EmpSalary
FOR XML PATH ('Employee')