Q:

Microsoft T-SQL XML Parsing Issue

I have XML similar to the following:
DECLARE @XML AS XML = 
'<RootElement xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
  <SubElement xmlns="http://foobar">
    <foo1>bar1</foo1>
  </SubElement>
</RootElement>'

And I'm trying to parse it with the following SQL:
; WITH XMLNAMESPACES(DEFAULT 'http://foobar')
SELECT 
    f.x.value('foo1[1]', 'varchar(10)')
from 
    @xml.nodes('/RootElement/SubElement') as f(x)

But it doesn't seem to work. Is the XML namespace on the SubElement node causing the issue? I ask because the following configuration works:
DECLARE @XML AS XML = 
'<RootElement xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
  <SubElement>
    <foo1>bar1</foo1>
  </SubElement>
</RootElement>'

SELECT 
    f.x.value('foo1[1]', 'varchar(10)')
from 
    @xml.nodes('/RootElement/SubElement') as f(x)

FYI:
select @@VERSION

Microsoft SQL Server 2008 R2 (SP1) - 10.50.2500.0 (X64)   Jun 17 2011 00:54:03   Copyright (c) Microsoft Corporation  Developer Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1) 

A:

Try this:

; WITH XMLNAMESPACES('http://foobar' as Y)
SELECT 
    f.x.value('.', 'varchar(10)')
from 
    @xml.nodes('/RootElement/Y:SubElement') as f(x)

