Create indexed view in SQL Server 2008

Question

CREATE TABLE Storage
(
    ID INT IDENTITY(1,1) PRIMARY KEY,
    data XML NOT NULL
)

GO

INSERT INTO Storage(data) 
VALUES('
<footballteams>   
    <team manager="Benitez">     
        <name>Liverpool</name>     
        <ground>Anfield</ground>   
    </team>   
    <team manager="Mourinho">     
        <name>Chelsea</name>     
        <ground>Stamford Bridge</ground>   
    </team>   
    <team manager="Wenger">     
    <name>Arsenal</name>     
        <ground>Highbury</ground>   
    </team> 
</footballteams>'); 
----------------------------------------
GO

CREATE VIEW FootballView WITH SCHEMABINDING AS 
(    
    SELECT            
        TeamName = Team.TeamNode.value('(name)[1]', 'varchar(100)'),              
        Manager = Team.TeamNode.value('(@manager)', 'varchar(100)'),              
        Ground = Team.TeamNode.value('(ground)[1]', 'varchar(100)')    
    FROM         
        dbo.Storage S          
        CROSS APPLY DATA.nodes('/footballteams') AS Teams(TeamsNode)
        CROSS APPLY data.nodes('/footballteams/team') AS Team(TeamNode)
)
GO
CREATE UNIQUE CLUSTERED INDEX TeamNameInd ON FootballView(TeamName)

-

Error Message: Cannot create index on view "CF.dbo.FootballView" because it contains an APPLY. Consider not indexing the view, or removing APPLY.

I realise that, indeed, an index can't be created for this view because CROSS APPLY is used. Can anyone suggest a workaround for this? As working with views like this which aren't indexed is too slow when it deals with higher volumes of XML data.

EDIT:

Is there any way I could index the XML itself?

Are you sure that any XML features are supported with indexed views? There are severe restrictions on what you can use. — usr, Jun 29 '12 at 14:00
There's a good rule of thumb - if you've found one way to write a query to achieve a particular result set, and the query is illegal in an indexed view, then any other way you might write the query to achieve the same result set will also be illegal. (okay, there may be exceptions, but I've not encountered many of them) — Damien_The_Unbeliever, Jun 29 '12 at 14:29
Re: Edit - have you examined [`CREATE XML INDEX`](http://msdn.microsoft.com/en-us/library/bb934097) on MSDN? — Damien_The_Unbeliever, Jun 29 '12 at 14:31
I looked at that just before you posted the link. Thank you, Damien. — , Jun 29 '12 at 14:47
The answer given here states that it is not possible: http://stackoverflow.com/questions/6617972/is-it-possible-to-create-an-indexed-view-from-xml-data-in-sql-server-2008 — usr, Jun 29 '12 at 15:12

score 0 · Accepted Answer · answered Jun 29 '12 at 16:09

You can solve this problem (and probably achieve better performance) by parsing XML data into a SQL relational table and keeping the tables in-synch by using a simple trigger. The following script is a sample of how it can be done and I hope it is useful to your case:

CREATE TABLE Storage (
  id INT IDENTITY(1,1) PRIMARY KEY,
  data XML NOT NULL
);

CREATE TABLE FootballTable (
  id INT,
  teamName varchar(100),
  manager varchar(100),
  ground varchar(100)
);
GO

CREATE TRIGGER TG_INS_Storage ON Storage
FOR INSERT, UPDATE, DELETE AS
BEGIN
  DELETE FootballTable WHERE id IN (SELECT dlt.id FROM deleted dlt);

  INSERT FootballTable (id, teamName, manager, ground)
  SELECT ins.id,
    TeamName = Team.TeamNode.value('(name)[1]', 'varchar(100)'),
    Manager  = Team.TeamNode.value('(@manager)', 'varchar(100)'),
    Ground   = Team.TeamNode.value('(ground)[1]', 'varchar(100)')
  FROM inserted ins
  CROSS APPLY ins.data.nodes('/footballteams/team') AS Team(TeamNode);
END

INSERT INTO Storage(data) 
VALUES('
<footballteams>
  <team manager="Benitez"><name>Liverpool</name><ground>Anfield</ground></team>   
  <team manager="Mourinho"><name>Chelsea</name><ground>Stamford Bridge</ground></team>   
  <team manager="Wenger"><name>Arsenal</name><ground>Highbury</ground></team> 
</footballteams>');
GO
SELECT * FROM FootballTable;

Create indexed view in SQL Server 2008

1 Answers1