InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comThu, 23 May 2013 02:00:45 GMT20http://www.sqlservercentral.com/Forums/Topic1397387-21-1.aspxPerfect. Thanks very much for your quick assistance!Mon, 17 Dec 2012 14:39:28 GMTcs2datahttp://www.sqlservercentral.com/Forums/Topic1397387-21-1.aspxHi, One way of approaching it could be to use a CTE + sub query similar to this:[code="sql"];WITH xCTE (CoveredEntity) AS( SELECT DISTINCT CoveredEntity FROM @t)SELECT CoveredEntity AS '@Name', ( SELECT CoverageLoser AS 'Loser' FROM @t t WHERE t.CoveredEntity = xCTE.CoveredEntity FOR XML PATH(''), TYPE )FROM xCTEFOR XML PATH('Entity'), ROOT('UnClaimEvent')[/code]Mon, 17 Dec 2012 13:40:19 GMTarthurolcothttp://www.sqlservercentral.com/Forums/Topic1397387-21-1.aspxI'm having trouble formatting an XML field in a query return. I've boiled the problem part down to this bare logic:[code="sql"]DECLARE @t TABLE( CoveredEntity varchar(10), CoverageLoser varchar(10))INSERT INTO @t --( CoveredEntity, CoverageLoser )SELECT 'Manager_A', 'Manager_Y'UNIONSELECT 'Manager_A', 'Manager_Z'UNIONSELECT 'Manager_B', 'Manager_Y'UNIONSELECT 'Manager_B', 'Manager_Z'UNIONSELECT 'Manager_C', 'Manager_Y'UNIONSELECT 'Manager_C', 'Manager_X'[/code]Now when I do this, [code="sql"]SELECT CoveredEntity AS '@Name', CoverageLoser AS 'Loser'FROM @tFOR XML PATH('Entity'), ROOT('UnClaimEvent')[/code]I get the correct data:[code="xml"]<UnClaimEvent> <Entity Name="Manager_A"> <Loser>Manager_Y</Loser> </Entity> <Entity Name="Manager_A"> <Loser>Manager_Z</Loser> </Entity> <Entity Name="Manager_B"> <Loser>Manager_Y</Loser> </Entity> <Entity Name="Manager_B"> <Loser>Manager_Z</Loser> </Entity> <Entity Name="Manager_C"> <Loser>Manager_Y</Loser> </Entity> <Entity Name="Manager_C"> <Loser>Manager_X</Loser> </Entity></UnClaimEvent>[/code]But the .NET team wants it returned such that the Loser elements are nested in a single Entity tag per Entity (identified by the Name attribute):[code="xml"]<UnClaimEvent> <Entity Name="Manager_A"> <Loser>Manager_Y</Loser> <Loser>Manager_Z</Loser> </Entity> <Entity Name="Manager_B"> <Loser>Manager_Y</Loser> <Loser>Manager_Z</Loser> </Entity> <Entity Name="Manager_C"> <Loser>Manager_Y</Loser> <Loser>Manager_X</Loser> </Entity></UnClaimEvent>[/code]Any ideas how to accomplish this? I tried an example using multiple CTEs (effectively a self-join) but all I did was end up creating extra nesting. Thanks.Mon, 17 Dec 2012 12:33:49 GMTcs2data