Indexing a view with xQuery in it I'm not sure if this can be done at all but if it can it would be a huge help.

I have a system where I am not allowed to make changes to the database structures.  I have yet to have code changes put in place either for that matter but at least those they will consider (bairly).

What I am dealing with is a table that contains XML data stored in a varchar(max) column.  Then a view that not only references the XML using xQuery but actually has xQuery in the WHERE clause.  

Here is the original query.

CREATE VIEW [dbo].[My_View]
AS
   WITH xoutput AS (   
          SELECT CONVERT(xml, ci.[Data]) AS x,  ci.[ContentType_id], ci.ID    
          FROM [dbo].ci
          INNER JOIN [dbo].ct 
            ON ci.[ContentType_id] = ct.[Id]
          INNER JOIN [dbo]. cv 
            ON ci.ID = cv.[ContentItemRecord_id]
          WHERE ct.[Name] = 'MyImage' and Latest = 1) 
  SELECT h.ep.value('(MyID)[1]','varchar(10)') as MyID,        
      h.ep.value('(Image)[1]', 'varchar(max)') as MyImage,       
      h.ep.value('(IsBlocked)[1]', 'bit') as IsBlocked,       
      h.ep.value('(IsPublished)[1]', 'bit') as IsPublished,
      [ContentType_id], ID
  FROM xoutput         
  CROSS APPLY x.nodes('//Data/MyImage') as h(ep)
  WHERE h.ep.value('(IsBlocked)[1]', 'bit') = 0 
    AND h.ep.value('(IsPublished)[1]', 'bit') = 1 


What I thought of trying was creating an indexed view out of at least part of it.

The first thing of course was to create a SCHEMABOUND view with COUNT_BIG.  Where I'm running into a problem is the grouping.

When I try it like this:

ALTER VIEW My_XML_Kenneth
WITH SCHEMABINDING
AS
    SELECT ci.[ContentType_id], ci.ID,
        h.ep.value('(IsBlocked)[1]', 'bit') AS IsBlocked,
        h.ep.value('(IsPublished)[1]', 'bit') AS IsPublished,
        h.ep.value('(MyID)[1]','varchar(10)') as MyID,
        h.ep.value('(Image)[1]', 'varchar(max)') as MyImage,       
        h.ep.value('(IsBlocked)[1]', 'bit') as IsBlocked,       
        h.ep.value('(IsPublished)[1]', 'bit') as IsPublished,
        COUNT_BIG(*) ct_bg
    FROM [dbo].ci
    INNER JOIN [dbo].ct 
        ON ci.[ContentType_id] = ct.[Id]
    INNER JOIN [dbo].cv 
        ON ci.ID = cv.[ContentItemRecord_id]
    CROSS APPLY (VALUES (CONVERT(xml, ci.[Data]))) x (x)
    CROSS APPLY x.x.nodes('//Data/MyImage') as h(ep)
    WHERE ct.[Name] = 'MyImage' 
      AND Latest = 1
    GROUP BY ci.ContentType_id, ci.ID, 
        h.ep.value('(IsBlocked)[1]', 'bit'),
        h.ep.value('(IsPublished)[1]', 'bit'),
        h.ep.value('(MyID)[1]','varchar(10)'),
        h.ep.value('(Image)[1]', 'varchar(max)'),       
        h.ep.value('(IsBlocked)[1]', 'bit'),       
        h.ep.value('(IsPublished)[1]', 'bit')
GO


I get an error 

XML methods are not allowed in a GROUP BY clause.


When I try this as the GROUP BY

GROUP BY ci.ContentType_id, ci.ID, h.ep.nodes('//')


I get this error

"nodes" is not a valid function, property, or field.


Is what I'm trying even possible?  

Note: last time I tried to come up with a fix to this view I got HEAVY push back that they couldn't possibly make a change.  So if it isn't possible it's not like I'm out much.
You can index a view that contains an XML column. You cannot index a view that contains any of the XML datatype methods ( eg .nodes, .query, .value, .exist ).

I would say your greatest issue is always casting the VARCHAR column to XML.  Is there are a particular reason you are not using the XML datatype?  Although it can alter XML slightly ( eg whitespace ), storing it as VARCHAR means you can't take advantage of the usual performance tuning methods with the XML datatype such as typing the XML ( XML SCHEMA COLLECTION ) and XML Indexes.

