当前位置：文江博客话题详情

如何在 SQL Server 表的 XML 数据中插入身份 ID 列？

发布于 2024-08-13 05:51:07 字数 817 浏览 1 评论 0原文

我的 SQL Server 2008 数据库中有一个审计表，其中包含我正在审计的数据的 XML。

不过，在该 XML 中，我需要审计表中审计记录的 ID。

问题是 ID 是一个身份列。

所以这是一种陷阱 22，在知道 ID 之前我无法插入 XML，并且在插入 XML 之前我无法找到 ID。

我需要 ID 字段作为标识列，并且需要 XML 中的 ID。

我该怎么做？

编辑：

从 SQL 锁定的角度来看，我知道这不应该起作用，但它工作得很好：

INSERT INTO Audit (
[Data]
,[CreatedUser]
,[CreatedDateTime]
,[LastModifiedUser]
,[LastModifiedDateTime]
) VALUES (
@Data
,@CreatedUser
,@CurrentDateTime
,@CreatedUser
,@CurrentDateTime
);

SELECT @NewAudit_ID = SCOPE_IDENTITY()

SELECT @Data = Data
FROM Audit WITH (NOLOCK)
WHERE Audit_ID = @NewAudit_ID

SET @Data.modify('
    replace value of (/Search[1]/@Id[1]) with sql:variable("@NewAudit_ID")')    


UPDATE Audit
SET Data = @Data
WHERE hAudit_ID = @NewAudit_ID

为什么这会起作用？我应该使用它吗？

原文

I have an audit table in my SQL Server 2008 database which contains the XML for data that I am auditing.

Inside this XML, though, I require the ID of the audit record in the audit table.

The catch is that the ID is an identity column.

So it is kind of a catch 22, I can't insert the XML until I know the ID, and I cant find the ID until I insert the XML.

I need the ID field to be an identity column and I need the ID in the XML.

How can I do this?

Edit:

From a SQL Locking perspective, I know this shouldn't work, however it is working fine:

INSERT INTO Audit (
[Data]
,[CreatedUser]
,[CreatedDateTime]
,[LastModifiedUser]
,[LastModifiedDateTime]
) VALUES (
@Data
,@CreatedUser
,@CurrentDateTime
,@CreatedUser
,@CurrentDateTime
);

SELECT @NewAudit_ID = SCOPE_IDENTITY()

SELECT @Data = Data
FROM Audit WITH (NOLOCK)
WHERE Audit_ID = @NewAudit_ID

SET @Data.modify('
    replace value of (/Search[1]/@Id[1]) with sql:variable("@NewAudit_ID")')    


UPDATE Audit
SET Data = @Data
WHERE hAudit_ID = @NewAudit_ID

Why would this work? Should I use it?

分享到QQ

分享到微博

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

发布评论

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

他夏了夏天 2024-08-20 05:51:07

您插入从审核中收到的 XML。呈现数据时，您可以创建一个包含原始 XML 和标识的 XML：

SELECT Id AS [Id]
 , OriginalXml AS [*]
FROM AuditTable
FOR XML PATH('...'), TYPE;

You insert the XML as you received it from the audit. When presenting the data, you can manufacture an XML that contains both the original XML and the identity:

SELECT Id AS [Id]
 , OriginalXml AS [*]
FROM AuditTable
FOR XML PATH('...'), TYPE;

回复收藏 0 原文

情感失落者 2024-08-20 05:51:07

一种方法是通过存储过程插入空白记录。该存储过程将具有一个输出参数，您可以使用新插入行的标识来填充该输出参数。您的应用程序可以在插入 XML 数据后读取标识值。然后，更新存储过程可以将标识和 XML 数据作为参数来更新新插入的行。缺点是这需要插入和更新。

 CREATE PROCEDURE [dbo].[cp_InsertForID]    
 (    
 @IDOUT INT = NULL OUTPUT
 )    
 AS
 INSERT INTO AUDITABLE VALUES ('')
 SET @IDOUT = (SELECT SCOPE_IDENTITY())

One way would be to insert a blank record via a stored procedure. The stored procedure would have an output parameter that you would populate with the identity of the newly inserted row. Your app could read the identity value after the insert for the XML data. Then an update stored procedure could take the identity and XML data as parameters to update the newly inserted row. The downside is that this requires an insert and update.

 CREATE PROCEDURE [dbo].[cp_InsertForID]    
 (    
 @IDOUT INT = NULL OUTPUT
 )    
 AS
 INSERT INTO AUDITABLE VALUES ('')
 SET @IDOUT = (SELECT SCOPE_IDENTITY())

回复收藏 0 原文

~没有更多了~