Thursday, 17 October 2013

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




No comments:

Post a Comment