Parsing XML in SQL - OPENXML

We can parse XML Documents from SQL using OPENXML. Before that we use an sp named sp_xml_preparedocument.

EXEC sp_xml_preparedocument @doc OUTPUT, @doc

SELECT EmployeeID,EmployeeName
FROM   OPENXML (@doc, '/EmployeeList/Employee')
WITH
   (
      EmployeeID  varchar(10),
      EmployeeName varchar(150)
   )

We can even use INNER JOIN or many other queries on OPEN XML.

For bulk data updation or insertion this query is very useful to parse the input XML Data.