title: Query complex XML in SQL Server 2008 R2
body: <p>I am trying to parse through an XML in SQL Server 2008 R2 and I am having some issues. I am trying to parse through the items for each parent node, but I can't seem to get the data accurate. The XML data is below:</p>
  
  <pre><code> &lt;Project xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="Project.xsd" Name="Job"&gt;
    &lt;Design&gt;
      &lt;Catalog&gt;
        &lt;Identification&gt;
          &lt;Name&gt;Group1&lt;/Name&gt;
        &lt;/Identification&gt;
        &lt;FeatureSet ID="IDFS-1" Type="Style"&gt;
          &lt;Description&gt;Style1&lt;/Description&gt;
        &lt;/FeatureSet&gt;
        &lt;Item ID="IDI-1-2730"&gt;
          &lt;UserCode&gt;Item1&lt;/UserCode&gt;
          &lt;LineItemNumber&gt;1&lt;/LineItemNumber&gt;
        &lt;/Item&gt;
        &lt;Item ID="IDI-1-1595"&gt;
          &lt;UserCode&gt;Item2&lt;/UserCode&gt;
          &lt;LineItemNumber&gt;2&lt;/LineItemNumber&gt;
        &lt;/Item&gt;
      &lt;/Catalog&gt;
      &lt;Catalog&gt;
        &lt;Identification&gt;
            &lt;Name&gt;Group2&lt;/Name&gt;
        &lt;/Identification&gt;
        &lt;FeatureSet ID="IDFS-1" Type="Style"&gt;
          &lt;Description&gt;Style2&lt;/Description&gt;
        &lt;/FeatureSet&gt;
        &lt;Item ID="IDI-1-2730"&gt;
          &lt;UserCode&gt;Item3&lt;/UserCode&gt;
          &lt;LineItemNumber&gt;1&lt;/LineItemNumber&gt;
        &lt;/Item&gt;
      &lt;/Catalog&gt;
    &lt;/Design&gt;
  &lt;/Project&gt;
  </code></pre>
  
  <p>This is the SQL that I have so far, but doesn't seem to be working.</p>
  
  <pre><code>select
      x.d.query('./UserCode').value('.','char(40)') as UserCode
      ,x.d.query('./LineItemNumber').value('.','char(40)') as lineitemnumber
      ,i.d.query('./Name').value('.','nvarchar(200)') as [Group]
      ,u.d.query('./Description').value('.','nvarchar(200)') as Style
  from 
      @xml.nodes('/Project/Design/Catalog/Item') as x(d)
  outer apply 
      @xml.nodes('/Project/Design/Catalog/Identification') as i(d)
  outer apply 
      @xml.nodes('/Project/Design/Catalog/FeatureSet') as u(d)
  </code></pre>
  
  <p>Obviously I am setting <code>@xml</code> to the xml data above with the XML data type.</p>
  
  <p>Below are the results that I am getting:</p>
  
  <pre><code>UserCode    lineitemnumber  Group   Style
  Item1       1               Group1  Style1
  Item1       1               Group1  Style2
  Item1       1               Group2  Style1
  Item1       1               Group2  Style2
  Item2       2               Group1  Style1
  Item2       2               Group1  Style2
  Item2       2               Group2  Style1
  Item2       2               Group2  Style2
  Item3       1               Group1  Style1
  Item3       1               Group1  Style2
  Item3       1               Group2  Style1
  Item3       1               Group2  Style2
  </code></pre>
  
  <p>What I am looking for is:</p>
  
  <pre><code>UserCode    lineitemnumber  Group   Style
  Item1       1               Group1  Style1
  Item2       2               Group1  Style1
  Item3       1               Group2  Style2
  </code></pre>
  
  <p>Every catalog will only have one Identification/Description and will also only have one Featureset/Description</p>