Can't get value from openxml when element has xmlns attribute

Question

The xmlns attribute in the following code stops me getting the value I need. Works fine with any other attribute but not xmlns. I have no control over the xml I'm given - how can I get the CrpId value?

declare @CrpId int, @i int, @xml xml
set @xml = 
'<NewProgressReportResult xmlns="http://myDomain.com/crp"&gt;
<CrpId>2160</CrpId>
</NewProgressReportResult>'

exec sp_xml_preparedocument @i output, @xml

select 
CrpId
from openxml (@i, 'NewProgressReportResult', 2)
with ( 
 CrpId int 'CrpId'
)

exec sp_xml_removedocument @i

Answer 1

I don't know OpenXML at all myself, but this FAQ seems to have the answer. Basically it's because you've got elements in a particular namespace (due to the xlmns attribute) so you need to be able to specify the same namespace in your query.

Converting this to your particular problem, I think you want:

declare @CrpId int, @i int, @xml xml
set @xml = 
'<NewProgressReportResult xmlns="http://myDomain.com/crp"&gt;
<CrpId>2160</CrpId>
</NewProgressReportResult>'

set @ns = '<root xmlns:ns="http://myDomain.com/crp"/&gt;

exec sp_xml_preparedocument @i output, @xml, @ns

select 
CrpId
from openxml (@i, '[ns:NewProgressReportResult]', 2)
with (  
        [ns:CrpId] int 'CrpId'
)

exec sp_xml_removedocument @i