Breaking News
Loading...
Wednesday 24 August 2016

How to use XML path in MS SQL

Generally XML Path in SQL is use to convert the multiple row in single row. you we can say that you can concate the multiple rows in single row for understanding the XML path function we simple take one example so first we create one table with State and city column as show in below

declare @Test Table(StateName VARCHAR(20),CityName VARCHAR(20))
Insert @Test(StateName,CityName)
Values
 ('Gujarat','Ahmedabad')
,('Gujarat','Rajkot')
,('Gujarat','Junagadh')
,('Rajasthan','Kota')
,('Rajasthan','Udaypur')
,('Rajasthan','Jaipur')
,('Maharastra','Mumbai-1')
,('Maharastra','Mumbai-2');


OUTPUT:




Here you see that table result display state name with city name but I want all the city name in single column with comma separated so for that I use the XML path function in MS SQL SERVER.
Syntax: <Your Select query> For XML Path ('');
It converts the data in XML format.

Select CityName
from @Test T1
FOR XML PATH('');


OUTPUT:



Now we do not want XML tag in between city name so for removing the XML starting and closing tag
You have to simple added semi column (",") as prefix to column name as show in below query



Select ','+CityName
from @Test T1
FOR XML PATH('');


OUTPUT :



SO you can see finally you get your result you see the output is the entire city name in single row with comma separated.
Note: here there is one extra semi column in start of string you can remove this by using STUFF function.(here STUFF function.)

0 comments:

Post a Comment

Thanks for comment

 
Toggle Footer