playing around with SQL Server XML data type - i might create a lightweight document database derived from this

gistfile1.sql

--insert into Documents VALUES ('products/1', '<product><name>Foo</name></product>', 'dynamic');
--insert into Documents VALUES ('products/2', '<product><name>Bar</name></product>', 'dynamic');
--insert into Documents VALUES ('products/3', '<product><name>Fizz</name></product>', 'dynamic');
--insert into Documents VALUES ('products/4', '<product><name>Buzz</name></product>', 'dynamic');

UPDATE Documents SET Data = '<product><name>Foo</name></product>', entitytype='dynamic' WHERE Id = 'products/1';

select Data.value('(//product/name)[1]','varchar(30)') from Documents where entitytype = 'dynamic2'
select Data.value('(//product/name)[1]','varchar(30)') from Documents
select Data.value('(//product/name)[1]','varchar(max)') from Documents
select Data.query('(//product/name)[1]') from Documents where id = 'products/4'
select Data.query('(//product/name)[1]') from Documents

select id from documents where Data.exist('(//product/name[lower-case(.)="fool"])') = 1 and id like 'products/1%'
select data.query('((//product/name[lower-case(.)="foo"]) or (//product/name[lower-case(.)="fizz"])) = true()') from documents where entitytype = 'dynamic'
select data.query('(//product/name[lower-case(.)="foo" or lower-case(.)="fizz"])') from documents where entitytype = 'dynamic'
select id from documents where Data.exist('(//product/name[lower-case(.)="foo" or lower-case(.)="fizz"])') = 1 and entitytype = 'dynamic'
select id from documents where Data.exist('(//product/name[upper-case(.)="FOO" or upper-case(.)="FIZZ"])') = 1 and entitytype = 'dynamic'
select id from documents where Data.exist('(//product/name[lower-case(.)=("foo","fizz")])') = 1 and entitytype = 'dynamic'
select id from documents where Data.exist('(//product/name[lower-case(.)="foo"])') = 1

select id from documents where Data.exist('(//product[contains(name[1], "Foo")])') = 1
select id from documents where Data.exist('(//product/name[.="Foo"])') = 1

CREATE INDEX IX_EntityType ON [dbo].[Documents](EntityType);
CREATE PRIMARY XML INDEX [PrimaryXmlIndex] ON [dbo].[Documents]([Data]);
CREATE XML INDEX [SecondaryXmlIndex_Path] ON [dbo].[Documents] ([Data]) USING XML INDEX [PrimaryXmlIndex] FOR PATH;
CREATE XML INDEX [SecondaryXmlIndex_Value] ON [dbo].[Documents] ([Data]) USING XML INDEX [PrimaryXmlIndex] FOR VALUE;
CREATE XML INDEX [SecondaryXmlIndex_Property] ON [dbo].[Documents] ([Data]) USING XML INDEX [PrimaryXmlIndex] FOR PROPERTY;


DBCC FREEPROCCACHE;