Nodes and NameSpaces

with myTable as
(
  SELECT CAST('<root><ids><id>1</id><id>2</id></ids></root>' AS XML) AS Data
)
  
SELECT x.value('.', 'int')
FROM myTable
CROSS APPLY Data.nodes('/root/ids/id') x(x)

--------
  
;with XMLNAMESPACES('bar' as b)
, myTable as
(
  SELECT CAST('<root><ids xmlns="bar"><id>1</id><id>2</id></ids></root>' AS XML) AS Data
)
  
SELECT x.value('.', 'int')
FROM myTable
CROSS APPLY Data.nodes('/root/b:ids/b:id') x(x)



Nodes and Namespaces: Part II
stackoverflow reference*
stackoverflow reference**