Sorry for the delay - Christmas got in the way!
One (rather important) error. The XML column is NOT typed (there was some discussion during development and it was turned down eventually).
USE [MER]
GO
/****** Object: Table [dbo].[Metadata_Structural] Script Date: 29/12/2014 10:48:07 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Metadata_Structural](
[EntityId] [bigint] NOT NULL,
[MetadataValue] [xml] NOT NULL,
[MetadataId] [bigint] IDENTITY(1,1) NOT NULL,
[CreatedDT] [datetime2](2) NOT NULL CONSTRAINT [DF_Metadata_Structural_CreatedDT] DEFAULT (getdate()),
[EditorId] [smallint] NOT NULL,
[IsCurrent] [bit] NOT NULL CONSTRAINT [DF_Metadata_Structural_IsCurrent] DEFAULT ((1)),
CONSTRAINT [PK_Metadata_Structural] PRIMARY KEY CLUSTERED
(
[MetadataId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [DATA]
) ON [DATA] TEXTIMAGE_ON [DATA]
GO
ALTER TABLE [dbo].[Metadata_Structural] WITH CHECK ADD CONSTRAINT [FK_Metadata_Structural_Editor] FOREIGN KEY([EditorId])
REFERENCES [dbo].[Editor] ([EditorId])
GO
ALTER TABLE [dbo].[Metadata_Structural] CHECK CONSTRAINT [FK_Metadata_Structural_Editor]
GO
ALTER TABLE [dbo].[Metadata_Structural] WITH CHECK ADD CONSTRAINT [FK_Metadata_Structural_Entity] FOREIGN KEY([EntityId])
REFERENCES [dbo].[Entity] ([EntityID])
GO
ALTER TABLE [dbo].[Metadata_Structural] CHECK CONSTRAINT [FK_Metadata_Structural_Entity]
GO
ALTER TABLE [dbo].[Metadata] CHECK CONSTRAINT [FK_Metadata_MetadataType]
GO
/****** Object: Index [FUQ_Metadata_Structural_Current] Script Date: 29/12/2014 10:50:01 ******/
CREATE UNIQUE NONCLUSTERED INDEX [FUQ_Metadata_Structural_Current] ON [dbo].[Metadata_Structural]
(
[EntityId] ASC
)
WHERE ([IsCurrent]=(1))
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [INDEXES]
GO
/****** Object: Index [UQ_Metadata_Structural_EntityId] Script Date: 29/12/2014 10:50:01 ******/
CREATE UNIQUE NONCLUSTERED INDEX [UQ_Metadata_Structural_EntityId] ON [dbo].[Metadata_Structural]
(
[EntityId] ASC,
[CreatedDT] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [INDEXES]
GO
SET ARITHABORT ON
SET CONCAT_NULL_YIELDS_NULL ON
SET QUOTED_IDENTIFIER ON
SET ANSI_NULLS ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
SET NUMERIC_ROUNDABORT OFF
GO
/****** Object: Index [test_mdark] Script Date: 29/12/2014 10:50:01 ******/
CREATE SELECTIVE XML INDEX [test_mdark] ON [dbo].[Metadata_Structural]
(
[MetadataValue]
)
WITH XMLNAMESPACES
(
'http://www.bl.uk/schemas/digitalobject/entities#' as bl,
'http://www.w3.org/1999/02/22-rdf-syntax-ns#' as rdf
)
FOR
(
[MDARKpath] = '(/rdf:RDF/rdf:Description/bl:isDescribedBy/@rdf:resource)',
[RightsMDpath] = '(/rdf:RDF/rdf:Description/bl:hasRightsMD/@rdf:resource)'
)
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
GO