title: Shredding XML data in SQL2012- How can I get values from nested elements
body: <p>I have a table named <em>Audit.Event</em> which captures user events, one of the columns within that table stores the data as xml, which is structured as such</p>
  
  <p><strong>XML Data</strong></p>
  
  <pre><code>&lt;OrganisationSearchEvent xmlns:i="http://www.w3.org/2001/XMLSchema-instance"&gt;
    &lt;Options&gt;
      &lt;IsAgency&gt;false&lt;/IsAgency&gt;
      &lt;IsCharity&gt;false&lt;/IsCharity&gt;
      &lt;IsCompany&gt;false&lt;/IsCompany&gt;
      &lt;IsEducationalEstablishment&gt;false&lt;/IsEducationalEstablishment&gt;
      &lt;IsProgrammeProvider&gt;false&lt;/IsProgrammeProvider&gt;
      &lt;Name i:nil="true" /&gt;
      &lt;OrganisationIDs xmlns:d3p1="http://schemas.datacontract.org/2004/07/System" i:nil="true" /&gt;
      &lt;ProfessionalBodies xmlns:d3p1="http://schemas.datacontract.org/2004/07/System" i:nil="true" /&gt;
      &lt;Sectors xmlns:d3p1="http://schemas.datacontract.org/2004/07/System"&gt;
        &lt;d3p1:int&gt;21&lt;/d3p1:int&gt;
        &lt;d3p1:int&gt;82&lt;/d3p1:int&gt;
      &lt;/Sectors&gt;
      &lt;SubSector xmlns:d3p1="http://schemas.datacontract.org/2004/07/System" i:nil="true" /&gt;
    &lt;/Options&gt;
    &lt;Paging&gt;
      &lt;EndRecord i:nil="true" /&gt;
      &lt;PageNumber&gt;1&lt;/PageNumber&gt;
      &lt;PagingMode&gt;Paged&lt;/PagingMode&gt;
      &lt;RecordsPerPage&gt;20&lt;/RecordsPerPage&gt;
      &lt;StartRecord i:nil="true" /&gt;
      &lt;TotalPages&gt;1&lt;/TotalPages&gt;
    &lt;/Paging&gt;
  &lt;/OrganisationSearchEvent&gt;
  </code></pre>
  
  <p>I need to shred the xml and capture the data in the elements named <em>d3p1:int</em>. So far I have wrote the following query:</p>
  
  <p><strong>Query</strong></p>
  
  <pre><code>SELECT 
  Data.value('(//d3p1:int)[1]', 'int') AS [SectorID],
  FROM [Audit].[EventData]
  Cross Apply Data.nodes('//OrganisationSearchEvent/Options/Sectors') as Test(X)
  Where EventTypeID = 7 
  Order By CapturedDateTime Desc
  </code></pre>
  
  <p>All I would expect to see is </p>
  
  <pre><code>+----------+
  | SectorID |
  +----------+
  |       10 |
  |       45 |
  |       50 |
  |       22 |
  |        6 |
  +----------+
  </code></pre>
  
  <p>But instead I get the following error</p>
  
  <blockquote>
    <p>The name "d3p1" does not denote a namespace.</p>
  </blockquote>
  
  <p>If I replace "d3p1:int" with just "d3p1", nulls are returned</p>
  
  <p>If I use the following line in the select clause as a test, the correct data for that element is returned.</p>
  
  <pre><code>Data.value('(//Name)[1]','nvarchar(100)') AS [Name]
  </code></pre>
  
  <p>Could anyone point me in the right direction, am unsure what the actual cause of this is</p>
  
  <p>Thanks</p>
  
  <p>
  <h3><strong>--Edit--</strong></h3></p>
  
  <p>Following Shnugo's suggestion I have tried the following with no luck, I must be missing something!</p>
  
  <p>I am unable to point to the actual XMLdata as shown as this is being stored via the application into a column in the "Autit.EventData" table, what I tried to do is this.</p>
  
  <p><strong>Attempt 1</strong></p>
  
  <pre><code>Declare @xml XML=[Audit].[EventData].[Data]
  
  ;WITH XMLNAMESPACES ('http://www.w3.org/2001/XMLSchema-instance' AS d3p1)
  SELECT i.value('.','int') AS [SectorID]
  FROM @xml.nodes('/OrganisationSearchEvent/Options/Sectors/d3p1:int') as A(i)
  </code></pre>
  
  <p>But I get the error </p>
  
  <blockquote>
    <p>The multi-part identifier "Audit.EventData.Data" could not be bound.</p>
  </blockquote>
  
  <p>I assume I still need to use the table so I tried this next</p>
  
  <p><strong>Attempt 2</strong></p>
  
  <pre><code>;WITH XMLNAMESPACES ('http://www.w3.org/2001/XMLSchema-instance' AS d3p1)
  SELECT i.value('.','int') AS [SectorID]
  FROM [Audit].[EventData]
  
  Cross Apply Data.nodes('/OrganisationSearchEvent/Options/Sectors/d3p1:int') as A(i)
  </code></pre>
  
  <p>but this returns nothing</p>
  
  <p><strong>Final Solution</strong></p>
  
  <p>Here is the final query I used to return the correct data</p>
  
  <pre><code> SELECT 
  T.C.value('.', 'int') as [SelectedID's] 
  from Audit.[EventData] 
  cross apply Data.nodes('/OrganisationSearchEvent/Options/Sectors/*') as T(C) 
  where EventTypeID = 7 
  order by CapturedDateTime desc 
  </code></pre>