How to rewrite SQL xpath query to create an indexed view

Question

I have a view that extracts some information from a table with xml data.
Each document contains one Product and several PurchaseOrderDetail . I want to create a view with all PurchaseOrderDetail for each Product .

 CREATE VIEW [dbo].[XML_PurchaseOrders] WITH SCHEMABINDING AS SELECT pnvalue('.', 'int') AS PurchaseOrderID ,x.ProductID FROM dbo.XmlLoadData x CROSS APPLY x.PayLoad.nodes('declare namespace NS="http://schemas.datacontract.org/2004/07/XmlDbPerfTest"; /NS:ProductAndRelated/NS:Product/NS:PurchaseOrderDetails/NS:PurchaseOrderDetail/NS:PurchaseOrderID') p(n) GO 

The PayLoad column contains XML data.

The problem with this view is that it is very slow because XML documents are large and numerous.

I would like to index this view, but it gives me an error

Cannot create index in view "XmlLoad.dbo.XML_PurchaseOrders" because it contains APPLY. Consider not indexing the view or deleting APPLY.

Can I rewrite a view to add an index?

I tried several attempts, mainly with select ... from (select ....) innnerSelect group by foo , but everyone falls on one or another rule regarding indexes on views.

Answer 1

 Is it possible to rewrite the view to make it possible to add an index? 

No. I do not think so. Instead, I am going to use some information from this question and suggest a different way.

You can add a computed persistent column to your table, which contains only XML, which has identifiers. It is not permitted to use XQUERY material directly in the computed column, but you can do this using a user-defined function.

Here is the function:

 create function dbo.GetPurchaseOrderID(@XMLData xml) returns xml with schemabinding as begin return @XMLData.query('declare namespace NS="http://schemas.datacontract.org/2004/07/XmlDbPerfTest"; /NS:ProductAndRelated/NS:Product/NS:PurchaseOrderDetails/NS:PurchaseOrderDetail/NS:PurchaseOrderID') end 

Create a table with a saved XML column:

 CREATE TABLE [dbo].[XmlLoadData]( [ProductID] [int] NOT NULL identity, [PayLoad] [xml] NOT NULL, [Size] AS (len(CONVERT([nvarchar](max),[PayLoad],0))), [PurchaseOrderIDs] AS dbo.GetPurchaseOrderID(PayLoad) PERSISTED, CONSTRAINT [PK_XmlLoadData] PRIMARY KEY CLUSTERED ( [ProductID] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] 

Request for identifiers:

 ;with xmlnamespaces('http://schemas.datacontract.org/2004/07/XmlDbPerfTest' as NS) select ProductID, PNvalue('.', 'int') as PurchaseOrderID from XmlLoadData cross apply PurchaseOrderIDs.nodes('NS:PurchaseOrderID') as P(N) 

Limited testing on my part shows that this is a bit faster. If your XML documents are large, this should be a big improvement. I doubt this will improve performance by 1000x because you are still dealing with XML interpretation, but you tell me. Of course, I have no way to verify this without your data.

Answer 2

Have you tried the XML index?

http://msdn.microsoft.com/en-us/library/bb934097.aspx