Advertisements
Home > XML > Using DMLs with XMLs – INSERT (SQL Server)

Using DMLs with XMLs – INSERT (SQL Server)


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.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: