SQL Server 2022

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE YourTable (Xml1 xml, Xml2 xml);

INSERT YourTable VALUES (N'<HOME>
    <VALIDITYLIST>
        <VALIDITY STATE="1">
            <VALIDITYTYPE>1</VALIDITYTYPE>
            <GROUPCODE>DEFAULT</GROUPCODE>
            <ENTRY/>
            <CARD>2</CARD>
            <GIFTAID/>
            <VARIABLERANGE>false</VARIABLERANGE>
            <DAYS>365</DAYS>
            <NOTOPERATING>false</NOTOPERATING>
            <VALIDITYLIST/>
            <YPERESTRICTIONLIST/>
            <METRALOCKERV2>
                <LOCKERITEMID/>
            </METRALOCKERV2>
            <REQUIREDVAREXPDATE/>
        </VALIDITY>
    </VALIDITYLIST>
</HOME>
',
N'<HOME>
    <VALIDITYLIST>
        <VALIDITY STATE="1">
            <VALIDITYTYPE>1</VALIDITYTYPE>
            <GROUPCODE>DEFAULT</GROUPCODE>
            <GIFTAID/>
            <DYNAMICP/>
            <VALIDITYLIST>
                <VALIDITY STATE="1">
                    <VALIDITYTYPE>2</VALIDITYTYPE>
                    <EVENT>3</EVENT>
                    <ENTRYTYPE>2</ENTRYTYPE>
                    <NUMENTRY>1</NUMENTRY>
                </VALIDITY>
            </VALIDITYLIST>
        </VALIDITY>
    </VALIDITYLIST>
</HOME>
');

1 rows affected

Xml1	Xml2	NewXml
<?xml version="1.0" encoding="utf-16"?> <HOME> <VALIDITYLIST> <VALIDITY STATE="1"> <VALIDITYTYPE>1</VALIDITYTYPE> <GROUPCODE>DEFAULT</GROUPCODE> <ENTRY /> <CARD>2</CARD> <GIFTAID /> <VARIABLERANGE>false</VARIABLERANGE> <DAYS>365</DAYS> <NOTOPERATING>false</NOTOPERATING> <VALIDITYLIST /> <YPERESTRICTIONLIST /> <METRALOCKERV2> <LOCKERITEMID /> </METRALOCKERV2> <REQUIREDVAREXPDATE /> </VALIDITY> </VALIDITYLIST> </HOME>	<?xml version="1.0" encoding="utf-16"?> <HOME> <VALIDITYLIST> <VALIDITY STATE="1"> <VALIDITYTYPE>1</VALIDITYTYPE> <GROUPCODE>DEFAULT</GROUPCODE> <GIFTAID /> <DYNAMICP /> <VALIDITYLIST> <VALIDITY STATE="1"> <VALIDITYTYPE>2</VALIDITYTYPE> <EVENT>3</EVENT> <ENTRYTYPE>2</ENTRYTYPE> <NUMENTRY>1</NUMENTRY> </VALIDITY> </VALIDITYLIST> </VALIDITY> </VALIDITYLIST> </HOME>	<?xml version="1.0" encoding="utf-16"?> <HOME> <VALIDITYLIST> <VALIDITY> <VALIDITYTYPE>1</VALIDITYTYPE> <GROUPCODE>DEFAULT</GROUPCODE> <CARD>2</CARD> <VARIABLERANGE>false</VARIABLERANGE> <DAYS>365</DAYS> <NOTOPERATING>false</NOTOPERATING> <VALIDITYLIST> <VALIDITY STATE="1"> <VALIDITYTYPE>2</VALIDITYTYPE> <EVENT>3</EVENT> <ENTRYTYPE>2</ENTRYTYPE> <NUMENTRY>1</NUMENTRY> </VALIDITY> </VALIDITYLIST> </VALIDITY> </VALIDITYLIST> </HOME>