In the meantime, add the text() accessor and avoid repeating XML calls ( ie no need to .value IsBlocked as you have it as WHERE clause criteria ) for a performance boost:

CREATE VIEW [dbo].[My_View2_4]
AS
WITH xoutput AS (   
    SELECT CONVERT(xml, ci.[Data]) AS x,  ci.[ContentType_id], ci.ID    
    FROM [dbo].ci
        INNER JOIN [dbo].ct ON ci.[ContentType_id] = ct.[Id]
        INNER JOIN [dbo].cv ON ci.ID = cv.[ContentItemRecord_id]
    WHERE ct.[Name] = 'MyImage' and Latest = 1
          ) 
  SELECT h.ep.value('(MyID/text())[1]','varchar(10)') as MyID,        
      h.ep.value('(Image/text())[1]', 'varchar(max)') as MyImage,       
      0 as IsBlocked,       
      1 as IsPublished,
      [ContentType_id], ID
  FROM xoutput
  CROSS APPLY x.nodes('/Data/MyImage') as h(ep)
  WHERE h.ep.value('(IsBlocked/text())[1]', 'bit') = 0 
    AND h.ep.value('(IsPublished/text())[1]', 'bit') = 1 
GO


These were my results from a simple 100k row test rig I created:


Simple test rig:

-- Simple test rig
USE tempdb
GO

SET NOCOUNT ON
GO

IF OBJECT_ID('dbo.ci') IS NOT NULL DROP TABLE dbo.ci 
IF OBJECT_ID('dbo.ct') IS NOT NULL DROP TABLE dbo.ct 
IF OBJECT_ID('dbo.cv') IS NOT NULL DROP TABLE dbo.cv
IF OBJECT_ID('dbo.My_View') IS NOT NULL DROP VIEW dbo.My_View
IF OBJECT_ID('dbo.My_View2') IS NOT NULL DROP VIEW dbo.My_View2
IF OBJECT_ID('dbo.My_View2_4') IS NOT NULL DROP VIEW dbo.My_View2_4
GO

CREATE TABLE dbo.ci ( Id INT IDENTITY PRIMARY KEY, ContentType_id INT, data VARCHAR(MAX) )
CREATE TABLE dbo.ct ( Id INT IDENTITY PRIMARY KEY, Name VARCHAR(20), Latest BIT )
CREATE TABLE dbo.cv ( ContentItemRecord_id INT PRIMARY KEY )
GO


;WITH cte AS (
SELECT TOP 100000 ROW_NUMBER() OVER ( ORDER BY ( SELECT 1 ) ) rn
FROM master.sys.columns c1
    CROSS JOIN master.sys.columns c2
    CROSS JOIN master.sys.columns c3
)
INSERT INTO dbo.ci ( ContentType_id, data )
SELECT rn, '&lt;Data&gt;
    &lt;MyImage&gt;
        &lt;MyID&gt;' + CAST( rn AS VARCHAR(MAX) ) + '&lt;/MyID&gt; 
        &lt;Image&gt;' + CAST( NEWID() AS VARCHAR(MAX) ) + '&lt;/Image&gt;
        &lt;IsBlocked&gt;0&lt;/IsBlocked&gt;
        &lt;IsPublished&gt;1&lt;/IsPublished&gt;
    &lt;/MyImage&gt;
&lt;/Data&gt;'
FROM cte
GO

CHECKPOINT

INSERT INTO dbo.ct ( Name, Latest )
SELECT 'MyImage', ContentType_id
FROM dbo.ci

CHECKPOINT

INSERT INTO dbo.cv ( ContentItemRecord_id )
SELECT Id
FROM dbo.ci
GO

