You aren’t going to be able to make a view to this XML file easily as to use an XML file directly you have to run the sp_xml_prepairedocument and the sp_xml_removedocument procedures. I would recommend making a stored procedure instead of a view which then returns the data you are looking for.
If a view is required, you could setup a linked server to the XML file, and do a join to that XML file, but I’m not sure what sort of file locking a linked server will place on the XML file preventing you from updating it.
The stored procedure would look something like this.
<pre>CREATE PROCEDURE ShowXML AS
CREATE TABLE #RawXML (XMLData XML)
BULK INSERT #RawXML FROM ‘D:\YourXMLFile.XML’ WITH RAW
DECLARE @RawXML XML
SELECT @RawXML = XMLData
DECLARE @hDoc INT
EXEC sp_xml_preparedocument @hDoc OUTPUT @RawXML
JOIN OPENXML (@hDoc, ‘//’)
WITH (Guid uniqueidentifier ‘@Guid’,
FName NVARCHAR(50) ‘@FName’,
UserId NVARCHAR(50) ‘@UserId’) a ON YourArchiveTable.UserId = a.UserId
EXEC sp_xml_removedocument @hDoc
Keep in mind that this isn’t tested and will probably need a bit of tweaking based on your XML file and table setup.