SQL Server 2012 – Creating Nested XML in Indexed (Materialized) View

Short answer: You can't.

Long answer:

There's a few things going on here.

First, you might be underestimating the power of XML AUTO. It will provide a certain amount of nesting "automatically". Your provided example, in fact, could be handled without the nested XML generation.

Let's make some test tables and data:

USE tempdb;

DROP TABLE IF EXISTS T1;
DROP TABLE IF EXISTS T2;

CREATE TABLE T1
(
    Col1 int,
    Col2 varchar(50),
    [Col] char(1)
);

CREATE TABLE T2
(
    [Col] char(1),
    Col3 varchar(50),
    Col4 varchar(50)
);

INSERT INTO T1 (Col1, Col2, [Col]) VALUES
    (1, 'Test1', 'A'), (2, 'Test2', 'B');
INSERT INTO T2 ([Col], Col3, Col4) VALUES
    ('A','1x','1y'), ('A','2x','2y'), ('B','3x','3y'), ('B','4x','4y'), ('B','5x','5y');

Your query:

SELECT
    Col1,
    Col2,
    (
        SELECT      Col3, Col4
        FROM        T2
        WHERE       T2.Col = T1.Col
        FOR XML AUTO, TYPE
    )
FROM        T1
FOR XML AUTO, TYPE;

Produces the same results as one without any nesting:

SELECT
    Col1,
    Col2,
    Col3,
    Col4
FROM        T1
JOIN        T2
ON          T2.Col = T1.Col
FOR XML AUTO, TYPE;

They both produce:

<T1 Col1="1" Col2="Test1">
  <T2 Col3="1x" Col4="1y" />
  <T2 Col3="2x" Col4="2y" />
</T1>
<T1 Col1="2" Col2="Test2">
  <T2 Col3="3x" Col4="3y" />
  <T2 Col3="4x" Col4="4y" />
  <T2 Col3="5x" Col4="5y" />
</T1>

(That will break down if you have more complex or custom nesting requirements.)

Regardless of this particular example, I don't think there's an effective way to get (dynamic) XML into an indexed view.

First, an "indexed view" is really a view with a unique clustered index. You can't create "normal" indexes on an XML column.

Let's say you try:

CREATE TABLE T3
(
    x xml
);
CREATE UNIQUE CLUSTERED INDEX IX_T3 ON T3 (x);
GO

You will get an error:

Msg 1977, Level 16, State 1, Line 73 Could not create index 'IX_T3' on table 'T3'. Only XML Index can be created on XML column 'x'.

What about an XML index? Well:

You cannot create an XML index, either primary or secondary, on an xml column in a view, on a table-valued variable with xml columns, or xml type variables.

CREATE XML INDEX (Transact-SQL)

So in order to have the XML materialized using a view, it can't be the only column. You need a "normal" column to create the unique clustered index on. One way to get a normal column would be to have the XML be created with a subquery. But as you point out, if you try this:

CREATE VIEW V1
(
    n,
    x
)
WITH SCHEMABINDING
AS
SELECT 
    1,
    (
        SELECT 'Test' AS 'Col'
        FOR XML PATH, TYPE
    )
GO

CREATE UNIQUE CLUSTERED INDEX IX_V1 ON V1 (n);
GO

You will get this error:

Msg 10127, Level 16, State 1, Line 83 Cannot create index on view "tempdb.dbo.V1" because it contains one or more subqueries. Consider changing the view to use only joins instead of subqueries. Alternatively, consider not indexing this view.

You might think you could cheat by creating an outer view which didn't have a subquery, calling the original view. But if you try:

CREATE VIEW V2
(
    n,
    x
)
WITH SCHEMABINDING
AS
SELECT
    n,
    x
FROM    dbo.V1;
GO

CREATE UNIQUE CLUSTERED INDEX IX_V2 ON V2(n);
GO

You will get an error:

Msg 1937, Level 16, State 1, Line 98 Cannot create index on view 'tempdb.dbo.V2' because it references another view 'dbo.V1'. Consider expanding referenced view's definition by hand in indexed view definition.

So you can't nest these views, and you can't use subqueries. You can't use the APPLY operator either, so you can't use a table-valued user-defined function (nor can you effectively use the nodes XML function).

But you can use deterministic scalar-valued functions. So you can construct some rudimentary XML. For example:

CREATE FUNCTION F1
(
    @Col1 int,
    @Col2 varchar(50)
)
RETURNS xml
WITH SCHEMABINDING
AS
BEGIN
    RETURN
        (
            SELECT
                @Col1,
                @Col2
            FOR XML PATH(''), TYPE
        )
END
GO

CREATE VIEW V3
(
    n,
    x
)
WITH SCHEMABINDING
AS
SELECT
    Col1,
    dbo.F1(T1.Col1, T1.Col2)
FROM    dbo.T1 AS T1;
GO

CREATE UNIQUE CLUSTERED INDEX IX_V3 ON V3(n);
GO

While you could create complex, multi-level XML within the function, the function cannot reference any tables (or it wouldn't be deterministic). For example, this:

CREATE FUNCTION F2
(
    @Col1 int,
    @Col2 varchar(50),
    @Col char(1)
)
RETURNS xml
WITH SCHEMABINDING
AS
BEGIN
    RETURN
        (
            SELECT
                @Col1 AS Col1,
                @Col2 AS Col2,
                (
                    SELECT      Col3, Col4
                    FROM        dbo.T2 AS T2
                    WHERE       T2.Col = @Col
                    FOR XML AUTO, TYPE
                )
            FOR XML PATH, TYPE
        )
END
GO

CREATE VIEW V4
(
    n,
    x
)
WITH SCHEMABINDING
AS
SELECT
    Col1,
    dbo.F2(T1.Col1, T1.Col2, T1.Col)
FROM    dbo.T1 AS T1;
GO

CREATE UNIQUE CLUSTERED INDEX IX_V4 ON V4(n);
GO

Results in this error:

Msg 10133, Level 16, State 1, Line 181 Cannot create index on view "tempdb.dbo.V4" because function "dbo.F2" referenced by the view performs user or system data access.

Therefore the function can't go get the information it needs. It must be fed the information. And there's simply no way (that I can think of) to collect the data from multiple rows and feed it to the function. You can feed a deterministic function XML to manipulate, but you can't create the base XML without a subquery.

So in short, I don't think there's any way using an indexed view to materialize XML which contains data from multiple rows in any table.