-- Original view
CREATE VIEW [dbo].[My_View]
AS
WITH xoutput AS (   
    SELECT CONVERT(xml, ci.[Data]) AS x,  ci.[ContentType_id], ci.ID    
    FROM [dbo].ci
        INNER JOIN [dbo].ct ON ci.[ContentType_id] = ct.[Id]
        INNER JOIN [dbo].cv ON ci.ID = cv.[ContentItemRecord_id]
    WHERE ct.[Name] = 'MyImage' and Latest = 1
          ) 
  SELECT h.ep.value('(MyID)[1]','varchar(10)') as MyID,        
      h.ep.value('(Image)[1]', 'varchar(max)') as MyImage,       
      h.ep.value('(IsBlocked)[1]', 'bit') as IsBlocked,       
      h.ep.value('(IsPublished)[1]', 'bit') as IsPublished,
      [ContentType_id], ID
  FROM xoutput
  CROSS APPLY x.nodes('//Data/MyImage') as h(ep)
  WHERE h.ep.value('(IsBlocked)[1]', 'bit') = 0 
    AND h.ep.value('(IsPublished)[1]', 'bit') = 1 
GO


CREATE VIEW [dbo].[My_View2]
AS
WITH xoutput AS (   
    SELECT CONVERT(xml, ci.[Data]) AS x,  ci.[ContentType_id], ci.ID    
    FROM [dbo].ci
        INNER JOIN [dbo].ct ON ci.[ContentType_id] = ct.[Id]
        INNER JOIN [dbo].cv ON ci.ID = cv.[ContentItemRecord_id]
    WHERE ct.[Name] = 'MyImage' and Latest = 1
          ) 
  SELECT h.ep.value('(MyID/text())[1]','varchar(10)') as MyID,        
      h.ep.value('(Image/text())[1]', 'varchar(max)') as MyImage,       
      h.ep.value('(IsBlocked/text())[1]', 'bit') as IsBlocked,       
      h.ep.value('(IsPublished/text())[1]', 'bit') as IsPublished,
      [ContentType_id], ID
  FROM xoutput
  CROSS APPLY x.nodes('/Data/MyImage') as h(ep)
  WHERE h.ep.value('(IsBlocked/text())[1]', 'bit') = 0 
    AND h.ep.value('(IsPublished/text())[1]', 'bit') = 1 
GO

--... a few other aborted attempts. 2.1 to 2.3

CREATE VIEW [dbo].[My_View2_4]
AS
WITH xoutput AS (   
    SELECT CONVERT(xml, ci.[Data]) AS x,  ci.[ContentType_id], ci.ID    
    FROM [dbo].ci
        INNER JOIN [dbo].ct ON ci.[ContentType_id] = ct.[Id]
        INNER JOIN [dbo].cv ON ci.ID = cv.[ContentItemRecord_id]
    WHERE ct.[Name] = 'MyImage' and Latest = 1
          ) 
  SELECT h.ep.value('(MyID/text())[1]','varchar(10)') as MyID,        
      h.ep.value('(Image/text())[1]', 'varchar(max)') as MyImage,       
      0 as IsBlocked,       
      1 as IsPublished,
      [ContentType_id], ID
  FROM xoutput
  CROSS APPLY x.nodes('/Data/MyImage') as h(ep)
  WHERE h.ep.value('(IsBlocked/text())[1]', 'bit') = 0 
    AND h.ep.value('(IsPublished/text())[1]', 'bit') = 1 
GO


CREATE VIEW [dbo].[My_View2_5]
AS
WITH xoutput AS (   
    SELECT ci.[Data],  ci.[ContentType_id], ci.ID    
    FROM [dbo].ci
        INNER JOIN [dbo].ct ON ci.[ContentType_id] = ct.[Id]
        INNER JOIN [dbo].cv ON ci.ID = cv.[ContentItemRecord_id]
    WHERE ct.[Name] = 'MyImage' and Latest = 1
          ) 
  SELECT h.ep.value('(MyID/text())[1]','varchar(10)') as MyID,        
      h.ep.value('(Image/text())[1]', 'varchar(max)') as MyImage,       
      0 as IsBlocked,       
      1 as IsPublished,
      [ContentType_id], ID
FROM xoutput
    CROSS APPLY (SELECT CONVERT(xml, [Data]).query('.')) AS T(x)
        CROSS APPLY x.nodes('/Data/MyImage') as h(ep)
  WHERE h.ep.value('(IsBlocked/text())[1]', 'bit') = 0 
    AND h.ep.value('(IsPublished/text())[1]', 'bit') = 1 
GO

/*
SELECT *
FROM [dbo].[My_View]
GO
SELECT *
FROM [dbo].[My_View2_4]
GO
SELECT *
FROM [dbo].[My_View2_5]
GO
*/