OpenXML For bulk Operations in SQL

In SQL Server, OpenXML is very powerful method for the XML data manipulation. This article shows that how to use the OpenXML for XML string. OpenXML is used to parse the XML in Rowset data form.

In this tutorial, i have created one XML string and saved the parsed data in Table variable. If XML data is present in table, then any operation can be performed.

DECLARE @friendsXML Varchar(600)

SET @friendsXML =
'


Santosh
Karemore
SAP
Pune


Manoranjan
Sahoo
IT Consultant
Tokyo


Subodh
Singh
JIRA
Jabalpur


Praveen
Meghwal
.NET
Ujjain


'

DECLARE @friends TABLE (
Id INT IDENTITY(1,1),
FName VARCHAR(30),
LName VARCHAR(30),
Technology VARCHAR(30),
Location VARCHAR(30)
)

DECLARE @XMLDocPointer INT

--Preprare the internal XML
EXEC sp_xml_preparedocument @XMLDOcPointer OUTPUT,
@friendsXML

--Insert the record in @friends table
INSERT INTO @friends (
FName,LName,Technology,Location)
SELECT
*
FROM OPENXML(@XMLDocPointer, '/Friends/Friend',2)
WITH
(
FName VARCHAR(30),
LName VARCHAR(30),
Technology VARCHAR(30),
Location VARCHAR(30)
)

-- Remove the pointer from memory
EXEC sp_xml_removedocument @XMLDocPointer

SELECT
Id ,FName,LName,Technology,Location
FROM @friends