Use a common field in two tables with XML in SQL Server

How delete the redundance in the table #XMLItm?

My code:

  BEGIN TRY
    DECLARE @xml XML= '<?xml version="1.0" encoding="iso-8859-1"?>
                <lists>
                    <list list="LIST A" remark="xx">
                        <item id_list="1" code="Y" description="Yes" order="1" enabled="1" />
                        <item id_list="1" code="N" description="No" order="2" enabled="1" />
                    </list>
                    <list list="LIST B" remark="yy">
                        <item id_list="2" code="E" description="Enabled" order="1" enabled="1" />
                        <item id_list="2" code="D" description="Disabled" order="2" active="1" />
                    </list>
                </lists>';
    CREATE TABLE #XMLLst
    (
        [ID_LIST]      INT NOT NULL, 
        [NAME_LIST]  VARCHAR(250) NOT NULL, 
        [REMARKS] VARCHAR(8000)
    );
    INSERT INTO #XMLLst
    (
        ID_LIST, 
        NAME_LIST, 
        REMARKS
    )
           SELECT DISTINCT 
                  ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS ID_LIST,
                  Ssn.value('(/list/@list)[1]', 'Varchar(250)') AS NAME_LIST, 
                  Ssn.value('(/list/@remark)[1]', 'varchar(8000)') AS REMARKS
           FROM
           (
               SELECT S.s.query('.') AS Ssn
               FROM @xml.nodes('/lists/list') AS S(s)
           ) AS SSnes;
    WITH CTE_DATOS_ORIGEN
         AS (SELECT DISTINCT 
                    ID_LIST, 
                    NAME_LIST, 
                    REMARKS
             FROM #XMLLst)
         MERGE INTO [sos].[LIST] LIST
         USING CTE_DATA_ORIGIN
         ON LIST.ID_LIST = CTE_DATA_ORIGIN.ID_LIST
             WHEN MATCHED AND CTE_DATA_ORIGIN.NAME_LIST = LIST.NAME_LIST
             THEN UPDATE SET 
                             LIST.NAME_LIST = CTE_DATA_ORIGIN.NAME_LIST, 
                             LIST.REMARKS = CTE_DATA_ORIGIN.REMARKS
             WHEN NOT MATCHED
             THEN
               INSERT(ID_LIST, 
                      NAME_LIST, 
                      REMARKS)
               VALUES
         (
            CTE_DATA_ORIGIN.ID_LIST, 
            CTE_DATA_ORIGIN.NAME_LIST, 
            CTE_DATA_ORIGIN.REMARKS
         );

    CREATE TABLE #XMLItm
    (
        [ID_OPTION_LIST] INT NOT NULL, 
        [ID_LIST]        INT NOT NULL, 
        [CODE]          VARCHAR(50) NOT NULL, 
        [DESCRIPTION]     VARCHAR(1000) NOT NULL, 
        [ORDER]           INT NOT NULL, 
        [ENABLED]          BIT
    );
    INSERT INTO #XMLItm
    (
        ID_OPTION_LIST, 
        ID_LIST, 
        CODE, 
        DESCRIPTION, 
        ORDER, 
        ENABLED
    )
           SELECT DISTINCT 
                  ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS ID_OPTION_LIST,
                  -- I want delete this option and load the ID_LIST from #XMLLst
                  Ssn.value('(/item/@id_list)[1]', 'int') AS ID_LIST, 
                  Ssn.value('(/item/@code)[1]', 'varchar(50)') AS CODE, 
                  Ssn.value('(/item/@description)[1]', 'varchar(1000)') AS DESCRIPTION, 
                  Ssn.value('(/item/@order)[1]', 'int') AS ORDER, 
                  Ssn.value('(/item/@enabled)[1]', 'int') AS ENABLED
           FROM
           (
               SELECT S.s.query('.') AS Ssn
               FROM @xml.nodes('/lists/list/item') AS S(s)
           ) AS SSnes;
    WITH CTE_DATA_ORIGIN
         AS (SELECT DISTINCT 
                    ID_OPCION_LIST, 
                    ID_LIST, 
                    CODE, 
                    DESCRIPTION, 
                    ORDER, 
                    ENABLED
             FROM #XMLItm)
         MERGE INTO [sos].[ITEM_LIST] ITEM
         USING CTE_DATA_ORIGIN
         ON ITEM.ID_OPTION_LIST = CTE_DATA_ORIGIN.ID_OPTION_LIST
            AND ITEM.ID_LIST = CTE_DATA_ORIGIN.ID_LIST
             WHEN MATCHED AND CTE_DATA_ORIGIN.CODE = ITEM.CODE
             THEN UPDATE SET 
                             ITEM.DESCRIPTION = CTE_DATA_ORIGIN.DESCRIPTION, 
                             ITEM.ORDER = CTE_DATA_ORIGIN.ORDER, 
                             ITEM.ENABLED = CTE_DATA_ORIGIN.ENABLED
             WHEN NOT MATCHED
             THEN
               INSERT(ID_OPTION_LIST, 
                      ID_LIST, 
                      CODE, 
                      DESCRIPTION, 
                      ORDER, 
                      ENABLED)
               VALUES
         (
            CTE_DATOS_ORIGEN.ID_OPTION_LIST, 
            CTE_DATOS_ORIGEN.ID_LIST, 
            CTE_DATOS_ORIGEN.CODE, 
            CTE_DATOS_ORIGEN.DESCRIPTION, 
            CTE_DATOS_ORIGEN.ORDER, 
            CTE_DATOS_ORIGEN.ENABLED
         );
    DROP TABLE #XMLLst;
    DROP TABLE #XMLItm;

In this code I want load information in two tables for a common field ID_LIST. How load the ID_LIST from #XMLLst in the temp table #XMLItm?

I want delete the field ID_LIST from XML in the table #XMLItm and take a ID_LIST from temp table #XMLLst;

Function:

In the table #XMLLst load a list whit your id (auto increment), name and remarks and export from XML to SQL.

In the table #XMLItm load a item linked with a id list.

OUTPUT EXPECTED

+-----------+---------------+---------+
|ID_LIST    |    NAME_LIST  | REMARKS |
|1          |LIST A         | xx      |
|2          |LIST B         | yy      |
+-------------------------------------+

ID_OPTION_LIST  ID_LIST     CODE    DESCRIPTION ORDER   ENABLED
1               1           Y       yes         1       1
2               1           N       No          2       1
3               2           E       Enabled     1       1
4               2           D       Disables    2       1

Answer

Please try the following solution. It joins list with child item fragments in the XML. Additionally, it generates two running sequences on both levels.

SQL

-- DDL and sample data population, start
DECLARE @xml XML = 
'<?xml version="1.0" encoding="iso-8859-1"?>
<lists>
    <list list="LIST A" remark="xx">
        <item id_list="1" code="Y" description="Yes" order="1" enabled="1" />
        <item id_list="1" code="N" description="No" order="2" enabled="1" />
    </list>
    <list list="LIST B" remark="yy">
        <item id_list="2" code="E" description="Enabled" order="1" enabled="1" />
        <item id_list="2" code="D" description="Disabled" order="2" active="1" />
    </list>
</lists>';
-- DDL and sample data population, end

-- It seems exactly waht you need
SELECT ROW_NUMBER() OVER (ORDER BY i) AS ID_OPTION_LIST
    , DENSE_RANK() OVER (ORDER BY c) AS ID_LIST_Sequence
    , c.value('@list', 'VARCHAR(250)') AS NAME_LIST
    , c.value('@remark', 'VARCHAR(8000)') AS REMARKS
    , i.value('@id_list', 'int') AS ID_LIST
    , i.value('@code', 'varchar(50)') AS CODE 
    , i.value('@description', 'varchar(1000)') AS DESCRIPTION
    , i.value('@order', 'int') AS [ORDER]
    , i.value('@enabled', 'int') AS ENABLED
FROM @xml.nodes('/lists/list') AS t(c)
    CROSS APPLY t.c.nodes('item') AS x(i);

Output

+----------------+------------------+-----------+---------+---------+------+-------------+-------+---------+
| ID_OPTION_LIST | ID_LIST_Sequence | NAME_LIST | REMARKS | ID_LIST | CODE | DESCRIPTION | ORDER | ENABLED |
+----------------+------------------+-----------+---------+---------+------+-------------+-------+---------+
|              1 |                1 | LIST A    | xx      |       1 | Y    | Yes         |     1 | 1       |
|              2 |                1 | LIST A    | xx      |       1 | N    | No          |     2 | 1       |
|              3 |                2 | LIST B    | yy      |       2 | E    | Enabled     |     1 | 1       |
|              4 |                2 | LIST B    | yy      |       2 | D    | Disabled    |     2 | NULL    |
+----------------+------------------+-----------+---------+---------+------+-------------+-------+---------+