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>