英文:
Have to map XML data into a SQL table?
问题
我正在我的存储过程中获取XML数据，并希望通过映射值来更新相应的SQL Server表格。
这是XML的示例：
DECLARE @xmlData XML = '<NewDataSet>
<Table>
<SystemKey>India</SystemKey>
<Translation>Bharat</Translation>
</Table>
<Table>
<SystemKey>UAE</SystemKey>
<Translation>United Arab Emirates</Translation>
</Table>
<Table>
<SystemKey>Afghanistan</SystemKey>
<Translation>Afghanistan</Translation>
</Table>
</NewDataSet>';
代码如下：
SELECT
t.c.value('(SystemKey/text())[1]', 'NVARCHAR(MAX)') AS [SystemKey],
r.c.value('text()[1]', 'NVARCHAR(MAX)') AS Translation
FROM
@xmlData.nodes('NewDataSet/Table') AS t(c)
CROSS APPLY
t.c.nodes('*[local-name(.) != "SystemKey"]') AS r(c)
我想要使用XML数据的值直接更新表格2；例如，SystemKey = Table2.KEY，并且Translation = Table2.Translation。
我可以直接做到这一点，而不需要使用临时表和游标吗？
英文:
I'm getting XML data in my stored procedure and want to update the corresponding SQL Server table by mapping the values.
This is a sample of the XML:
DECLARE @xmlData XML = '<NewDataSet>
<Table>
<SystemKey>India</SystemKey>
<Translation>Bharat</Translation>
</Table>
<Table>
<SystemKey>UAE</SystemKey>
<Translation>United Arab Emirates</Translation>
</Table>
<Table>
<SystemKey>Afghanistan</SystemKey>
<Translation>Afghanistan</Translation>
</Table>
</NewDataSet>'
Code is as follows:
SELECT
t.c.value('(SystemKey/text())[1]', 'NVARCHAR(MAX)') AS [SystemKey],
r.c.value('text()[1]', 'NVARCHAR(MAX)') AS Translation
FROM
@xmldata.nodes('NewDataSet/Table') AS t(c)
CROSS APPLY
t.c.nodes('*[local-name(.) != "SystemKey"]') AS r(c)
I want to update table 2 with the values from XML data; eg. SystemKey = Table2.KEY AND Translation = Table2.Translation.
Can I do this directly without a temp table and a cursor?
答案1
得分: 0
这是您想要的吗？（我不确定您尝试实现的更新类型是什么。如果您想要插入或更新，您可以使用合并操作来完成相同的操作）。
DECLARE @xmlData XML = '<NewDataSet>
<Table>
<SystemKey>India</SystemKey>
<Translation>Bharat</Translation>
</Table>
<Table>
<SystemKey>UAE</SystemKey>
<Translation>United Arab Emirates</Translation>
</Table>
<Table>
<SystemKey>Afghanistan</SystemKey>
<Translation>Afghanistan</Translation>
</Table>
</NewDataSet>';
WITH Translations
AS
(
SELECT
t.c.value('(SystemKey/text())[1]', 'NVARCHAR(MAX)') AS [SystemKey],
r.c.value('text()[1]', 'NVARCHAR(MAX)') AS Translation
FROM
@xmlData.nodes('NewDataSet/Table') AS t(c)
CROSS APPLY
t.c.nodes('*[local-name(.) != "SystemKey"]') AS r(c)
)
UPDATE t2
SET t2.Translation = t.Translation
FROM Table2 AS t2
INNER JOIN Translations AS t
ON t.SystemKey = t2.SystemKey;
英文:
Is this what you're after? (I wasn't sure what type of update you were trying to achieve. You could do the same with a merge if you want an insert or update).
DECLARE @xmlData XML = '<NewDataSet>
<Table>
<SystemKey>India</SystemKey>
<Translation>Bharat</Translation>
</Table>
<Table>
<SystemKey>UAE</SystemKey>
<Translation>United Arab Emirates</Translation>
</Table>
<Table>
<SystemKey>Afghanistan</SystemKey>
<Translation>Afghanistan</Translation>
</Table>
</NewDataSet>';
WITH Translations
AS
(
SELECT
t.c.value('(SystemKey/text())[1]', 'NVARCHAR(MAX)') AS [SystemKey],
r.c.value('text()[1]', 'NVARCHAR(MAX)') AS Translation
FROM
@xmldata.nodes('NewDataSet/Table') AS t(c)
CROSS APPLY
t.c.nodes('*[local-name(.) != "SystemKey"]') AS r(c)
)
UPDATE t2
SET t2.Translation = t.Translation
FROM Table2 AS t2
INNER JOIN Translations AS t
ON t.SystemKey = t2.SystemKey;
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论