Résultats combinés de plusieurs procédures stockées en un seul file XML

SQL Server 2014: search d'une solution telle que publiée dans [Résultats combinés de plusieurs procédures stockées dans un file XML] [1] [1]: http://www.sqlservercentral.com/Forums/Topic1377879-21-1.aspx

Je veux que la sortie soit comme

<root> <Products> <...> </Products> <Colours> <...> </Colours> <Sizes> <...> </Sizes> </root> 

Et le code:

 DECLARE @TempExportTable TABLE ( Products XML, Colours XML, Sizes XML ) 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; '); ) SELECT Products as '*', Colours as '*', Sizes as '*' from @TempExportTable FOR XML PATH('ExportList') 

Est-ce réalisable?

Votre problème est que ces SP fournissent probablement des sets de lignes structurellement non identiques. Les SP ne sont pas la bonne façon de lire datatables … Vous ne savez pas ce que vous faites dans vos SP, mais cela pourrait être beaucoup plus facile avec les TVF

En suivant un exemple comment vous pourriez réaliser ce que vous voulez:

 CREATE PROCEDURE dbo.Test1 AS BEGIN SELECT * FROM (VALUES(1,'Test1_1'),(2,'Test1_2')) AS tbl(t1a,t1b); END GO CREATE PROCEDURE dbo.Test2 AS BEGIN SELECT * FROM (VALUES('a','Test1_A',100),('b','Test1_B',200)) AS tbl(t2a,t2b,t2c); END GO CREATE PROCEDURE dbo.Test3 AS BEGIN SELECT * FROM (VALUES('a'),('b')) AS tbl(t3); END GO --Here starts your solution --First we need tables to fill the SPs results into --The column's names don't have to be the same as delivered from the SP... DECLARE @t1 TABLE(t1x INT,t1y VARCHAR(100)); INSERT INTO @t1 EXEC dbo.Test1; DECLARE @t2 TABLE(t1x VARCHAR(100),t2y VARCHAR(100),t3z INT); INSERT INTO @t2 EXEC dbo.Test2; DECLARE @t3 TABLE(t3x VARCHAR(100)); INSERT INTO @t3 EXEC dbo.Test3; SELECT (SELECT * FROM @t1 FOR XML PATH('T1_Row'),ROOT('T1'),TYPE) ,(SELECT * FROM @t2 FOR XML PATH('T2_Row'),ROOT('T2'),TYPE) ,(SELECT * FROM @t3 FOR XML PATH('T3_Row'),ROOT('T3'),TYPE) FOR XML PATH(''),ROOT('root') GO DROP PROCEDURE dbo.Test1; DROP PROCEDURE dbo.Test2; DROP PROCEDURE dbo.Test3; 

Exactement le même résultat, mais beaucoup plus facile à appeler était cette approche avec des fonctions scalaires:

 CREATE FUNCTION dbo.Test1() RETURNS XML AS BEGIN RETURN (SELECT * FROM (VALUES(1,'Test1_1'),(2,'Test1_2')) AS tbl(t1a,t1b) FOR XML PATH('T1_Row'),ROOT('T1')); END GO CREATE FUNCTION dbo.Test2() RETURNS XML AS BEGIN RETURN (SELECT * FROM (VALUES('a','Test1_A',100),('b','Test1_B',200)) AS tbl(t2a,t2b,t2c) FOR XML PATH('T2_Row'),ROOT('T2')); END GO CREATE FUNCTION dbo.Test3() RETURNS XML AS BEGIN RETURN (SELECT * FROM (VALUES('a'),('b')) AS tbl(t3) FOR XML PATH('T3_Row'),ROOT('T3')); END GO SELECT (SELECT dbo.Test1()) ,(SELECT dbo.Test2()) ,(SELECT dbo.Test3()) FOR XML PATH(''),ROOT('root') GO DROP FUNCTION dbo.Test1; DROP FUNCTION dbo.Test2; DROP FUNCTION dbo.Test3; 

Le résultat dans les deux cas:

 <root> <T1> <T1_Row> <t1x>1</t1x> <t1y>Test1_1</t1y> </T1_Row> <T1_Row> <t1x>2</t1x> <t1y>Test1_2</t1y> </T1_Row> </T1> <T2> <T2_Row> <t1x>a</t1x> <t2y>Test1_A</t2y> <t3z>100</t3z> </T2_Row> <T2_Row> <t1x>b</t1x> <t2y>Test1_B</t2y> <t3z>200</t3z> </T2_Row> </T2> <T3> <T3_Row> <t3x>a</t3x> </T3_Row> <T3_Row> <t3x>b</t3x> </T3_Row> </T3> </root> 

Essayez ceci (SP doit sortir xml ou nvarchar comme datatables xml):

 DECLARE @TempExportTable TABLE (Results xml); INSERT INTO @TempExportTable EXEC [dbo].[spGetProductsDesc] @tId; --The output must be like: --<Products> -- <Product id="1" name="pr1" /> -- <Product id="2" name="pr2" /> -- ... --</Products> INSERT INTO @TempExportTable EXEC [dbo].[spGetColoursDesc] @tId; --The output must be like: --<Colours> -- <Colour id="1" name="red" /> -- <Colour id="2" name="white" /> -- ... --</Colours> INSERT INTO @TempExportTable EXEC [dbo].[spGetSizesDesc] @tId; -- --<Sizes> -- <Size id="1" name="S" /> -- <Size id="2" name="M" /> -- ... --</Sizes> SELECT Results as '*' FROM @TempExportTable for xml PATH('') ,ROOT('root'); 

Résultats:

 <root> <Products> <Product id="1" name="pr1" /> <Product id="2" name="pr2" /> <Product id="3" name="pr3" /> </Products> <Colours> <Colour id="1" name="red" /> <Colour id="2" name="white" /> <Colour id="3" name="green" /> </Colours> <Sizes> <Size id="1" name="S" /> <Size id="2" name="M" /> <Size id="3" name="L" /> </Sizes> </root>