P
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:
(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.
