Use of OPENXML in SQL Server

n 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:
1 DECLARE @friendsXML Varchar(600)
2
3 SET @friendsXML =
4 '
5
6
7 Santosh
8 Karemore
9 SAP
10 Pune
11
12
13 Manoranjan
14 Sahoo
15 IT Consultant
16 Tokyo
17
18
19 Subodh
20 Singh
21 JIRA
22 Jabalpur
23
24
25 Praveen
26 Meghwal
27 .NET
28 Ujjain
29
30
31 '
32
33 DECLARE @friends TABLE (
34 Id INT IDENTITY(1,1),
35 FName VARCHAR(30),
36 LName VARCHAR(30),
37 Technology VARCHAR(30),
38 Location VARCHAR(30)
39 )
40
41 DECLARE @XMLDocPointer INT
42
43 --Preprare the internal XML
44 EXEC sp_xml_preparedocument @XMLDOcPointer OUTPUT,
45 @friendsXML
46
47 --Insert the record in @friends table
48 INSERT INTO @friends (
49 FName,LName,Technology,Location)
50 SELECT
51 *
52 FROM OPENXML(@XMLDocPointer, '/Friends/Friend',2)
53 WITH
54 (
55 FName VARCHAR(30),
56 LName VARCHAR(30),
57 Technology VARCHAR(30),
58 Location VARCHAR(30)
59 )
60
61 -- Remove the pointer from memory
62 EXEC sp_xml_removedocument @XMLDocPointer
63
64 SELECT
65 Id ,FName,LName,Technology,Location
66 FROM @friends

Output:
OpenXML in SQL Server

OpenXML in SQL Server