Fetch the data in XML format using SQL Server

In this example will see how to fetch data in XML format in SQL Server and Save the XML file.
Suppose we have a table named as T_Population.

To fetch the record as XML Please follow below,

SELECT * FROM T_Population FOR XML AUTO

When the click the XML result we can see the below XML file.

<T_Population Id="1" Country="UK" PopulationCountDate="2019-01-01" PopulationCount="40088090" />
<T_Population Id="2" Country="USA" PopulationCountDate="2019-03-25" PopulationCount="57753898" />
<T_Population Id="3" Country="India" PopulationCountDate="2019-01-25" PopulationCount="1297094947" />
<T_Population Id="4" Country="UK" PopulationCountDate="2019-05-01" PopulationCount="400885786" />
<T_Population Id="5" Country="UK" PopulationCountDate="2019-08-01" PopulationCount="400885855" />
<T_Population Id="6" Country="UK" PopulationCountDate="2019-12-05" PopulationCount="400885859" />
<T_Population Id="7" Country="USA" PopulationCountDate="2019-05-25" PopulationCount="577567936" />
<T_Population Id="8" Country="USA" PopulationCountDate="2019-09-25" PopulationCount="577634534" />
<T_Population Id="9" Country="USA" PopulationCountDate="2019-12-25" PopulationCount="577644444" />
<T_Population Id="10" Country="India" PopulationCountDate="2019-02-25" PopulationCount="1297095555" />
<T_Population Id="11" Country="India" PopulationCountDate="2019-04-25" PopulationCount="1297274678" />
<T_Population Id="12" Country="India" PopulationCountDate="2019-06-25" PopulationCount="1297333443" />
<T_Population Id="13" Country="India" PopulationCountDate="2019-09-25" PopulationCount="1298435903" />
<T_Population Id="14" Country="India" PopulationCountDate="2019-10-25" PopulationCount="1299563676" />
<T_Population Id="15" Country="India" PopulationCountDate="2019-12-25" PopulationCount="1299956535" />

Here you can see the XML document contain multiple root level elements. To fetch the data as Parent-child and save the XML into physical location we need to follow this.

Saving as XML file in SQL Server

SELECT * FROM T_Population FOR XML PATH('T_Population'),
ROOT('T_Population')

You can save the File in your physical location into CSV/XML File

Now the Final XML file should be like this.

<T_Population>  
  <T_Population>  
   <Id>1</Id>  
   <Country>UK</Country>  
   <PopulationCountDate>2019-01-01</PopulationCountDate>  
   <PopulationCount>40088090</PopulationCount>  
  </T_Population>  
  <T_Population>  
   <Id>2</Id>  
   <Country>USA</Country>  
   <PopulationCountDate>2019-03-25</PopulationCountDate>  
   <PopulationCount>57753898</PopulationCount>  
  </T_Population>  
  <T_Population>  
   <Id>3</Id>  
   <Country>India</Country>  
   <PopulationCountDate>2019-01-25</PopulationCountDate>  
   <PopulationCount>1297094947</PopulationCount>  
  </T_Population>  
  <T_Population>  
   <Id>4</Id>  
   <Country>UK</Country>  
   <PopulationCountDate>2019-05-01</PopulationCountDate>  
   <PopulationCount>400885786</PopulationCount>  
  </T_Population>  
  <T_Population>  
   <Id>5</Id>  
   <Country>UK</Country>  
   <PopulationCountDate>2019-08-01</PopulationCountDate>  
   <PopulationCount>400885855</PopulationCount>  
  </T_Population>  
  <T_Population>  
   <Id>6</Id>  
   <Country>UK</Country>  
   <PopulationCountDate>2019-12-05</PopulationCountDate>  
   <PopulationCount>400885859</PopulationCount>  
  </T_Population>  
  <T_Population>  
   <Id>7</Id>  
   <Country>USA</Country>  
   <PopulationCountDate>2019-05-25</PopulationCountDate>  
   <PopulationCount>577567936</PopulationCount>  
  </T_Population>  
  <T_Population>  
   <Id>8</Id>  
   <Country>USA</Country>  
   <PopulationCountDate>2019-09-25</PopulationCountDate>  
   <PopulationCount>577634534</PopulationCount>  
  </T_Population>  
  <T_Population>  
   <Id>9</Id>  
   <Country>USA</Country>  
   <PopulationCountDate>2019-12-25</PopulationCountDate>  
   <PopulationCount>577644444</PopulationCount>  
  </T_Population>  
  <T_Population>  
   <Id>10</Id>  
   <Country>India</Country>  
   <PopulationCountDate>2019-02-25</PopulationCountDate>  
   <PopulationCount>1297095555</PopulationCount>  
  </T_Population>  
  <T_Population>  
   <Id>11</Id>  
   <Country>India</Country>  
   <PopulationCountDate>2019-04-25</PopulationCountDate>  
   <PopulationCount>1297274678</PopulationCount>  
  </T_Population>  
  <T_Population>  
   <Id>12</Id>  
   <Country>India</Country>  
   <PopulationCountDate>2019-06-25</PopulationCountDate>  
   <PopulationCount>1297333443</PopulationCount>  
  </T_Population>  
  <T_Population>  
   <Id>13</Id>  
   <Country>India</Country>  
   <PopulationCountDate>2019-09-25</PopulationCountDate>  
   <PopulationCount>1298435903</PopulationCount>  
  </T_Population>  
  <T_Population>  
   <Id>14</Id>  
   <Country>India</Country>  
   <PopulationCountDate>2019-10-25</PopulationCountDate>  
   <PopulationCount>1299563676</PopulationCount>  
  </T_Population>  
  <T_Population>  
   <Id>15</Id>  
   <Country>India</Country>  
   <PopulationCountDate>2019-12-25</PopulationCountDate>  
   <PopulationCount>1299956535</PopulationCount>  
  </T_Population>  
 </T_Population>  

Summary

   In this tutorial we discussed how to Fetch the data in XML format using SQL Server. If have any question related to this topic then give your feedback.