SQL with Manoj

In my previous XML related posts I wrote about how can we query and SELECT XML strings & pull data from parent-child nodes and hierarchies. In this post we’ll see how we can modify that XML data by applying DMLs to it.

In this part let’s see how we can INSERT new records in a XML string:

DECLARE @xml XML

-- Creating sample XML string with single row:
SET @xml=N'<Root>
    <stud id="1">
		<fname>Manoj</fname>
		<lname>Pandey</lname>
		<class>10</class>
		<marks>80.5</marks>
	</stud>
</Root>';

-- Now check the sample row by querying it with SELECT statement:
SELECT
	Tab.Col.value('@id','int') AS ID,
	Tab.Col.value('fname[1]','nvarchar(20)') AS FirstName,
	Tab.Col.value('lname[1]','nvarchar(20)') AS LastName,
	Tab.Col.value('class[1]','int') AS class,
	Tab.Col.value('marks[1]','float') AS Marks
FROM @xml.nodes('/Root/stud') Tab(Col)

Output:-
ID FirstName LastName class Marks
1 Manoj Pandey 10 80.5
DMLs in XML provides a common function xml.modify() to modify the XML string in 3 ways, i.e. INSERT, UPDATE & DELETE. Here we will see how we can INSERT new records at different positions in the sample XML created above:

--// Now INSERTing some more records:

-- 1. INSERT (basic)
SET @xml.modify('
insert <stud id="2">
		<fname>Saurabh</fname>
		<lname>Sharma</lname>
		<class>11</class>
		<marks>82.7</marks>
	</stud>
into (/Root)[1]');

-- 2. INSERT at beginning, first record
SET @xml.modify('
insert <stud id="0">
		<fname>Kanchan</fname>
		<lname>Pandey</lname>
		<class>10</class>
		<marks>90.5</marks>
	</stud>
as first
into (/Root)[1]');

-- 3. INSERT at end, last record
SET @xml.modify('
insert <stud id="3">
		<fname>Rajesh</fname>
		<lname>Shah</lname>
		<class>11</class>
		<marks>70.3</marks>
	</stud>
as last
into (/Root)[1]');

-- Let's check the records just inserted:
SELECT
	Tab.Col.value('@id','int') AS ID,
	Tab.Col.value('fname[1]','nvarchar(20)') AS FirstName,
	Tab.Col.value('lname[1]','nvarchar(20)') AS LastName,
	Tab.Col.value('class[1]','int') AS class,
	Tab.Col.value('marks[1]','float') AS Marks
FROM @xml.nodes('/Root/stud') Tab(Col)

Output:-
ID FirstName LastName class Marks
0 Kanchan Pandey 10 90.5
1 Manoj Pandey 10 80.5
2 Saurabh Sharma 11 82.7
3 Rajesh Shah 11 70.3

-- Check final XML with INSERTed records above:
SELECT @xml

In my next XML-DML post we will see UPDATE and DELETE operations.