Use of OPENXML in SQL Server

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.

Source code:

DECLARE @friendsXML Varchar(600)

SET @friendsXML =
'
<Friends>
<Friend>
<FName>Santosh</FName>
<LName>Karemore</LName>
<Technology>SAP</Technology>
<Location>Pune</Location>
</Friend>
<Friend>
<FName>Manoranjan</FName>
<LName>Sahoo</LName>
<Technology>IT Consultant</Technology>
<Location>Tokyo</Location>
</Friend>
<Friend>
<FName>Subodh</FName>
<LName>Singh</LName>
<Technology>JIRA</Technology>
<Location>Jabalpur</Location>
</Friend>
<Friend>
<FName>Praveen</FName>
<LName>Meghwal</LName>
<Technology>.NET</Technology>
<Location>Ujjain</Location>
</Friend>
</Friends>
'

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

Output:

OpenXML in SQL Server
OpenXML in SQL Server

Posted

in

by

Tags:


Related Posts

Comments

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Discover more from Jitendra Zaa

Subscribe now to keep reading and get access to the full archive.

Continue Reading