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>
1 rows affected
SELECT *,
NewXml = (
SELECT
VALIDITYLIST = (
SELECT
ISNULL(x1.query('.'), x2.query('.'))
FROM
t.Xml1.nodes('HOME/VALIDITYLIST/VALIDITY/*[.//text()]') x1(x1)
FULL JOIN
t.Xml2.nodes('HOME/VALIDITYLIST/VALIDITY/*[.//text()]') x2(x2)
ON x2.x2.value('local-name(.)','nvarchar(30)') = x1.x1.value('local-name(.)','nvarchar(30)')
FOR XML PATH(''), ROOT('VALIDITY'), TYPE
)
FOR XML PATH(''), ROOT('HOME'), TYPE
)
FROM YourTable t;
| 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> |