CODEWITHSUNDEEP
sql-serverxmlsql-server-2008stored-procedures
Robin
Robin

Reputation: 87

Generating xml from SQL Server 2008

For a given claim I have 3 diagnosis codes. 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 is 

claimID diagnosis
20  v234
20  v24
20  v256

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

Upvotes: 2

Views: 97

Answers (2)

Mikael Eriksson
Mikael Eriksson

Reputation: 138960

Fetch the rows in a subquery and hard code or use a parameter for the claimID in the main query. 

select 20 as claimID,
       (
       select diagnosis 
       from sandbox.dbo.testing
       where claimID = 20
       for xml path(''), type
       )
for xml path('sandbox.dbo.testing')

Upvotes: 3

Pரதீப்
Pரதீப்

Reputation: 93724

Use Xml Path() and Root 

SELECT *
FROM   sandbox.dbo.testing
WHERE  claimID = 20
FOR xml path(''), root('sandbox.dbo.testing')

Upvotes: 0

Related Questions