<?xml version="1.0" encoding="utf-8" ?>
<users>
<user>
<FirstName>Vinod</FirstName>
<LastName>Patel</LastName>
<UserName>VinodPatel</UserName>
<Job>Team Leader</Job>
</user>
<user>
<FirstName>Hiten</FirstName>
<LastName>Patel</LastName>
<UserName>HitenPatel</UserName>
<Job>Software Developer</Job>
</user>
<user>
<FirstName>Hari</FirstName>
<LastName>Fofandi</LastName>
<UserName>HariFofandi</UserName>
<Job>Business Analyst</Job>
</user>
</users>
DECLARE @XMLdata XML
SET @XMLdata='<users>
<user>
<FirstName>Vinod</FirstName>
<LastName>Patel</LastName>
<UserName>VinodPatel</UserName>
<Job>Team Leader</Job>
</user>
<user>
<FirstName>Hiten</FirstName>
<LastName>Patel</LastName>
<UserName>HitenPatel</UserName>
<Job>Software Developer</Job>
</user>
<user>
<FirstName>Hari</FirstName>
<LastName>Fofandi</LastName>
<UserName>HariFofandi</UserName>
<Job>Business Analyst</Job>
</user>
</users>'
SELECT
t.value('(FirstName/text())[1]','varchar(50)')AS FirstName ,
t.value('(LastName/text())[1]','varchar(50)')AS LastName,
t.value('(UserName/text())[1]','varchar(50)')AS UserName,
t.value('(Job/text())[1]','varchar(50)')AS Job
FROM
@XMLdata .nodes('/users/user')AS TempTable(t)
<users>
<user>
<FirstName>Vinod</FirstName>
<LastName>Patel</LastName>
<UserName>VinodPatel</UserName>
<Job>Team Leader</Job>
</user>
<user>
<FirstName>Hiten</FirstName>
<LastName>Patel</LastName>
<UserName>HitenPatel</UserName>
<Job>Software Developer</Job>
</user>
<user>
<FirstName>Hari</FirstName>
<LastName>Fofandi</LastName>
<UserName>HariFofandi</UserName>
<Job>Business Analyst</Job>
</user>
</users>
DECLARE @XMLdata XML
SET @XMLdata='<users>
<user>
<FirstName>Vinod</FirstName>
<LastName>Patel</LastName>
<UserName>VinodPatel</UserName>
<Job>Team Leader</Job>
</user>
<user>
<FirstName>Hiten</FirstName>
<LastName>Patel</LastName>
<UserName>HitenPatel</UserName>
<Job>Software Developer</Job>
</user>
<user>
<FirstName>Hari</FirstName>
<LastName>Fofandi</LastName>
<UserName>HariFofandi</UserName>
<Job>Business Analyst</Job>
</user>
</users>'
SELECT
t.value('(FirstName/text())[1]','varchar(50)')AS FirstName ,
t.value('(LastName/text())[1]','varchar(50)')AS LastName,
t.value('(UserName/text())[1]','varchar(50)')AS UserName,
t.value('(Job/text())[1]','varchar(50)')AS Job
FROM
@XMLdata .nodes('/users/user')AS TempTable(t)