Nodes and Namespaces: Part II

--without namespace
set @myXML='<root><ids><id><number>1</number><name>one</name></id><id><number>2</number><name>two</name></id></ids></root>'
;with
myTable as
(
SELECT @myXML AS Data
)
SELECT
data,
x.XmlCol.value('number[1]', 'int')
,x.XmlCol.value('(name)[1]', 'varchar(100)')
, x.XmlCol.value('.', 'varchar(100)')
FROM myTable
CROSS APPLY Data.nodes('/root/ids/id') x(XmlCol)
-----

--with namespace
set @myXML='<root><ids xmlns="bar"><id><number>1</number><name>one</name></id><id><number>2</number><name>two</name></id></ids></root>'
;with XMLNAMESPACES('bar' as b)
,
myTable as
(
SELECT @myXML AS Data
)
SELECT
data,
x.XmlCol.value('b:number[1]', 'int')
,x.XmlCol.value('(b:name)[1]', 'varchar(100)')
, x.XmlCol.value('.', 'varchar(100)')
FROM myTable
CROSS APPLY Data.nodes('/root/b:ids/b:id') x(XmlCol)
-----