XML DML (Data Modification Language) - SQL Server

Here is my example of XML modification language in SQL Server. First lets say we have a table like:

CREATE TABLE [dbo].[XmlModificationLanguageExample](
	[ID] [uniqueidentifier] NOT NULL,
	[XmlField] [xml] NULL,
 CONSTRAINT [PK_XmlModificationLanguageExample] PRIMARY KEY CLUSTERED 
(
	[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

Final goal is to have XML like:

<root>
	<Parent Name="IamFather" Kind="Father">
		<Value Name="Oldest" Type="Father" Value="True"/>
		<Value Name="Kids" Type="Children" Value="
				<root>
					<Parent Name="IamTheSonOfMyFather" Kind="Father">
						<Value Name="Oldest" Type="Father" Value="True"/>
						<Value Name="MyKids" Type="Geni.Files" Value="
								<root>
									<Children Kind="Male" Count="12" ChildName="Stanko">
										<Value Name="Name" Type="Male.Child" Value="48yrsOld"/>
									</Children>
								</root>
						" />
					</Parent>
				</root>
		" />
  </Parent>
</root>

First, lets insert XML like:

<root>
	<Parent Name="IamFather" Kind="Father">
		<Value Name="Oldest" Type="Father" Value="True"/>
		<Value Name="Kids" Type="Children" Value="" />
  </Parent>
</root>

INSERT INTO [dbo].[XmlModificationLanguageExample]
           ([ID]
           ,[XmlField])
     VALUES
           (NEWID()
           , '<root>
	<Parent Name="IamFather" Kind="Father">
		<Value Name="Oldest" Type="Father" Value="True"/>
		<Value Name="Kids" Type="Children" Value="" />
  </Parent>
</root>')

Now first I am gonna prepare XML which I will insert into the node:

<Value Name="Kids" Type="Children" Value="" />

DECLARE @FatherXML XML = '<root>
	<Parent Name="IamTheSonOfMyFather" Kind="Father">
		<Value Name="Oldest" Type="Father" Value="True"/>
		<Value Name="MyKids" Type="Geni.Files" Value="" />
  </Parent>
</root>';

DECLARE @Kids nvarchar(max) = '<root>
	<Children Kind="Male" Count="12" ChildName="Stanko">
		<Value Name="Name" Type="Male.Child" Value="48yrsOld"/>
	</Children>
</root>';

SET @FatherXML.modify('
    replace value of (
       /root/Parent[@Name="IamTheSonOfMyFather"]
	   /Value[@Name="MyKids"]/@Value)[1]
    with (sql:variable("@Kids") cast as xs:string?)
');

select @FatherXML;

Here notice that @Kids is of type nvarchar(max), where @FatherXML is of type XML Now that XML I will insert into main XML, and update the table in server:

DECLARE @FatherAsString NVARCHAR(MAX) = CONVERT(NVARCHAR(MAX), @FatherXML);

UPDATE XmlModificationLanguageExample
SET XmlField.modify('
    replace value of (
		/root/Parent[@Name="IamFather"]
	   /Value[@Name="Kids"]/@Value)[1]
    with (sql:variable("@FatherAsString") cast as xs:string?)
')
WHERE ID = '4D14CD89-002F-4985-B54E-CA2CC0DA6913';

select * from XmlModificationLanguageExample

Here notice that I am converting @FatherXML to NVARCHAR(MAX), and like this I updating attribute. Now, lets say that between nodes:

<Value Name="Oldest" Type="Father" Value="True"/>
<Value Name="Kids" Type="Children" Value="" />

I want to add new node:

<Value Name="Youngest" Type="Father" Value="True"/>

after "Oldest":

UPDATE XmlModificationLanguageExample
SET XmlField.modify('
    insert sql:variable("@YoungestXML")
    after (
        /root/Parent[@Name="IamFather"]
		/Value[@Name="Oldest"]
    )[1]
')
WHERE ID = '4D14CD89-002F-4985-B54E-CA2CC0DA6913';

select * from XmlModificationLanguageExample

Here is the whole SQL query:

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[XmlModificationLanguageExample]') AND type in (N'U'))
DROP TABLE [dbo].[XmlModificationLanguageExample]
GO

CREATE TABLE [dbo].[XmlModificationLanguageExample](
	[ID] [uniqueidentifier] NOT NULL,
	[XmlField] [xml] NULL,
 CONSTRAINT [PK_XmlModificationLanguageExample] PRIMARY KEY CLUSTERED 
(
	[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

DECLARE @ID uniqueidentifier = NEWID();

INSERT INTO [dbo].[XmlModificationLanguageExample]
           ([ID]
           ,[XmlField])
     VALUES
           (@ID
           , '<root>
	<Parent Name="IamFather" Kind="Father">
		<Value Name="Oldest" Type="Father" Value="True"/>
		<Value Name="Kids" Type="Children" Value="" />
  </Parent>
</root>')

DECLARE @FatherXML XML = '<root>
	<Parent Name="IamTheSonOfMyFather" Kind="Father">
		<Value Name="Oldest" Type="Father" Value="True"/>
		<Value Name="MyKids" Type="Geni.Files" Value="" />
  </Parent>
</root>';

DECLARE @Kids nvarchar(max) = '<root>
	<Children Kind="Male" Count="12" ChildName="Stanko">
		<Value Name="Name" Type="Male.Child" Value="48yrsOld"/>
	</Children>
</root>';

SET @FatherXML.modify('
    replace value of (
       /root/Parent[@Name="IamTheSonOfMyFather"]
	   /Value[@Name="MyKids"]/@Value)[1]
    with (sql:variable("@Kids") cast as xs:string?)
');

SELECT @FatherXML AS FatherXmlData;

DECLARE @FatherAsString NVARCHAR(MAX) = CONVERT(NVARCHAR(MAX), @FatherXML);

UPDATE XmlModificationLanguageExample
SET XmlField.modify('
    replace value of (
		/root/Parent[@Name="IamFather"]
	   /Value[@Name="Kids"]/@Value)[1]
    with (sql:variable("@FatherAsString") cast as xs:string?)
')
WHERE ID = @ID;

select * from XmlModificationLanguageExample

DECLARE @YoungestXML XML = '<Value Name="Youngest" Type="Father" Value="True"/>'

UPDATE XmlModificationLanguageExample
SET XmlField.modify('
    insert sql:variable("@YoungestXML")
    after (
        /root/Parent[@Name="IamFather"]
		/Value[@Name="Oldest"]
    )[1]
')
WHERE ID = @ID;

select * from XmlModificationLanguageExample