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