Output xml format - Sql

Posted by Sharpcnet under Sql Server on 4/9/2014 | Points: 10 | Views : 542 | Status : [Member] | Replies : 1
Using Sql Server 2008 R2

SQL :

select U.UserId,U.Username,P.FormName,P.View,P.Delete
from tblUser U
left join
( select P.*,F.FormName
from tblProfile p
left join tblForm F on P.FormId = F.FormId
where P.ProfileId = 1
) P on U.ProfileId = P.ProfileId
where U.Username='test' and U.Password = 'test'
for xml path('User'),Root('Root')


RESULT :

UserId Username FormName View Delete
-------------------------------------
8 test bank 1 0
8 test unit 1 1


XML :

<Root>
<User>
<UserId>8</UserId>
<Username>test</Username>
<FormName>bank</FormName>
<View>1</View>
<Delete>0</Delete>
</User>
<User>
<UserId>8</UserId>
<Username>test</Username>
<FormName>unit</FormName>
<View>1</View>
<Delete>1</Delete>
</User>
</Root>


What should I do to get it in this format:

<Root>
<User>
<UserId>8</UserId>
<Username>test</Username>
</User>
<Forms>
<Form>
<FormName>bank</FormName>
<View>1</View>
<Delete>0</Delete>
<Form>
<Form>
<FormName>unit</FormName>
<View>1</View>
<Delete>1</Delete>
<Form>
</Forms>
</Root>





Responses

Posted by: Adsingh_Naz on: 5/14/2014 [Member] Starter | Points: 25

Up
0
Down
I am eager to provide you the answer.. It would be easy for me if you provide some sample data

Sharpcnet, if this helps please login to Mark As Answer. | Alert Moderator

Login to post response