Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

XML: Adjust Element Positioning Expand / Collapse
Author
Message
Posted Monday, December 17, 2012 12:33 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Tuesday, July 8, 2014 1:37 PM
Points: 232, Visits: 213
I'm having trouble formatting an XML field in a query return. I've boiled the problem part down to this bare logic:

DECLARE @t TABLE(
CoveredEntity varchar(10),
CoverageLoser varchar(10)
)

INSERT INTO @t
--( CoveredEntity, CoverageLoser )
SELECT 'Manager_A', 'Manager_Y'
UNION
SELECT 'Manager_A', 'Manager_Z'
UNION
SELECT 'Manager_B', 'Manager_Y'
UNION
SELECT 'Manager_B', 'Manager_Z'
UNION
SELECT 'Manager_C', 'Manager_Y'
UNION
SELECT 'Manager_C', 'Manager_X'

Now when I do this,
SELECT CoveredEntity AS '@Name', 
CoverageLoser AS 'Loser'
FROM @t
FOR XML PATH('Entity'), ROOT('UnClaimEvent')

I get the correct data:
<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>

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):

<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>

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.
Post #1397387
Posted Monday, December 17, 2012 1:40 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 10:46 AM
Points: 2,644, Visits: 1,651
Hi, One way of approaching it could be to use a CTE + sub query similar to this:

;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 xCTE
FOR XML PATH('Entity'), ROOT('UnClaimEvent')


Post #1397412
Posted Monday, December 17, 2012 2:39 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Tuesday, July 8, 2014 1:37 PM
Points: 232, Visits: 213
Perfect. Thanks very much for your quick assistance!
Post #1397435
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse