title: SQL Server from XML parameter to table - working with optional child nodes
body: <p>On SQL Server 2008 R2, I am trying to read XML value as table. </p>
  
  <p>So far, I am here : </p>
  
  <pre><code>DECLARE @XMLValue AS XML;
  SET @XMLValue = '&lt;SearchQuery&gt;
      &lt;ResortID&gt;1453&lt;/ResortID&gt;
      &lt;CheckInDate&gt;2011-10-27&lt;/CheckInDate&gt;
      &lt;CheckOutDate&gt;2011-11-04&lt;/CheckOutDate&gt;
      &lt;Room&gt;
          &lt;NumberOfADT&gt;2&lt;/NumberOfADT&gt;
          &lt;CHD&gt;
              &lt;Age&gt;10&lt;/Age&gt;
          &lt;/CHD&gt;
          &lt;CHD&gt;
              &lt;Age&gt;12&lt;/Age&gt;
          &lt;/CHD&gt;
      &lt;/Room&gt;
      &lt;Room&gt;
          &lt;NumberOfADT&gt;1&lt;/NumberOfADT&gt;
      &lt;/Room&gt;
      &lt;Room&gt;
          &lt;NumberOfADT&gt;1&lt;/NumberOfADT&gt;
          &lt;CHD&gt;
              &lt;Age&gt;7&lt;/Age&gt;
          &lt;/CHD&gt;
      &lt;/Room&gt;
  &lt;/SearchQuery&gt;';
  
  SELECT 
      Room.value('(NumberOfADT)[1]', 'INT') AS NumberOfADT
  FROM @XMLValue.nodes('/SearchQuery/Room') AS SearchQuery(Room);
  </code></pre>
  
  <p>As you can see, <code>Room</code> node sometimes get <code>CHD</code> child nodes but sometimes don't.</p>
  
  <p>Assume that I am getting this XML value as a Stored Procedure parameter. So, I need to work with the values in order to query my database tables. What would be the best way to read this XML parameter entirely?</p>
  
  <p><strong>EDIT</strong></p>
  
  <p>I think I need to express what I am expecting in return here. The below script code is for the table what I need here : </p>
  
  <pre><code>DECLARE @table AS TABLE(
      ResorrtID INT,
      CheckInDate DATE,
      CheckOutDate DATE,
      NumberOfADT INT,
      CHDCount INT,
      CHDAges NVARCHAR(100)
  );
  </code></pre>
  
  <p>For the XML value I have provide above, the below Insert t-sql is suitable : </p>
  
  <pre><code>INSERT INTO @table VALUES(1453, '2011-10-27', '2011-11-04', 2, 2, '10;12');
  INSERT INTO @table VALUES(1453, '2011-10-27', '2011-11-04', 1, 0, NULL);
  INSERT INTO @table VALUES(1453, '2011-10-27', '2011-11-04', 1, 1, '7');
  </code></pre>
  
  <p><code>CHDCount</code> is for the number of <code>CHD</code> nodes under <code>Room</code> node. Also, how many <code>Room</code> node I have, that many table row I am having here.</p>
  
  <p>As for how it should look, see the below picture : </p>
  
  <p><img src="https://i.stack.imgur.com/nwyxt.png" alt="enter image description here"></p>
  
  <blockquote>
    <p>Actually, this code is for hotel reservation search query. So, I need
    to work with these values I got from XML parameter to query my tables
    and return available rooms. I am telling this because maybe it helps
    you guys to see it through. I am not looking for a complete code for
    room reservation system. That would be so selfish.</p>
  </blockquote>