Generate increment in field ID_LIST XML in SQL Server

Question

How can I generate an auto increment in this example for the id_list field:

BEGIN TRY
    DECLARE @xml XML= '<?xml version="1.0" encoding="iso-8859-1"?>
                <lists>
                    -- I need delete id and generate auto the autoincrement
                    <list id="1" list="LIST A" remark="xx">
                        <item id_option="1" id_list="1" code="Y" description="Yes" order="1" enabled="1" />
                        <item id_option="2" id_list="1" code="N" description="No" order="2" enabled="1" />
                    </list>
                    <list id="2" list="LIST B" remark="yy">
                        <item id_option="3" id_list="2" code="E" description="Enabled" order="1" enabled="1" />
                        <item id_option="4" 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 
                  Ssn.value('(/list/@id)[1]', 'int') 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
         );
    DROP TABLE #XMLLst;

I need delete the field id from xml and generate auto the id.

The xml load 2 elements with id, name and remarks, the table can't modify and the field Id_table dont´t have autoincrement.

Answer 1

Here is how to generate an auto-increment ID column from the XML.

I modified the @id attribute values as follows: 10 and 20.

ROW_NUMBER() window function generates what you are after.

I didn't deal with the MERGE statement. it is up to you.

SQL

-- DDL and sample data population, start
DECLARE @xml XML= 
'<?xml version="1.0" encoding="iso-8859-1"?>
<lists>-- I need delete id and generate auto the autoincrement
    <list id="10" list="LIST A" remark="xx">
        <item id_option="1" id_list="1" code="Y" description="Yes" order="1"
              enabled="1"/>
        <item id_option="2" id_list="1" code="N" description="No" order="2"
              enabled="1"/>
    </list>
    <list id="20" list="LIST B" remark="yy">
        <item id_option="3" id_list="2" code="E" description="Enabled" order="1"
              enabled="1"/>
        <item id_option="4" id_list="2" code="D" description="Disabled"
              order="2" active="1"/>
    </list>
</lists>';

DECLARE @XMLLst TABLE ([ID_LIST] INT NOT NULL, [NAME_LIST]  VARCHAR(250) NOT NULL, [REMARKS] VARCHAR(8000));
-- DDL and sample data population, end

INSERT INTO @XMLLst (ID_LIST, NAME_LIST, REMARKS)
SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS ID_LIST
    --, c.value('@id', 'INT') AS ID
    , c.value('@list', 'VARCHAR(250)') AS NAME_LIST
    , c.value('@remark', 'VARCHAR(8000)') AS REMARKS
FROM @xml.nodes('/lists/list') AS t(c);

-- test
SELECT * FROM @XMLLst;

Output

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