December 17, 2012 at 12:33 pm
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(
CoveredEntityvarchar(10),
CoverageLoservarchar(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.
December 17, 2012 at 1:40 pm
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')
December 17, 2012 at 2:39 pm
Perfect. Thanks very much for your quick assistance!
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply