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?

Answer 1

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;