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?