title: SQL XQuery and Cross Apply problems...too many rows in results
body: <p>I have a table of contracts.  There are several columns and finally an XML column representing the whole contract.  Inside the contract are projects (1 or more) and inside each project are 1 or more lines.  I need to be able to select all of the lines and the projects they are on, but I'm getting odd results right now with my sample query.</p>
  
  <p><strong>Here is a sample of the XML:</strong></p>
  
  <pre><code>&lt;ZEstimateContract xmlns="http://schemas.datacontract.org/2004/07/Zeller.Gp" xmlns:i="http://www.w3.org/2001/XMLSchema-instance" xmlns:z="http://schemas.microsoft.com/2003/10/Serialization/" z:Id="i1"&gt;
    &lt;Name xmlns="http://schemas.datacontract.org/2004/07/Zynergy"&gt;QGPET0000000218&lt;/Name&gt;
    &lt;_projects&gt;
      &lt;ZEstimateProject z:Id="i9"&gt;
        &lt;Name xmlns="http://schemas.datacontract.org/2004/07/Zynergy"&gt;&amp;lt;BOM1&amp;gt;&lt;/Name&gt;
        &lt;Parent i:nil="true" /&gt;
        &lt;Quantity&gt;1&lt;/Quantity&gt;
        &lt;_lines&gt;
          &lt;ZEstimateLine z:Id="i40"&gt;
            &lt;Name xmlns="http://schemas.datacontract.org/2004/07/Zynergy"&gt;0.080 Aluminum 2ft x 4ft&lt;/Name&gt;
            &lt;_lines /&gt;
          &lt;/ZEstimateLine&gt;
        &lt;/_lines&gt;
        &lt;_projects /&gt;
      &lt;/ZEstimateProject&gt;
      &lt;ZEstimateProject z:Id="i97"&gt;
        &lt;Name xmlns="http://schemas.datacontract.org/2004/07/Zynergy"&gt;&amp;lt;BOM2&amp;gt;&lt;/Name&gt;
        &lt;Parent i:nil="true" /&gt;
        &lt;Quantity&gt;1&lt;/Quantity&gt;      
        &lt;_lines&gt;
          &lt;ZEstimateLine z:Id="i113"&gt;          
            &lt;Name xmlns="http://schemas.datacontract.org/2004/07/Zynergy"&gt;#8X8SPOOL&lt;/Name&gt;
          &lt;/ZEstimateLine&gt;
        &lt;_projects /&gt;
      &lt;/ZEstimateProject&gt;
    &lt;/_projects&gt;
  &lt;/ZEstimateContract&gt;
  </code></pre>
  
  <p><strong>And here is my sample query:</strong></p>
  
  <pre><code>WITH XMLNAMESPACES('http://schemas.datacontract.org/2004/07/Zeller.Gp' AS ZC, 
      'http://schemas.datacontract.org/2004/07/Zynergy' AS ZYN)
  
  SELECT Contract, LineName.value('.', 'varchar(50)') as LineItemName, ProjName.value('.', 'varchar(50)') as ProjectName
  FROM dbo.tblContractMaster AS CM 
  CROSS APPLY CM.FullContract.nodes('/ZC:ZEstimateContract/ZC:_projects/ZC:ZEstimateProject/ZYN:Name') as Proj(ProjName)
  CROSS APPLY CM.FullContract.nodes('/ZC:ZEstimateContract/ZC:_projects/ZC:ZEstimateProject/ZC:_lines/ZC:ZEstimateLine/ZYN:Name') as Line(LineName)
  </code></pre>
  
  <p><strong>The results are:</strong></p>
  
  <pre><code>QGPET0000000218    0.080 Aluminum 2ft x 4ft          &lt;BOM1&gt;
  QGPET0000000218    #8X8SPOOL                         &lt;BOM1&gt;
  QGPET0000000218    0.080 Aluminum 2ft x 4ft          &lt;BOM2&gt;
  QGPET0000000218    #8X8SPOOL                         &lt;BOM2&gt;
  </code></pre>
  
  <p>The problem is that "0.080 Aluminum 2ft x 4ft" is only on "BOM1" and "#8X8SPOOL" is only on "BOM2", so there should only be 2 rows in the results.  Please help, thanks!</p>