Databases
StackOverflow2013
Postsdbo
POProducing XML from Multiple Tables in SQL Server
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POProducing XML from Multiple Tables in SQL Server
    primarykey
    data
    text
    <p>Is it possible to generate the following XML using "FOR XML" in SQL Server</p> <pre><code> &lt;Person&gt; &lt;HomeID&gt;1&lt;/HomeID&gt; &lt;DayID&gt;1&lt;/DayID&gt; &lt;String&gt;I get up at 07:00&lt;/String&gt; &lt;String&gt;I have breakfast at 07:30&lt;/String&gt; &lt;String&gt;I go to office at 08:00&lt;/String&gt; &lt;String&gt;I have lunch at 13:00&lt;/String&gt; &lt;String&gt;I come back from office at 17:00&lt;/String&gt; &lt;String&gt;I have dinner at 19:00&lt;/String&gt; &lt;String&gt;I sleep at 21:30&lt;/String&gt; &lt;DayID&gt;2&lt;/DayID&gt; &lt;String&gt;I get up at 08:00&lt;/String&gt; &lt;String&gt;I have breakfast at 08:30&lt;/String&gt; &lt;String&gt;I have lunch at 13:00&lt;/String&gt; &lt;String&gt;I have dinner at 20:00&lt;/String&gt; &lt;String&gt;I sleep at 23:00&lt;/String&gt; &lt;/Person&gt; &lt;Person&gt; &lt;HomeID&gt;2&lt;/HomeID&gt; &lt;DayID&gt;1&lt;/DayID&gt; &lt;String&gt;I get up at 07:00&lt;/String&gt; &lt;String&gt;I have breakfast at 07:30&lt;/String&gt; &lt;String&gt;I go to office at 08:00&lt;/String&gt; &lt;String&gt;I have lunch at 13:00&lt;/String&gt; &lt;String&gt;I come back from office at 17:00&lt;/String&gt; &lt;String&gt;I have dinner at 19:00&lt;/String&gt; &lt;String&gt;I sleep at 21:30&lt;/String&gt; &lt;DayID&gt;2&lt;/DayID&gt; &lt;String&gt;I get up at 08:00&lt;/String&gt; &lt;String&gt;I have breakfast at 08:30&lt;/String&gt; &lt;String&gt;I have lunch at 13:00&lt;/String&gt; &lt;String&gt;I have dinner at 20:00&lt;/String&gt; &lt;String&gt;I sleep at 23:00&lt;/String&gt; &lt;Person&gt; </code></pre> <p>My initial attempt was very bad.</p> <pre><code> 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 </code></pre> <hr> <p>I have two tables, TB1 and TB2.</p> <p>Fields in TB1 are HomeID, DayID, TimeCode, timevalue.</p> <pre><code> 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 </code></pre> <p>Fields in TB2 are DayType, StringCode, RndString.</p> <pre><code> 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 </code></pre> <p><strong>Note:</strong> TB1.DayID=TB2.DayType and TB1.TimeCode=TB2.StringCode</p>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USSarangArd
    1. USSarangArd
    plurals
    1. PL
      singulars
      1. LTLinked
    1. PL
      singulars
      1. LTLinked
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. COThis 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. http://pastebin.com/Ub8AAwAG
      singulars
      1. POProducing XML from Multiple Tables in SQL Server
      1. USMikael Eriksson
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload