how to read xml file in sql server stored procedure

In this article, I am going to explain how to read or select xml data in SQL.

Firstly I show you xml which one is ready in SQL procedure. The given below xml have Empcode,name,DOB and age fileds, which one shown by sql in a table

<EmpDetails>     <NewDataSet>         <EmpCode>EMP014</EmpCode>         <name>Sharad</name>         <DOB>12/24/1978 12:00:00 AM</DOB>         <age>35</age>         <location>x</location>         <gender>Male</gender>         <StateID>1</StateID>         <CityID>1</CityID>         <designation>1 </designation>     </NewDataSet>     <NewDataSet>         <EmpCode>EMP015</EmpCode>         <name>Devesh </name>         <DOB>12/24/1983 12:00:00 AM</DOB>         <age>31</age>         <location>Delhi</location>         <gender>Male</gender>         <StateID>1</StateID>         <CityID>1</CityID>         <designation>1</designation>     </NewDataSet> </EmpDetails>

Now i write a Procedure that read xml and return result in table format.

Create PROCEDURE usp_ReadXml (                 @xmlString NVARCHAR(MAX) = '<EmpDetails>     <NewDataSet>         <EmpCode>EMP014</EmpCode>         <name>Sharad</name>         <DOB>12/24/1978 12:00:00 AM</DOB>          <age>35</age>         <location>x</location>         <gender>Male</gender>         <StateID>1</StateID>         <CityID>1</CityID>         <designation>1 </designation>     </NewDataSet>     <NewDataSet>         <EmpCode>EMP015</EmpCode>         <name>Devesh </name>         <DOB>12/24/1983 12:00:00 AM</DOB>         <age>31</age>         <location>Delhi</location>         <gender>Male</gender>         <StateID>1</StateID>         <CityID>1</CityID>         <designation>1</designation>     </NewDataSet> </EmpDetails> '                 ) AS BEGIN                 DECLARE @xmlHandle INT                   EXEC sp_xml_preparedocument @xmlHandle OUTPUT,  @xmlString                   SELECT NAME,                                 Age,                                 Gender,                                 Location,                                 designation,                                 StateID,                                 CityID,                                 EmpCode,                                 DOB                 FROM OPENXML(@xmlHandle, '/EmpDetails/NewDataSet', 2) WITH (                                                 name NVARCHAR(100),                                                 age INT,                                                 gender VARCHAR(10),                                                 location VARCHAR(50),                                                 designation INT,                                                 StateID INT,                                                 CityID INT,                                                 EmpCode VARCHAR(50),                                                 DOB DATETIME                                                 )                   EXEC sp_xml_removedocument @xmlHandle END

Output is

Run this command "Exec usp_ReadXml" and now output is