title: Get all the Subchild values using XQUERY-SQL
body: <p>I'm attempting to create an xquery expression that will return all the subchild node values. Now I get only the first subchild under each child. </p>
  
  <p>I have the following XML:</p>
  
  <pre><code>&lt;order&gt;
      &lt;child1&gt;
        &lt;subchild id="S1"&gt;
          &lt;name type="primary"&gt;
            &lt;first&gt;NAOMI&lt;/first&gt;
            &lt;middle /&gt;
            &lt;last&gt;ADAMS&lt;/last&gt;
            &lt;suffix /&gt;
          &lt;/name&gt;
        &lt;/subchild&gt;
        &lt;subchild id="S2"&gt;
          &lt;name type="primary"&gt;
            &lt;first&gt;TOVER&lt;/first&gt;
            &lt;middle /&gt;
            &lt;last&gt;DALI&lt;/last&gt;
            &lt;suffix /&gt;
          &lt;/name&gt;
        &lt;/subchild&gt;
      &lt;/child1&gt;
      &lt;child2&gt;
        &lt;subchild id="V1"&gt;
          &lt;year&gt;2002&lt;/year&gt;
          &lt;make&gt;PONTI&lt;/make&gt;
          &lt;model&gt;AZTEK&lt;/model&gt;
          &lt;vin&gt;3G7DA03E32S597676&lt;/vin&gt;
        &lt;/subchild&gt;
        &lt;subchild id="V2"&gt;
          &lt;year&gt;2003&lt;/year&gt;
          &lt;make&gt;HONDA&lt;/make&gt;
          &lt;model&gt;CIVIC&lt;/model&gt;
          &lt;vin&gt;2H94DA03E80S1538&lt;/vin&gt;
        &lt;/subchild&gt;
      &lt;/child2&gt;
      &lt;child3&gt;
        &lt;subchild id="A1"&gt;
          &lt;house&gt;7741&lt;/house&gt;
          &lt;street1&gt;SAINT BERNARD ST&lt;/street1&gt;
          &lt;apartment /&gt;
          &lt;city&gt;PLAYA DEL REY&lt;/city&gt;
          &lt;state&gt;CA&lt;/state&gt;
          &lt;postalcode&gt;90293&lt;/postalcode&gt;
        &lt;/subchild&gt;
        &lt;subchild id="A2"&gt;
          &lt;house&gt;2371&lt;/house&gt;
          &lt;street1&gt;HANNUM DR&lt;/street1&gt;
          &lt;apartment /&gt;
          &lt;city&gt;MARINA DEL REY&lt;/city&gt;
          &lt;state&gt;CA&lt;/state&gt;
          &lt;postalcode&gt;90293&lt;/postalcode&gt;
        &lt;/subchild&gt;
      &lt;/child3&gt;
    &lt;/order&gt;
  </code></pre>
  
  <p>My result should look like :</p>
  
  <pre><code>  FirstName LastName    HouseNumber StreetName         City         SState  Zip    Year     Model   Make    VIN
     NAOMI     ADAMS        7741   SAINT BERNARD ST   PLAYA DEL REY     CA    90293   2002    PONTI   AZTEK   G7DA03E32S597676
     TOVER     DALI         2371    HANNUM DR         MARINA DEL REY    CA    90024   2003    HONDA   CIVIC   2H94DA03E80S1538
  </code></pre>
  
  <p>My query :</p>
  
  <pre><code>SELECT 
     FirstName, LastName, HouseNumber, StreetName, City, SState, Zip, Year, Model, Make, VIN
  FROM 
     xmlTable 
  OUTER APPLY 
  (  
      SELECT  
         tbl.col.value('(child1/subchild/name/first)[1]','varchar(20)') AS FirstName,
         tbl.col.value('(child1/subchild/name/last)[1]','varchar(20)') AS LastName,
         tbl.col.value('(child1/subchild/name/middle)[1]','varchar(20)') AS MiddleName,
         tbl.col.value('(child3/subchild/house)[1]','varchar(20)') AS HouseNumber,
         tbl.col.value('(child3/subchild/street1)[1]','varchar(20)') AS StreetName,
         tbl.col.value('(child3/subchild/city)[1]','varchar(20)') AS City,
         tbl.col.value('(child3/subchild/state)[1]','varchar(20)') AS SState,
         tbl.col.value('(child3/subchild/postalcode)[1]','varchar(20)') AS Zip,
         tbl.col.value('(child2/subchild/model_year)[1]','varchar(20)') AS Year,
         tbl.col.value('(child2/subchild/model)[1]','varchar(20)') AS Model,
         tbl.col.value('(child2/subchild/make)[1]','varchar(20)') AS Make,
         tbl.col.value('(child2/subchild/vin)[1]','varchar(20)') AS VIN
      FROM 
         xmldocument.nodes('//order') AS tbl(col) 
     )  Y 
  </code></pre>
  
  <p>Thanks</p>