Generating xml from sql 2008



for a given claim i have 3 diagnosis code. Currently while generating xml from sql server I get 3 branches one for each diagnosis code with same claim id as displayed in below output. But i need a way out so that all the 3 diagnosis code can be clubbed with the associated claim id and produce desire result snippet.


Select Statement is : select * from sandbox.dbo.testing WHERE claimID=20 FOR XML auto,ELEMENTS


the output xml generated is:



<sandbox.dbo.testing>
<claimID>20</claimID>
<diagnosis>v234</diagnosis>
</sandbox.dbo.testing>
<sandbox.dbo.testing>
<claimID>20</claimID>
<diagnosis>v24</diagnosis>
</sandbox.dbo.testing>
<sandbox.dbo.testing>
<claimID>20</claimID>
<diagnosis>v256</diagnosis>
</sandbox.dbo.testing>


But need the result in below form



<sandbox.dbo.testing>
<claimID>20</claimID>
<diagnosis>v234</diagnosis>
<diagnosis>v24</diagnosis>
<diagnosis>v256</diagnosis>
</sandbox.dbo.testing>

No comments:

Post a Comment