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.
**************************************************************
*********************************************************************************
**************************************************************
**************************************************************
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