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

0 comments:

Post a Comment