从SQL Server中的多个表中产生XML [英] Producing XML from Multiple Tables in SQL Server

问题描述

是否可以在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提琴

这篇关于从SQL Server中的多个表中产生XML的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！