Is it possible to create an indexed view from Xml

问题:

I see from the 2005 documentation that you cannot create an indexed view from an Xml column.

Is this possible in 2008 or 2008R2? I can't find any documentation saying that it is something that was added but am looking for confirmation and I don't have handy access to a 2008 environment at the moment.

EDIT

My motivation behind this is that the amount of Xml is growing to the point where SSRS reports which aggregate data from the Xml are becoming slow.

回答1:

I don't believe this is possible. Without a better explanation of what you are trying to do, one suggestion I can offer is to pull the XML apart before insert (perhaps using an instead of trigger, or doing this shredding at the application layer) and storing the part(s) you want to use for the indexed view in separate non-XML columns.

回答2:

Depending on your need, what you could do is this:

• create a set of stored functions that extract certain bits of key information from your XML (function receives XML as input, extracts the info using XPath/XQuery, returns a VARCHAR or INT or something value)

  CREATE FUNCTION dbo.SomeFunction(@Input XML)
  RETURNS VARCHAR(20)
  WITH SCHEMABINDING
  AS BEGIN
     ......
  END
  

• add those key bits to your base table as computed columns that reference those functions, with the PERSISTED keyword:

  ALTER TABLE dbo.YourTable
     ADD ComputedColumns1 AS dbo.SomeFunction(XmlColumn) PERSISTED
  

• create your view on the table and those computed columns, with schemabinding:

  CREATE VIEW vYourView 
  WITH SCHEMABINDING
  AS  
        SELECT (list of columns)
        FROM dbo.YourTable
  

• create a unique, clustered index on that view - unless you've violated any of the requirements of the indexed view, this should work just fine:

  CREATE UNIQUE CLUSTERED INDEX CIX_YourView ON dbo.vYourView(.....)
  

This works fine if you need to extract a small number of key bits of information from an XML column - it's definitely not recommended for lots of XML elements / values.