Merge two XML columns in SQL Server

Question

Hope, someone out there can help me with SQL Query, I am trying to merge two XML column, I am currently using SQL Azure 2019.

First XML

<HOME>
    <VALIDITYLIST>
        <VALIDITY STATE="1">
            <VALIDITYTYPE>1</VALIDITYTYPE>
            <GROUPCODE>DEFAULT</GROUPCODE>
            <ENTRY/>
            <CARD>2</CAR>
            <GIFTAID/>
            <VARIABLERANGE>false</VARIABLERANGE>
            <DAYS>365</DAYS>
            <NOTOPERATING>false</NOTOPERATING>
            <VALIDITYLIST/>
            <YPERESTRICTIONLIST/>
            <METRALOCKERV2>
                <LOCKERITEMID/>
            </METRALOCKERV2>
            <REQUIREDVAREXPDATE/>
        </VALIDITY>
    </VALIDITYLIST>
</HOME>

Second XML

<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>

Somehow SQL merge two xmls and return only nodes where values are present. something like.

<HOME>
    <VALIDITYLIST>
        <VALIDITY>
            <VALIDITYTYPE>1</VALIDITYTYPE>
            <GROUPCODE>DEFAULT</GROUPCODE>
            <CARD>2</CAR>
            <VARIABLERANGE>false</VARIABLERANGE>
            <DAYS>365</DAYS>
            <NOTOPERATING>false</NOTOPERATING>
            <VALIDITYLIST>
                <VALIDITY>
                    <VALIDITYTYPE>2</VALIDITYTYPE>
                    <EVENT>3</EVENT>
                    <ENTRYTYPE>2</ENTRYTYPE>
                    <NUMENTRY>1</NUMENTRY>
                </VALIDITY>
            </VALIDITYLIST>
        </VALIDITY>
    </VALIDITYLIST>
</HOME> 

Thanks guys for sharing your thought [edited part below]

I still would like to handle within SQL. Since Nodes are fixed, i am thinking of creating two tables by reading individual XML's and then depending on the values, i'll construct new XML, now i am stuck on first part, reading the XML. This is what i come up and i should expect to see value of 1 and 2 when reading 2nd XML but returning NULL's, can you see what i am doing wrong here?

select @XML2.value('(/HOME/VALIDITYTYPE/node())[1]', 'nvarchar(max)') as VALIDITYTYPE
, @XML2.value('(/HOME/VALIDITYLIST/VALIDITYLIST/VALIDITYTYPE/node())[1]', 'nvarchar(max)') as VALIDITYTYPE

Answer 1

Yes, this is possible in pure SQL, using XQuery and full-joining the two XMLs, then reconstructing it using FOR XML

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;

The predicate *[.//text()] checks that the current node has any descendant text element at any depth.

We join by local-name(.) the name of the current node, and take the first column's result if available, otherwise the second. FOR XML does not add a node name if the column is unnamed.

This all assumes you always have exactly one HOME/VALIDITYLIST/VALIDITY node, otherwise it's more complicated.

db<>fiddle

Answer 2

One more way by using XQuery FLWOR expression.

It simulates a LEFT OUTER JOIN by using col1 XML column as a base.

It needs to be tested to cover all edge cases.

SQL

DECLARE @tbl TABLE (ID INT IDENTITY PRIMARY KEY, col1 XML, col2 XML);
INSERT @tbl (col1, col2) 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>');

SELECT t.ID, x
    , x.query('<HOME><VALIDITYLIST><VALIDITY>
    {
        for $x in /root/HOME[1]/VALIDITYLIST/VALIDITY/*
        let $y := /root/HOME[2]/VALIDITYLIST/VALIDITY/*[local-name(.)=local-name($x)]
        return if ($x[.//text()]) then $x
            else $y
    }
    </VALIDITY></VALIDITYLIST></HOME>') AS result
FROM @tbl AS t
    CROSS APPLY (SELECT TRY_CAST('<root>' + 
        TRY_CAST(col1 AS NVARCHAR(MAX)) +
        TRY_CAST(col2 AS NVARCHAR(MAX)) +
    '</root>' AS XML)) AS t1(x);

Output

<HOME>
  <VALIDITYLIST>
    <VALIDITY>
      <VALIDITYTYPE>1</VALIDITYTYPE>
      <GROUPCODE>DEFAULT</GROUPCODE>
      <CARD>2</CARD>
      <GIFTAID />
      <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>