use RelXmlDemo
go

set nocount on;
set quoted_identifier on;
go


--------------------------------------------------
-- In this example, suppose we want the data structured
-- relationally for many use-cases. But we have some
-- applications which really would like a simple
-- "serialized object" XML view of a particular sub-set.
--
-- The case is to track "tags", such as keywords for products.
-- We want to be able to list all tags, see commonality,
-- see which products are tagged, display tags,
-- but also track the date something was tagged
-- and the user who did it...
--------------------------------------------------


--------------------------------------------------
-- TABLES.

if exists (select * from sys.tables where [name] = 'Tag') drop table dbo.Tag;
go
create table dbo.Tag (
	TagID int identity(1,1) primary key,
	TagName varchar(max) not null,
	CreatedDatetime datetime not null default getdate()
);
go

if exists (select * from sys.tables where [name] = 'Product') drop table dbo.Product;
go
create table dbo.Product (
	ProductID int identity(1,1) primary key,
	ProductName varchar(max) not null,
	ProductNumber varchar(max) not null
);
go

if exists (select * from sys.tables where [name] = 'ProductTag') drop table dbo.ProductTag;
go
create table dbo.ProductTag (
	-- This tracks which tags are assigned to which products, by whom, and when.
	ProductID int not null,
	TagID int not null,
	TaggedDatetime datetime not null default getdate(),
	TaggedByUserID int not null,
	constraint PK_ProductTag primary key (ProductID, TagID)
);
go


-- Borrow more data from AdventureWorks:
insert	dbo.Product (ProductName, ProductNumber)
select	top 10 [Name], ProductNumber
from	AdventureWorks.Production.Product
;

-- Set up a few start-up tag cases:
insert dbo.Tag (TagName) values ('latest model');
insert dbo.Tag (TagName) values ('trendy');
insert dbo.Tag (TagName) values ('retro');

insert dbo.ProductTag (ProductID, TagID, TaggedByUserID) values (1,2,1);
insert dbo.ProductTag (ProductID, TagID, TaggedByUserID) values (1,3,1);
insert dbo.ProductTag (ProductID, TagID, TaggedByUserID) values (3,2,1);
insert dbo.ProductTag (ProductID, TagID, TaggedByUserID) values (3,1,1);

-- END TABLES
--------------------------------------------------


select * from dbo.Product;
select * from dbo.Tag;
select * from dbo.ProductTag;

select	p.ProductName, t.TagName, pt.TaggedDatetime
from	dbo.Product p
		inner join dbo.ProductTag pt on (p.ProductID = pt.ProductID)
		inner join dbo.Tag t on (pt.TagID = t.TagID)
order by p.ProductName, t.TagName
;




--------------------------------------------------
-- VIEW...

-- Some application use cases want the whole "Tags" collection as a single XML value.
-- Here's a VIEW that solves this handily.
if exists (select 1 from sys.views where [name] = 'vProduct') drop view dbo.vProduct;
go

create view dbo.vProduct
as
	select	p.ProductID
		,	p.ProductName
		,	p.ProductNumber
		-- Here we select the XML collection, if there are any tags:
		,	cast( ( select	t.TagName as "@name"
						,	pt.TaggedDatetime as "@tagdate"
						,	pt.TaggedByUserID as "@user"
					from	dbo.ProductTag pt
							inner join dbo.Tag t on (pt.TagID = t.TagID)
					where	pt.ProductID = p.ProductID -- Link to outer query.
					order by pt.TaggedDatetime
					for xml path ('Tag'), root ('Tags')
				  ) as xml) as Tags
	from	dbo.Product p
go


select	*
from	dbo.vProduct





--------------------------------------------------
-- PROCEDURE ...

-- Applications that wish to, may use a procedure that includes the TagCollection:
if exists (select 1 from sys.procedures where [name] = 'UpdateProduct') drop procedure dbo.UpdateProduct
go

create procedure dbo.UpdateProduct
	@ProductID int
,	@ProductName varchar(max) = null
,	@ProductNumber varchar(max) = null
,	@Tags xml = null
,	@UserID int -- User who performed this update. (And therefore made any newly assigned tags.)
as
begin
	-- < other Product field updates here> . . .
	
	-- Update Tag collection if it is provided.
	-- *How* you update, depends on your design / requirements.
	-- This example leaves any matching tags, deletes newly missing ones,
	--	and adds new ones from the XML.
	
	if (@Tags is not null)
	begin
	
		-- To make it a bit simpler, lets shred the XML Tags into a Table-Var.
		declare @tt table (TagName varchar(max), TagID int);
		insert @tt (TagName)
		select	tg.c.value('@name','varchar(max)')
		from	@Tags.nodes('/Tags/Tag') as tg(c)
		;
		
		-- Update the global list if necessary:
		insert	dbo.Tag (TagName)
		select	tt.TagName
		from	@tt tt
		where	not exists (select 1 from dbo.Tag where TagName = tt.TagName)
		;
		
		-- Now fetch IDs (just makes joins easier, wouldn't do in production code).
		update	tt
		set		TagID = t.TagID
		from	@tt tt
				inner join dbo.Tag t on (tt.TagName = t.TagName)
		;
	
		-- Delete [ProductTag] entries for this Product that weren't
		-- in the XML we received.
		delete	dbo.ProductTag
		where	ProductID = @ProductID
			and	TagID not in (select TagID from @tt)
		;
		
		-- Assign any tags to the Product that weren't there before:
		insert	dbo.ProductTag (ProductID, TagID, TaggedByUserID)
		select	@ProductID, t.TagID, @UserID
		from	@tt t
		where	not exists (select 1 from dbo.ProductTag
							where	ProductID = @ProductID
								and TagID = t.TagID
							)
		;
	end
	return 0;
end
go





--------------------------------------------------
-- Using the Procedure...

-- Use to update a Tag:
select	ProductID, Tags
from	dbo.vProduct
where	ProductID = 1;

declare @mynewtags xml;
set @mynewtags = '<Tags>
	<Tag name="trendy"/>
    <Tag name="classic"/>
</Tags>'
-- Removed "retro", added "classic".

exec dbo.UpdateProduct
	@ProductID = 1
,	@Tags = @mynewtags
,	@UserID = 7438
;

select	ProductID, Tags
from	dbo.vProduct
where	ProductID = 1;








--------------------------------------------------
-- Other applications can interact with Tags in a more
-- 'traditional' way, and more precisely if needed.
-- Querying anything related to Tags uses only
-- the normal relational operators, keeping things
-- simple.
-- 
-- Other variations might include an instead-of
-- trigger on the [vProduct] view, having the logic
-- of that stored procedure in the case of inserts
-- or updates.
--------------------------------------------------
