本文介绍了从SQL Server中的多个表中产生XML的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
是否可以在SQL Server中使用"FOR XML"生成以下XML
Is it possible to generate the following XML using "FOR XML" in SQL Server
<Person>
<HomeID>1</HomeID>
<DayID>1</DayID>
<String>I get up at 07:00</String>
<String>I have breakfast at 07:30</String>
<String>I go to office at 08:00</String>
<String>I have lunch at 13:00</String>
<String>I come back from office at 17:00</String>
<String>I have dinner at 19:00</String>
<String>I sleep at 21:30</String>
<DayID>2</DayID>
<String>I get up at 08:00</String>
<String>I have breakfast at 08:30</String>
<String>I have lunch at 13:00</String>
<String>I have dinner at 20:00</String>
<String>I sleep at 23:00</String>
</Person>
<Person>
<HomeID>2</HomeID>
<DayID>1</DayID>
<String>I get up at 07:00</String>
<String>I have breakfast at 07:30</String>
<String>I go to office at 08:00</String>
<String>I have lunch at 13:00</String>
<String>I come back from office at 17:00</String>
<String>I have dinner at 19:00</String>
<String>I sleep at 21:30</String>
<DayID>2</DayID>
<String>I get up at 08:00</String>
<String>I have breakfast at 08:30</String>
<String>I have lunch at 13:00</String>
<String>I have dinner at 20:00</String>
<String>I sleep at 23:00</String>
<Person>
我最初的尝试非常糟糕.
My initial attempt was very bad.
Select HomeID,
(
Select DayID,
(
SELECT TB2.RndString+' '+CAST(TB1.timevalue AS varchar(5))
FROM TB1,TB2
where TB1.DayID=TB2.DayType and TB1.TimeCode=TB2.StringCode
FOR XML PATH ('String'), TYPE
)
from TB1
for XML AUTO, TYPE
)
from TB1
for XML AUTO, ELEMENTS
我有两个表TB1和TB2.
I have two tables, TB1 and TB2.
TB1中的字段是HomeID，DayID，TimeCode，timevalue.
Fields in TB1 are HomeID, DayID, TimeCode, timevalue.
HomeID DayID TimeCode timevalue
1 1 1 07:00:00
1 1 2 07:30:00
1 1 3 08:00:00
1 1 4 13:00:00
1 1 5 17:00:00
1 1 6 19:00:00
1 1 7 21:30:00
1 2 1 08:00:00
1 2 2 08:30:00
1 2 3 13:00:00
1 2 4 20:00:00
1 2 5 23:00:00
2 1 1 08:00:00
2 1 2 08:30:00
2 1 3 09:00:00
2 1 4 13:00:00
2 1 5 18:00:00
2 1 6 20:00:00
2 1 7 22:00:00
2 2 1 09:00:00
2 2 2 10:00:00
2 2 3 13:00:00
2 2 4 19:00:00
2 2 5 22:30:00
TB2中的字段是DayType，StringCode，RndString.
Fields in TB2 are DayType, StringCode, RndString.
DayType StringCode RndString
1 1 I get up at
1 2 I have breakfast at
1 3 I go to office at
1 4 I have lunch at
1 5 I come back from office at
1 6 I have dinner at
1 7 I sleep at
2 1 I get up at
2 2 I have breakfast at
2 3 I have lunch at
2 4 I have dinner at
2 5 I sleep at
注意:TB1.DayID = TB2.DayType和TB1.TimeCode = TB2.StringCode
Note:TB1.DayID=TB2.DayType and TB1.TimeCode=TB2.StringCode
推荐答案
我认为所需的XML并不是您的最佳选择，因此我将为您提出另一种结构.
I don't think the XML you want is optimal for you so I will suggest another structure for you.
select T1_1.HomeID as [@HomeID],
(
select T1_2.DayID as [@ID],
(
select T2.RndString+' '+left(T1_3.TimeValue, 5) as '*'
from TB1 as T1_3
inner join TB2 as T2
on T1_3.DayID = T2.DayType and
T1_3.TimeCode = T2.StringCode
where T1_2.HomeID = T1_3.HomeID and
T1_2.DayID = T1_3.DayID
order by T2.StringCode
for xml path('String'), type
)
from TB1 as T1_2
where T1_2.HomeID = T1_1.HomeID
group by T1_2.DayID,
T1_2.HomeID
order by T1_2.DayID
for xml path('Day'), type
)
from TB1 as T1_1
group by T1_1.HomeID
order by T1_1.HomeID
for xml path('Person')
结果:
<Person HomeID="1">
<Day ID="1">
<String>I get up at 07:00</String>
<String>I have breakfast at 07:30</String>
<String>I go to office at 08:00</String>
<String>I have lunch at 13:00</String>
<String>I come back from office at 17:00</String>
<String>I have dinner at 19:00</String>
<String>I sleep at 21:30</String>
</Day>
<Day ID="2">
<String>I get up at 08:00</String>
<String>I have breakfast at 08:30</String>
<String>I have lunch at 13:00</String>
<String>I have dinner at 20:00</String>
<String>I sleep at 23:00</String>
</Day>
</Person>
<Person HomeID="2">
<Day ID="1">
<String>I get up at 08:00</String>
<String>I have breakfast at 08:30</String>
<String>I go to office at 09:00</String>
<String>I have lunch at 13:00</String>
<String>I come back from office at 18:00</String>
<String>I have dinner at 20:00</String>
<String>I sleep at 22:00</String>
</Day>
<Day ID="2">
<String>I get up at 09:00</String>
<String>I have breakfast at 10:00</String>
<String>I have lunch at 13:00</String>
<String>I have dinner at 19:00</String>
<String>I sleep at 22:30</String>
</Day>
</Person>
这篇关于从SQL Server中的多个表中产生XML的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！