use RelXmlDemo
go

if exists (select * from sys.tables where [name] = 'Log') drop table dbo.Log;
go

-- Steal some sample data from AdventureWorks:
select top 100 *
into dbo.Log
from AdventureWorks.dbo.DatabaseLog;

alter table dbo.Log
	add constraint PK_Log primary key (DatabaseLogID);
go





--------------------------------------------------
-- The data:

select top 10 DatabaseLogID, PostTime, XmlEvent
from dbo.Log;






--------------------------------------------------
-- What if we care only about a minor sub-set of
-- the XML value?

-- Let's get the SPID and LoginName:
select	top 10
		DatabaseLogID
	,	XmlEvent.value('/EVENT_INSTANCE[1]/SPID[1]','int') as SPID
	,	XmlEvent.value('/EVENT_INSTANCE[1]/LoginName[1]','sysname') as LoginName
	,	XmlEvent
--	,	*
from	dbo.Log



-- Depending on performance requirements and complexity you want, this is easily handled by:
--	a. A VIEW (or Indexed View).
--	b. Adding COMPUTED COLUMNS to the table.
--	c. Adding trigger-updated columns.
--	d. Enforcing inserts/updates only through official procedures.



