⟩ Do you know what is OPENXML in SQL Server?
-OPENXML provides an easy way to use an XML document as a data-source for your procedures.
-OPENXML data can be manipulated the same way we deal with database tables by treating xml tags in the form of columns and the value in the form of rows.
-By using OPENXML Data can be inserted or updated very quickly without multiple trips to the database.
-Example:
DECLARE @count int
DECLARE @xml varchar(5000)
SET @xml ='<Employees>
<Employee id="1">
<Name>DEF</Name>
<Employee >1234</ Employee >
</Employee >
<Employee id="2">
<Name>ABC</Name>
<PhoneNo>2211</PhoneNo>
</Employee >
</Employees>'
EXEC sp_xml_preparedocument @count OUTPUT, @xml
SELECT *
FROM OPENXML (@count, Employees/Employee')
WITH (id Varchar(10), Name varchar(100) 'Name' , PhoneNo Varchar(50) 'PhoneNo')
EXEC sp_xml_removedocument @index
It will give following result.
1 DEF 1234
2 ABC 2211
