How do I insert the identity ID column in XML data in a SQL Server table?

Question

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?

Answer 1

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;

Answer 2

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())