Tuesday, January 13, 2009

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')
      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.


Post a Comment