Shredding XML data in SQL2012- How can I get values from nested elements

Question

I have a table named Audit.Event which captures user events, one of the columns within that table stores the data as xml, which is structured as such

XML Data

<OrganisationSearchEvent xmlns:i="http://www.w3.org/2001/XMLSchema-instance">
  <Options>
    <IsAgency>false</IsAgency>
    <IsCharity>false</IsCharity>
    <IsCompany>false</IsCompany>
    <IsEducationalEstablishment>false</IsEducationalEstablishment>
    <IsProgrammeProvider>false</IsProgrammeProvider>
    <Name i:nil="true" />
    <OrganisationIDs xmlns:d3p1="http://schemas.datacontract.org/2004/07/System" i:nil="true" />
    <ProfessionalBodies xmlns:d3p1="http://schemas.datacontract.org/2004/07/System" i:nil="true" />
    <Sectors xmlns:d3p1="http://schemas.datacontract.org/2004/07/System">
      <d3p1:int>21</d3p1:int>
      <d3p1:int>82</d3p1:int>
    </Sectors>
    <SubSector xmlns:d3p1="http://schemas.datacontract.org/2004/07/System" i:nil="true" />
  </Options>
  <Paging>
    <EndRecord i:nil="true" />
    <PageNumber>1</PageNumber>
    <PagingMode>Paged</PagingMode>
    <RecordsPerPage>20</RecordsPerPage>
    <StartRecord i:nil="true" />
    <TotalPages>1</TotalPages>
  </Paging>
</OrganisationSearchEvent>

I need to shred the xml and capture the data in the elements named d3p1:int. So far I have wrote the following query:

Query

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

All I would expect to see is

+----------+
| SectorID |
+----------+
|       10 |
|       45 |
|       50 |
|       22 |
|        6 |
+----------+

But instead I get the following error

The name "d3p1" does not denote a namespace.

If I replace "d3p1:int" with just "d3p1", nulls are returned

If I use the following line in the select clause as a test, the correct data for that element is returned.

Data.value('(//Name)[1]','nvarchar(100)') AS [Name]

Could anyone point me in the right direction, am unsure what the actual cause of this is

Thanks

--Edit--

Following Shnugo's suggestion I have tried the following with no luck, I must be missing something!

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.

Attempt 1

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)

But I get the error

The multi-part identifier "Audit.EventData.Data" could not be bound.

I assume I still need to use the table so I tried this next

Attempt 2

;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)

but this returns nothing

Final Solution

Here is the final query I used to return the correct data

 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 

Answer 1

Try it like this:

DECLARE @xml XML=
'<OrganisationSearchEvent xmlns:i="http://www.w3.org/2001/XMLSchema-instance">
  <Options>
    <IsAgency>false</IsAgency>
    <IsCharity>false</IsCharity>
    <IsCompany>false</IsCompany>
    <IsEducationalEstablishment>false</IsEducationalEstablishment>
    <IsProgrammeProvider>false</IsProgrammeProvider>
    <Name i:nil="true" />
    <OrganisationIDs xmlns:d3p1="http://schemas.datacontract.org/2004/07/System" i:nil="true" />
    <ProfessionalBodies xmlns:d3p1="http://schemas.datacontract.org/2004/07/System" i:nil="true" />
    <Sectors xmlns:d3p1="http://schemas.datacontract.org/2004/07/System">
      <d3p1:int>21</d3p1:int>
      <d3p1:int>82</d3p1:int>
    </Sectors>
    <SubSector xmlns:d3p1="http://schemas.datacontract.org/2004/07/System" i:nil="true" />
  </Options>
  <Paging>
    <EndRecord i:nil="true" />
    <PageNumber>1</PageNumber>
    <PagingMode>Paged</PagingMode>
    <RecordsPerPage>20</RecordsPerPage>
    <StartRecord i:nil="true" />
    <TotalPages>1</TotalPages>
  </Paging>
</OrganisationSearchEvent>';

WITH XMLNAMESPACES('http://schemas.datacontract.org/2004/07/System' AS d3p1)
SELECT i.value('.','int')
FROM @xml.nodes('//d3p1:int') AS A(i);

Without namespaces you might use wildcards like here

SELECT i.value('.','int')
FROM @xml.nodes('//*:int') AS A(i);

Or even the full path

WITH XMLNAMESPACES('http://schemas.datacontract.org/2004/07/System' AS d3p1)
SELECT i.value('.','int')
FROM @xml.nodes('/OrganisationSearchEvent/Options/Sectors/d3p1:int') AS A(i);

This depends on your demands. The general advise is: Be as specific as possible.

About your code: As you want to read all int-elements, you need the .nodes() to return your <d3p1:int>-elements. Your query stops one level to high. And secondly, if you have to deal with namespaces, you have either to use them explicitly or use a wildcard.

UPDATE

Without knowing your actual database it is hard to point you out...

From your question I take, that this:

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

together with this

Data.value('(//Name)[1]','nvarchar(100)') AS [Name]

returns the correct data?

If so, the least specific approach might be this:

SELECT 
Data.value('.','int') AS SectorID
FROM [Audit].[EventData]
Cross Apply Data.nodes('//*:int') as Test(X)
Where EventTypeID = 7 
Order By CapturedDateTime Desc

This should pick any element called int (not the datatype, the element's name!) and list this row-wise...

If you get this to work, you might try to find a more specific XPath, but this should - at least - return the correct values...