Greetings! I didn't work with XML before and so I'm posting this question on how we can generate serialized XML. I have the following table -

DECLARE @Population TABLE (CountryId INT IDENTITY(1,1), CountryName VARCHAR(15), StateName VARCHAR(20), PopulationCount INT)
INSERT INTO @Population (CountryName, StateName, PopulationCount)
VALUES ('USA','California',300000),
('USA','Chicago',500000),
('Australia','Queensland',550000),
('India','Delhi',700000),
('India','Mumbai',50000),
('India','UP',180000),
('India','AP',10000000)

SELECT *
FROM @Population 

This is the XML output I'm expecting for each row.

<Population CountryCode="123">
  <Node>
    <NodeName>County</NodeName>
    <NodeValue>USA</NodeValue>
  </Node>
  <Node>
    <NodeName>State 1 Population</NodeName>
    <NodeValue>300000</NodeValue>
  </Node>
  <Node>
    <NodeName>State 2 Population</NodeName>
    <NodeValue>500000</NodeValue>
  </Node>
</Population>


<Population CountryCode="789">
  <Node>
    <NodeName>County</NodeName>
    <NodeValue>Australia</NodeValue>
  </Node>
  <Node>
    <NodeName>State 1 Population</NodeName>
    <NodeValue>550000</NodeValue>
  </Node>
</Population>

<Population CountryCode="456">
  <Node>
    <NodeName>County</NodeName>
    <NodeValue>India</NodeValue>
  </Node>
  <Node>
    <NodeName>State 1 Population</NodeName>
    <NodeValue>700000</NodeValue>
  </Node>
  <Node>
    <NodeName>State 2 Population</NodeName>
    <NodeValue>50000</NodeValue>
  </Node>
 <Node>
    <NodeName>State 3 Population</NodeName>
    <NodeValue>180000</NodeValue>
  </Node>
 <Node>
    <NodeName>State 4 Population</NodeName>
    <NodeValue>10000000</NodeValue>
  </Node>
</Population>

Please note that I can have another country with 10 states or 30 states, so State Name is dynamic.

Can this be done in SQL ? or we have to use .NET ?

SQLServer2014 -12.0.2000.8(X64)

That XML is completely useless and poorly designed.  Why are the elements named Node and NodeName, etc?

Fix the XML design and your data will be simple to format as XML.

David

Try

DECLARE @Population TABLE (CountryId INT, CountryName VARCHAR(15), StateName VARCHAR(20), PopulationCount INT)
INSERT INTO @Population (CountryId, CountryName, StateName, PopulationCount)
VALUES (123, 'USA','California',300000),
(123, 'USA','Chicago',500000),
(400, 'Australia','Queensland',550000),
(500,'India','Delhi',700000),
(500, 'India','Mumbai',50000),
(500, 'India','UP',180000),
(500, 'India','AP',10000000)

SELECT CountryId as [@CountryCode], 
'Contry' as [Node/NodeName],
CountryName as [Node/NodeValue],
(SELECT StateName + ' Population' as [NodeName],
PopulationCount as [NodeValue] from @Population [Node]
where Node.CountryId = P.CountryId
FOR XML PATH('Node'), TYPE) 
FROM @Population P 
GROUP BY P.CountryId, P.CountryName
for XML PATH('Population'), ROOT('Info')

@Dave - That's the XML format the Webs service accepts and I don't have any control on that.

@Naomi - Thank you! A small change that I need to make, similar to Country, I need to create another node for CountryLocation. So this if this is the input

DECLARE @Population TABLE (CountryId INT, CountryName VARCHAR(15), 
			CountryLocation VARCHAR(10),
			StateName VARCHAR(20), PopulationCount INT)
INSERT INTO @Population (CountryId, CountryName, 
			CountryLocation, 
			StateName, PopulationCount)
VALUES (123, 'USA', 'West', 'California',300000),
(123, 'USA', 'West','Chicago',500000),
(400, 'Australia','West','Queensland',550000),
(500,'India','East','Delhi',700000),
(500, 'India','East','Mumbai',50000),
(500, 'India','East','UP',180000),
(500, 'India','East','AP',10000000)

SELECT *
FROM @Population

This is the expected output -

