So, basically you are talking about an Audit table that just stores the "Audits" in a different Xml format? I've been doing that for a while but never relied on the Schemas (I guess I should). Basically, a View can be created for each "type" or "xml schema" so that if you DID need to see what audits took place, it's pretty easy to report on.

I've recently done something similar with regards to importing multiple Products into our database but support an old Xml format as well. So, basically I created two Xml Schema Collections (called them ProductSchema_v01 and ProductSchema_v02). The Stored Procedure then ensures that the Xml data coming in conforms to one or the other schemas and then imports the data appropriately based on the version.

CREATE PROCEDURE [dbo].[usp_ImportProducts]

(

@Products Xml

)

AS

Declare @v1 Xml (ProductSchema_v01)

Declare @v2 Xml (ProductSchema_v02)

BEGIN TRY

Set @v1 = @Products

END TRY

BEGIN CATCH

BEGIN TRY

Set @v2 = @Products

END TRY

BEGIN CATCH

RAISERROR('Invalid Xml...', 16, 1)

END CATCH

END CATCH

If (@v1 Is Not Null)

....

Else If (@v2 Is Not Null)

....