I'm looking for a similar solution, but instead of the 3 select statements, the values will come from 3 stored procs. I tried replacing the procs, get error.
INSERT INTO #TempExportTable VALUES
EXEC ('
EXEC [dbo].[spGetProductsDesc] @tId FOR XML AUTO, ELEMENTS;
EXEC [dbo].[spGetColoursDesc] @tId FOR XML AUTO, ELEMENTS;
EXEC [dbo].[spGetSizesDesc] @tId FOR XML AUTO, ELEMENTS;
');
Any help?
DECLARE @TempExportTable TABLE
(
Products XML,
Colours XML,
Sizes XML
)
INSERT INTO @TempExportTable VALUES
(
(SELECT * FROM Products FOR XML AUTO, ELEMENTS),
(SELECT * FROM Colours FOR XML AUTO, ELEMENTS),
(SELECT * FROM Sizes FOR XML AUTO, ELEMENTS)
)
SELECT
Products as '*',
Colours as '*',
Sizes as '*'
from @TempExportTable
FOR XML PATH('ExportList')