Sql Server : Read a Value from xml with and with out namespace

In this article we are going to see how to read a value from Xml and return the result set.
Let we take an example , From this example i am going to fetch the value from attribute as well as from element .so first declare an xml variable and assign a value in it. Then create a Select statement by specifying the nodes.

declare @x xml

set @x =cast( N'<Colors>

<Color ColorId="#004544" Name="Cyan" >Cyan Color</Color>

<Color ColorId="#000000" Name="Black">Black Color</Color>

<Color ColorId="#121212" Name="Gray" />

</Colors>

' AS XML)

;

**************************************************************

select

n.b.value('@ColorId','varchar(40)') as 'Code',

n.b.value('.','varchar(50)') as 'Color'

from @x.nodes('Colors/Color') n(b);

GO

**************************************************************

Output :

Code       Color

#004544    Cyan Color

#000000    Black Color

#121212

*********************************************************************************

Let we take an example if we have an namespace mention in xml like below example what will be the output for above query.

declare @x xml

set @x =cast( N'<Colors xmlns="http://love.com/">

<Color ColorId="#004544" Name="Cyan" >Cyan Color</Color>

<Color ColorId="#000000" Name="Black">Black Color</Color>

<Color ColorId="#121212" Name="Gray" />

</Colors>

' AS XML)

;

Output will be empty because due to namespace declaration it will not fetch the values correctly, then how to fetch the values from xml if it is having a namespace. Simple declare a xmlnamespace and assign  a alias. namespace mention in xml xmlns="http://love.com/"

declare @x xml

set @x =cast( N'<Colors xmlns="http://love.com/">

<Color ColorId="#004544" Name="Cyan" >Cyan Color</Color>

<Color ColorId="#000000" Name="Black">Black Color</Color>

<Color ColorId="#121212" Name="Gray" />

</Colors>

' AS XML)

;

**************************************************************

with XMLNAMESPACES ('http://love.com/' as ns)

select

n.b.value('@ColorId','varchar(40)'),

n.b.value('.','varchar(50)')

from @x.nodes('ns:Colors/ns:Color') n(b);

**************************************************************

Output :

Code       Color

#004544    Cyan Color

#000000    Black Color

#121212