title: Select values in SQL Server from XML
body: <p>I have this XML:</p>
  
  <pre><code> &lt;SearchResponse&gt;
      &lt;Data&gt;
          &lt;Information&gt;
              &lt;Code&gt;Code 1&lt;/Code&gt;
              &lt;Options&gt;
                  &lt;Option&gt;
                      &lt;TID&gt;1&lt;/TID&gt;
                      &lt;Rooms&gt;
                          &lt;ReturnRoom&gt;
                              &lt;RoomId&gt;1&lt;/RoomId&gt;
                              &lt;Description&gt;Single&lt;/Description&gt;
                          &lt;/ReturnRoom&gt;
                          &lt;ReturnRoom&gt;
                              &lt;RoomId&gt;2&lt;/RoomId&gt;
                              &lt;Description&gt;Standard&lt;/Description&gt;
                          &lt;/ReturnRoom&gt;
                      &lt;/Rooms&gt;
                  &lt;/Option&gt;
                  &lt;Option&gt;
                      &lt;TID&gt;2&lt;/TID&gt;
                      &lt;Rooms&gt;
                          &lt;ReturnRoom&gt;
                              &lt;RoomId&gt;1&lt;/RoomId&gt;
                              &lt;Description&gt;Single&lt;/Description&gt;
                          &lt;/ReturnRoom&gt;
                      &lt;/Rooms&gt;
                  &lt;/Option&gt;
              &lt;/Options&gt;
          &lt;/Information&gt;      
      &lt;/Data&gt;
  &lt;/SearchResponse&gt;
  </code></pre>
  
  <p>How I can get </p>
  
  <pre><code>Code      Name     TID  RoomId    Description 
  ---------------------------------------------------
  Code 1    Hotel 1   1  1;2     Single;Standard
  Code 1    Hotel 1   2   1      Single
  </code></pre>