How to Convert Hierarchical Data into XML in SQL Server

sample Input Table Details:

Expected Output:

<Geo>
	<Continent Name="Asia">
		<Country Name="China">
			<State Name="Bejing"></State>
		</Country>
		<Country Name="Japan">
			<State Name="Tokyo"></State>
		</Country>	
	</Continent>
	<Continent Name="Europe">
		<Country Name="Germany">
			<State Name="Berlin"></State>
		</Country>
		<Country Name="France">
			<State Name="Paris"></State>
		</Country>	
	</Continent>
</Geo>

How to convert Input relation Data as Above Output XML?

Something like this:

create table #continents (
	continentid int, [continent name] nvarchar(100))
insert into #continents
SELECT	1, 'Asia'
UNION ALl
SELECT	2, 'Europe'
UNION ALL
SELECT	3, 'Africa'

CREATE TABLE #countries (countryid int, [country name] nvarchar(100), continentid INT)

insert into #countries
SELECT	1,'China', 1
UNION ALL
SELECT	2, 'Japan', 1
UNION ALL
SELECT	3, 'Germany', 2
UNION ALL
SELECT	4, 'France', 2

CREATE TABLE #states (stateId INT, [State Name] nvarchar(100), countryid INT)

INSERT INTO #states
SELECT	1, 'Tokya', 2
UNION ALL
SELECT	2, 'Berlin', 3
UNION ALL
SELECT	3, 'Paris', 4
UNION ALL
SELECT	4, 'Bejing', 1


select	c.[Continent Name] AS [@Name]
,	(
	select cc.[Country Name] AS [@Name]
	,	(
		SELECT	[State Name] AS [@Name]
		FROM	#states ss
		WHERE	ss.countryId = cc.countryId
		FOR XML PATH('State'), TYPE
	)
	FROM	#countries cc
	WHERE	cc.continentId = c.continentId
	FOR XML PATH('Country'), TYPE
	)
from	#continents c
for xml Path('Continent'), ROOT('Geo'), TYPE

Btw, next time, try to post data as create / insert script, instead of a screenshot, it really saves a lot of typing

I was just too late copying all the data....

WITH Continent AS (
SELECT 1 as Continent_ID, 'Asia' as Continent_Name
UNION ALL
SELECT 2, 'Europe'
UNION ALL
SELECT 3,'Africa'),
Country AS (
SELECT 1 as Country_ID, 'China' as Country_Name, 1 as Continent_ID
UNION ALL
SELECT 2,'Japan', 1
UNION ALL 
SELECT 3, 'Germany', 2),
State AS (
SELECT 1 as State_ID, 'Tokya' as State_Name, 2 as Country_ID
UNION ALL
SELECT 2, 'Berlin', 3 )
select Continent_Name, Country_Name, State_Name
from Continent
inner join Country on Country.Continent_ID = Continent.Continent_ID
inner join State ON State.Country_ID = Country.Country_ID FOR XML AUTO, Root('Geo'), TYPE;

output (after formatting):

<Geo>
<Continent Continent_Name="Asia">
<Country Country_Name="Japan">
<State State_Name="Tokya"/>
</Country>
</Continent>
<Continent Continent_Name="Europe">
<Country Country_Name="Germany">
<State State_Name="Berlin"/>
</Country>
</Continent>
</Geo>

More info: FOR XML (SQL Server)

> A SELECT query returns results as a rowset. You can optionally
> retrieve formal results of a SQL query as XML by specifying the FOR
> XML clause in the query. The FOR XML clause can be used in top-level
> queries and in subqueries. The top-level FOR XML clause can be used
> only in the SELECT statement. In subqueries, FOR XML can be used in
> the INSERT, UPDATE, and DELETE statements. FOR XML can also be used in
> assignment statements.