<?xml version="1.0"?>
<Info>
  <Population CountryCode="123">
    <Node>
      <NodeName>Contry</NodeName>
      <NodeValue>USA</NodeValue>
    </Node>
    <Node>
    	<NodeName>CountryLoc</NodeName>
    	<NodeValue>West</NodeValue>
    </Node>
    <Node>
      <NodeName>California Population</NodeName>
      <NodeValue>300000</NodeValue>
    </Node>
    <Node>
      <NodeName>Chicago Population</NodeName>
      <NodeValue>500000</NodeValue>
    </Node>
  </Population>
  <Population CountryCode="400">
    <Node>
      <NodeName>Contry</NodeName>
      <NodeValue>Australia</NodeValue>
    </Node>
    <Node>
    	<NodeName>CountryLoc</NodeName>
    	<NodeValue>West</NodeValue>
    </Node>
    <Node>
      <NodeName>Queensland Population</NodeName>
      <NodeValue>550000</NodeValue>
    </Node>
  </Population>
  <Population CountryCode="500">
    <Node>
      <NodeName>Contry</NodeName>
      <NodeValue>India</NodeValue>
    </Node>
    <Node>
    <NodeName>CountryLoc</NodeName>
    <NodeValue>East</NodeValue>
    </Node>
    <Node>
      <NodeName>Delhi Population</NodeName>
      <NodeValue>700000</NodeValue>
    </Node>
    <Node>
      <NodeName>Mumbai Population</NodeName>
      <NodeValue>50000</NodeValue>
    </Node>
    <Node>
      <NodeName>UP Population</NodeName>
      <NodeValue>180000</NodeValue>
    </Node>
    <Node>
      <NodeName>AP Population</NodeName>
      <NodeValue>10000000</NodeValue>
    </Node>
  </Population>
</Info>

I'm trying with this query -

SELECT CountryId as [@CountryCode], 
'Contry' as [Node/NodeName],
CountryName as [Node/NodeValue],
'CountryLoc' as [NodeName],
CountryLocation as [NodeValue],
(SELECT StateName + ' Population' as [NodeName],
PopulationCount as [NodeValue] from @Population [Node]
where Node.CountryId = P.CountryId
FOR XML PATH('Node'), TYPE) 
FROM @Population P 
GROUP BY P.CountryId, P.CountryName, P.CountryLocation
for XML PATH('Population'), ROOT('Info')

It's giving the following output -

<?xml version="1.0"?>
<Info>
  <Population CountryCode="123">
    <Node>
      <NodeName>Contry</NodeName>
      <NodeValue>USA</NodeValue>
    </Node>
    <NodeName>CountryLoc</NodeName>
    <NodeValue>West</NodeValue>
    <Node>
      <NodeName>California Population</NodeName>
      <NodeValue>300000</NodeValue>
    </Node>
    <Node>
      <NodeName>Chicago Population</NodeName>
      <NodeValue>500000</NodeValue>
    </Node>
  </Population>
  <Population CountryCode="400">
    <Node>
      <NodeName>Contry</NodeName>
      <NodeValue>Australia</NodeValue>
    </Node>
    <NodeName>CountryLoc</NodeName>
    <NodeValue>West</NodeValue>
    <Node>
      <NodeName>Queensland Population</NodeName>
      <NodeValue>550000</NodeValue>
    </Node>
  </Population>
  <Population CountryCode="500">
    <Node>
      <NodeName>Contry</NodeName>
      <NodeValue>India</NodeValue>
    </Node>
    <NodeName>CountryLoc</NodeName>
    <NodeValue>East</NodeValue>
    <Node>
      <NodeName>Delhi Population</NodeName>
      <NodeValue>700000</NodeValue>
    </Node>
    <Node>
      <NodeName>Mumbai Population</NodeName>
      <NodeValue>50000</NodeValue>
    </Node>
    <Node>
      <NodeName>UP Population</NodeName>
      <NodeValue>180000</NodeValue>
    </Node>
    <Node>
      <NodeName>AP Population</NodeName>
      <NodeValue>10000000</NodeValue>
    </Node>
  </Population>
</Info>

Please see in this result, the following is not in the <Node> </Node> tag and I wanted them in the <Node> tags.

    <NodeName>CountryLoc</NodeName>
    <NodeValue>West</NodeValue>

Hi Naarasimha,

Please try the below sample.

	DECLARE @Population TABLE (CountryId INT, CountryName VARCHAR(15), 
				CountryLocation VARCHAR(10),
				StateName VARCHAR(20), PopulationCount INT)
	INSERT INTO @Population (CountryId, CountryName, 
				CountryLocation, 
				StateName, PopulationCount)
	VALUES (123, 'USA', 'West', 'California',300000),
	(123, 'USA', 'West','Chicago',500000),
	(400, 'Australia','West','Queensland',550000),
	(500,'India','East','Delhi',700000),
	(500, 'India','East','Mumbai',50000),
	(500, 'India','East','UP',180000),
	(500, 'India','East','AP',10000000)

	SELECT *
	FROM @Population

	SELECT CountryId as [@CountryCode], 
	'Contry' as [Node/NodeName],
	CountryName as [Node/NodeValue],
	(SELECT 
	'CountryLoc' as [NodeName],
	CountryLocation as [NodeValue]
	FOR XML PATH('Node'),TYPE
	),
	(SELECT StateName + ' Population' as [NodeName],
	PopulationCount as [NodeValue] from @Population [Node]
	where Node.CountryId = P.CountryId
	FOR XML PATH('Node'), TYPE) 
	FROM @Population P 
	GROUP BY P.CountryId, P.CountryName, P.CountryLocation
	for XML PATH('Population'), ROOT('Info')

If you have any question, feel free to let me know.