Get SQL Server Table Records in XML

In this article just see how to get SQL Server Table Records in XML format. Most developers just want to get there record in XML format so that they can easily use this data in Web Services or REST full services.

Firstly, open your SQL Server and create a table with any script or schema or you can copy the following script:
  1. CREATE TABLE [dbo].[Employee](  
  2. [Id] [int] NOT NULL,  
  3. [Name] [varchar](50) NULL,  
  4. [Salary] [int] NULL,  
  5. [DptId] [int] NULL);  
Now insert some records for test query as in the following query:
  1. INSERT INTO [dbo].[Employee]  
  2. ([Id]  
  3. ,[Name]  
  4. ,[Salary]  
  5. ,[DptId])  
  6. VALUES  
  7. (<Id, int,>  
  8. ,<Name, varchar(50),>  
  9. ,<Salary, int,>  
  10. ,<DptId, int,>)  
  11. GO  
Or you can copy the following queries:
 
Insert into Employee values.
  1. (101,'Nitin Pandit',12345,111),  
  2. (102,'Rahul Pandit',12345,222),  
  3. (103,'Mohit Pandit',12345,333),  
  4. (104,'Sonu Pandit',12345,444)  
Now just run the select command to get the records.
  1. Select * from Employee  
Your output must look like the following image.

output

Now if you want to get the data in XML so just write your query as in the following:
  1. select * from Employee  
FOR XML path, root;

When you run this query so you’ll get your records in XMl and if you want to see all data just click on the link:

link

This is your data from SQL Server table to XML Records.
  1. <root>  
  2.     <row>  
  3.         <Id>101</Id>  
  4.         <Name>Nitin Pandit</Name>  
  5.         <Salary>12345</Salary>  
  6.         <DptId>111</DptId>  
  7.     </row>  
  8.     <row>  
  9.         <Id>102</Id>  
  10.         <Name>Rahul Pandit</Name>  
  11.         <Salary>12345</Salary>  
  12.         <DptId>222</DptId>  
  13.     </row>  
  14.     <row>  
  15.         <Id>103</Id>  
  16.         <Name>Mohit Pandit</Name>  
  17.         <Salary>12345</Salary>  
  18.         <DptId>333</DptId>  
  19.     </row>  
  20.     <row>  
  21.         <Id>104</Id>  
  22.         <Name>Sonu Pandit</Name>  
  23.         <Salary>12345</Salary>  
  24.         <DptId>444</DptId>  
  25.     </row>  
  26. </root>  
Thanks for reading this article. Share it and if you have any questions, then please mention in the comments section.
Connect (“Nitin Pandit”);


Similar Articles