Producing XML from Multiple Tables in SQL Server

Question

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

I have two tables, TB1 and TB2.

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

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

Note: TB1.DayID=TB2.DayType and TB1.TimeCode=TB2.StringCode

This is not the XML you are looking for. The XML structure and the indenting you have used does not match. Here is a better view of what your XML actually looks like. pastebin.com/Ub8AAwAG — Mikael Eriksson
– Mikael Eriksson, Commented May 22, 2013 at 9:54

Mikael Eriksson · Accepted Answer · 2013-05-22 10:06:37Z

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')

Result:

<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 Fiddle

Collectives™ on Stack Overflow

Producing XML from Multiple Tables in SQL Server

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related