title: Extracting data from SQL Server's XML execution plan
body: <p>My ultimate goal is automatic extraction of all referenced columns from a cached execution plan. This will help us keep a track of all the columns used by our scheduled set of SSRS reports.</p>
  
  <p>The XML data of interest looks like this:</p>
  
  <pre><code>&lt;ColumnReference Database="[AdventureWorksDW2012]" Schema="[dbo]" Table="[DimCustomer]"  Alias="[dC]" Column="HouseOwnerFlag" /&gt;
  </code></pre>
  
  <p>and I would like to store Database, Schema, Table, Alias and Column values in a table.</p>
  
  <p>However, for a proof of concept, I have taken a simple query and copied the following part of the full execution plan into a TSQL code below:</p>
  
  <pre><code>DECLARE @myDoc xml;
  SET     @myDoc = '&lt;ShowPlanXML xmlns="http://schemas.microsoft.com/sqlserver/2004/07/showplan"  Version="1.2" Build="11.0.3381.0"  TEST="1"&gt;
      &lt;BatchSequence&gt;
        &lt;Batch&gt;
          &lt;Statements&gt;
            &lt;StmtSimple StatementText="SELECT ... &amp;#xD;&amp;#xA;" StatementId="1" StatementCompId="1" ThereIsMoreHere="..." &gt;
              &lt;StatementSetOptions QUOTED_IDENTIFIER="true" ARITHABORT="true" ThereIsMoreHere="..." /&gt;
          &lt;/StmtSimple&gt;
        &lt;/Statements&gt;
      &lt;/Batch&gt;
    &lt;/BatchSequence&gt;
  &lt;/ShowPlanXML&gt;';
  
  SELECT StatementId = @myDoc.value('(/ShowPlanXML/BatchSequence/Batch/Statements/StmtSimple/@StatementId)[1]', 'int');
  SELECT StatementId = @myDoc.value('(/ShowPlanXML/@TEST)[1]',                                     'int');
  </code></pre>
  
  <p>Both SELECT statements are returning NULL. What is wrong here? I feel I am slowly going blind.
  This is executed against an SQL Server 2012 SP1 Developers edition.</p>