How do I add a node or new element in SQL Server using XQuery and the xml datatype

You can achieve this with code something like this:

-- declare a table variable to hold the data
DECLARE @table TABLE (ID INT IDENTITY PRIMARY KEY, XmlContent XML)

-- insert your XML into that table variable
INSERT INTO @table(XmlContent) VALUES(@Settingsxml)

-- define the relevant XML namespaces and UPDATE the table
;WITH XMLNAMESPACES('http://schemas.microsoft.com/winfx/2006/xaml/presentation' AS ns,
'clr-namespace:System;assembly=mscorlib' AS sys,
'http://schemas.microsoft.com/winfx/2006/xaml' as x)
UPDATE @table
SET XmlContent.modify('insert <sys:Int32 x:Key="def">300</sys:Int32> after (/ns:ResourceDictionary/sys:Int32[@x:Key="abc"])[1]')
WHERE ID = 1

-- now, your XML stored in the table variable contains the new element where you wanted to have it
SELECT XmlContent FROM @table

The output in the end is:

<ResourceDictionary 
       xmlns="http://schemas.microsoft.com/winfx/2006/xaml/presentation" 
       xmlns:x="http://schemas.microsoft.com/winfx/2006/xaml" 
       xmlns:sys="clr-namespace:System;assembly=mscorlib">
  <sys:Int32 x:Key="abc">1200</sys:Int32>
  <sys:Int32 x:Key="def">300</sys:Int32>
  <sys:Int32 x:Key="xyz">300</sys:Int32>
  <sys:Int32 x:Key="ghi">300</sys:Int32>
  <sys:String x:Key="def">Forgot your login or password? Please contact the IT Dept.</sys:String>
</ResourceDictionary>

Same answer as given by marc_s but using set on XML variable instead of update on table variable. Can't use with xmlnamespaces here so ...

set @Settingsxml.modify('declare default element namespace "http://schemas.microsoft.com/winfx/2006/xaml/presentation";
                         declare namespace x="http://schemas.microsoft.com/winfx/2006/xaml"; 
                         declare namespace sys="clr-namespace:System;assembly=mscorlib";
                         insert <sys:Int32 x:Key="def">300</sys:Int32> 
                         after (/ResourceDictionary/sys:Int32[@x:Key="abc"])[1]')

I found out how to query if the "abc" node exists plus how to add "def" after "abc" Here is the entire code below

DECLARE @abc varchar(max),@Settingsxml XML,@DoesDefExist varchar(10)

SELECT @Settingsxml='<ResourceDictionary xmlns="http://schemas.microsoft.com/winfx/2006/xaml/presentation" xmlns:x="http://schemas.microsoft.com/winfx/2006/xaml" xmlns:sys="clr-namespace:System;assembly=mscorlib">
  <sys:Int32 x:Key="abc">1200</sys:Int32>
  <sys:Int32 x:Key="xyz">300</sys:Int32>
  <sys:Int32 x:Key="ghi">300</sys:Int32>
  <sys:String x:Key="def">Forgot your login or password? Please contact the IT Dept.</sys:String>
</ResourceDictionary>'  


SELECT @[email protected]('declare namespace hrn="http://schemas.microsoft.com/winfx/2006/xaml/presentation";
                          declare namespace x="http://schemas.microsoft.com/winfx/2006/xaml";
                          declare namespace sys="clr-namespace:System;assembly=mscorlib";
                          /hrn:ResourceDictionary/sys:Int32/@x:Key="def"','varchar(10)')

IF (@DoesDefExist='false')
BEGIN
SET @Settingsxml.modify('declare namespace hrn="http://schemas.microsoft.com/winfx/2006/xaml/presentation";
                          declare namespace x="http://schemas.microsoft.com/winfx/2006/xaml";
                          declare namespace sys="clr-namespace:System;assembly=mscorlib";
                          insert <sys:Int32 x:Key="def">300</sys:Int32> after
                          (/hrn:ResourceDictionary/sys:Int32[@x:Key="abc"])[1]')
END       

SELECT     @Settingsxml