tsql - SQL & XML - 减少重复数据
问题描述
寻找一种使用FOR XML
.
这是架构和数据:
create table #area (StateId int, State varchar(10), Population int)
go
insert into #area values (1, 'AZ', 10000)
go
create table #person (Id int, FirstName varchar(10), LastName varchar(10), StateFk int)
go
insert into #person values (1, 'Joe', 'Smith', 1), (2, 'Jane', 'Doe', 1)
go
select a.State as '@State'
, a.Population as '@Population'
, p.FirstName as 'Person/FirstName'
, p.LastName as 'Person/LastName'
from #area a
inner join #person p on a.StateId = p.StateFk
where a.Id = 1
for xml path('Record'), root ('RecordSet')
go
结果如下:
<RecordSet>
<Record State="AZ" Population="10000">
<Person>
<FirstName>Joe</FirstName>
<LastName>Smith</LastName>
</Person>
</Record>
<Record State="AZ" Population="10000">
<Person>
<FirstName>Jane</FirstName>
<LastName>Doe</LastName>
</Person>
</Record>
</RecordSet>
但由于#area
数据是静态的,我想减少重复数据。使用FOR XML
,我怎么能写 ot 所以结果看起来像这样:
<RecordSet>
<State>AZ</State>
<Population>10000</Population>
<Person>
<FirstName>Joe</FirstName>
<LastName>Smith</LastName>
</Person>
<Person>
<FirstName>Jane</FirstName>
<LastName>Doe</LastName>
</Person>
</RecordSet>
或这个:
<RecordSet State="AZ" Population="1000">
<Person>
<FirstName>Joe</FirstName>
<LastName>Smith</LastName>
</Person>
<Person>
<FirstName>Jane</FirstName>
<LastName>Doe</LastName>
</Person>
</RecordSet>
解决方案
nm,用子查询弄明白了
select a.State as '@State'
, a.Population as '@Population'
, (select p.FirstName as 'Person/FirstName'
, p.LastName as 'Person/LastName'
from #person p
where p.stateFk = a.StateId
for xml path('')
) as 'Persons'
from #area a
for xml path('Record'), root ('RecordSet')
go
推荐阅读
- matplotlib - 在 matplotlib 中找到部分可行的解决方案
- three.js - Threejs在每个盒子面上覆盖单个图像而不重复
- mongoose - 如何在 Mongoose 中填充以下格式的数据集合
- php - 如何禁用 Apache 上 php 文件的服务器端缓存?
- python-3.x - 如何在python中使用套接字发送响应代码301?
- html - 将子元素设置为父元素的 100% 宽度
- wso2 - 无法检索应用程序并且无法为 API 创建订阅
- python - 图像直方图:IndexError:索引 4 超出轴 1 的范围,大小为 4
- angular - 如何动态添加到 ionic 应用程序的 CDN 链接
- azure - Tomcat 的 Azure SSO 配置