Databases
StackOverflow2013
Postsdbo
PO
Body
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PO
    text
    copied!<p>I don't think the XML you want is optimal for you so I will suggest another structure for you.</p> <pre class="lang-sql prettyprint-override"><code>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') </code></pre> <p>Result:</p> <pre class="lang-xml prettyprint-override"><code>&lt;Person HomeID="1"&gt; &lt;Day ID="1"&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;/Day&gt; &lt;Day ID="2"&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;/Day&gt; &lt;/Person&gt; &lt;Person HomeID="2"&gt; &lt;Day ID="1"&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 go to office at 09: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 18:00&lt;/String&gt; &lt;String&gt;I have dinner at 20:00&lt;/String&gt; &lt;String&gt;I sleep at 22:00&lt;/String&gt; &lt;/Day&gt; &lt;Day ID="2"&gt; &lt;String&gt;I get up at 09:00&lt;/String&gt; &lt;String&gt;I have breakfast at 10:00&lt;/String&gt; &lt;String&gt;I have lunch at 13:00&lt;/String&gt; &lt;String&gt;I have dinner at 19:00&lt;/String&gt; &lt;String&gt;I sleep at 22:30&lt;/String&gt; &lt;/Day&gt; &lt;/Person&gt; </code></pre> <p><a href="http://sqlfiddle.com/#!3/0b0dd/2" rel="nofollow">SQL Fiddle</a></p>
 

Querying!

 
Guidance
An individual column

Larger individual text columns get their own page to allow for proper reading.

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