在 SQL Server 中使用 XML 的两个表中的公共字段 [英] Use a common field in two tables with XML in SQL Server
本文介绍了在 SQL Server 中使用 XML 的两个表中的公共字段的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
如何删除#XMLItm表中的冗余?
How delete the redundance in the table #XMLItm?
我的代码:
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;
在这段代码中，我想在两个表中加载一个公共字段 ID_LIST 的信息.如何从临时表 #XMLItm 中的 #XMLLst 加载 ID_LIST?
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?
我想从表 #XMLItm 中的 XML 中删除字段 ID_LIST 并从临时表 #XMLLst 中获取 ID_LIST；
I want delete the field ID_LIST from XML in the table #XMLItm and take a ID_LIST from temp table #XMLLst;
功能:
在#XMLLst 表中加载一个包含您的 id(自动增量)、名称和备注的列表，并从 XML 导出到 SQL.
In the table #XMLLst load a list whit your id (auto increment), name and remarks and export from XML to SQL.
在#XMLItm 表中加载一个与 id 列表链接的项目.
In the table #XMLItm load a item linked with a id list.
预期输出
+-----------+---------------+---------+
|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
推荐答案
请尝试以下解决方案.它在 XML 中将 list 与子 item 片段连接起来.此外，它还在两个级别上生成两个运行序列.
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);
输出
+----------------+------------------+-----------+---------+---------+------+-------------+-------+---------+
| 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 |
+----------------+------------------+-----------+---------+---------+------+-------------+-------+---------+
这篇关于在 SQL Server 中使用 XML 的两个表中的公共字段的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
查看全文
