sql server 2008 query to generate xml



enter link description here


Please sugest sql to generate the given xml


I tried this



SELECT
1 as tag,
null as parent,
a.name as [countryid!1!name!element],
null as [countryid!1!country!element],
null as[country!2!state!element],
null as[state!3!city!element],
null as [city!4!place!element]
from
a A
join
b1 BKT on A.countryid = BKT.countryid

union all

select
2 as tag,
1 as parent,
name, country, state, city, place
from
a
join
b1 on a.countryid = b1.countryid
order by
[a].name
for xml explicit


This is not giving complete result


No comments:

Post a Comment