Sql Server - 捕获 XML 查询并将其保存到表中？

发布于 2024-08-04 03:01:32 字数 605 浏览 2 评论 0原文

我想运行一个（广泛的）查询来生成一行 XML。此 XML 表示大约 12 个表的关系数据。当我从顶级表中“删除”一行时，我想“捕获”当时数据的状态（以 XML 形式），将其保存到存档表中，然后删除所有子表数据，最后标记顶级表/行为“isDeleted=1”。我们还有其他数据挂在父表上，这些数据无法被删除，并且不能失去与顶级表的关系。

我已经解决了大部分 XML - 请参阅我的帖子蠕虫。

现在，我如何将其捕获到存档表中？

CREATE TABLE CampaignArchive(CampaignID int, XmlData XML)

INSERT INTO CampaignArchive(CampaignID, XmlData)
SELECT CampaignID, (really long list of columns) FROM (all my tables) FOR XML PATH ...

这是行不通的。 :)

有什么建议吗？

TIA

原文

I would like to run a (extensive) query that produces one line of XML. This XML represents about 12 tables worth of relational data. When I "delete" a row from the top level table I would like to "capture" the state of the data at that moment (in XML), save it to an archive table, then delete all the child table data and finally mark the top level table/row as "isDeleted=1". We have other data hanging off of the parent table that CANNOT be deleted and cannot lose the relation to the top table.

I have most of the XML worked out - see my post here on that can of worms.

Now, how can I capture that into an archive table?

CREATE TABLE CampaignArchive(CampaignID int, XmlData XML)

INSERT INTO CampaignArchive(CampaignID, XmlData)
SELECT CampaignID, (really long list of columns) FROM (all my tables) FOR XML PATH ...

This just does not work. :)

Any suggestions?

TIA

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

香橙ぽ 2024-08-11 03:01:32

您需要一个子查询将所有 XML 创建包装到进入 XmlData 列的单个标量中。再次，使用 TYPE 创建 XML 类型的标量而不是字符串：

INSERT INTO CampaignArchive(CampaignID, XmlData)
SELECT CampaignID, (
 select (really long list of columns) 
 FROM (all my tables) FOR XML PATH ..., type);

You need a subquery to wrap all that XML creation into one single scalar that goes into column XmlData. Again, use TYPE to create a scalar of type XML not a string:

INSERT INTO CampaignArchive(CampaignID, XmlData)
SELECT CampaignID, (
 select (really long list of columns) 
 FROM (all my tables) FOR XML PATH ..., type);

回复收藏 0 原文

~没有更多了~