Question on For XML clause

Hi All,
I have the following three tables . Using FOR XML clause I am getting
result as nested elements. Please let me know if there is anyway of getting
output as shown.
Createtable #Loan(LoanNumint
)
Createtable #Borrower(LoanNumint
,FirstNamevarchar(50)
,LastNamevarchar(50)
)
Createtable #LoanFee (LoanNumint
,LoanFeeNamevarchar(20)
,LoanFeeAmtreal
)
INSERT#LoanVALUES(12345)
INSERT#Borrower VALUES(12345,'Bill', 'Gates')
INSERT#LoanFee VALUES(12345,'Processing Fees', 100.00)
SELECTLoan.LoanNum
,Borrower.FirstName
,Borrower.LastName
,LoanFee.LoanFeeName
,LoanFee.LoanFeeAmt
FROM#Loan Loan (NOLOCK)
JOIN#Borrower Borrower (NOLOCK) ON Borrower.LoanNum = Loan.LoanNum
JOIN#LoanFee LoanFee (NOLOCK) ON LoanFee.LoanNum = Loan.LoanNum
FOR XML AUTO
DROP TABLE #Loan
DROP TABLE #Borrower
DROP TABLE #LoanFee
Current Nested Output :
<Loan LoanNum="12345">
<Borrower FirstName="Bill" LastName="Gates">
<LoanFee LoanFeeName="Processing Fees" LoanFeeAmt="1.0000000e+002"/>
</Borrower>
</Loan>
Expected Output where LoanFee is child of Loan not Borrower:
<Loan LoanNum="12345">
<Borrower FirstName="Bill" LastName="Gates" />
<LoanFee LoanFeeName="Processing Fees" LoanFeeAmt="1.0000000e+002"/>
</Loan>
Thank You
Srinivas
"Srinivas" <Srinivas@.discussions.microsoft.com> wrote in message
news:BC84DD57-3C02-4673-8B04-1A24823F7277@.microsoft.com...
> Hi All,
> I have the following three tables . Using FOR XML clause I am
> getting
> result as nested elements. Please let me know if there is anyway of
> getting
> output as shown.
You will need to use FOR XML EXPLICIT instead of AUTO to get the results you
want. The auto mode nests elements.
Bryant
|||Thank You
"Bryant Likes" wrote:

> "Srinivas" <Srinivas@.discussions.microsoft.com> wrote in message
> news:BC84DD57-3C02-4673-8B04-1A24823F7277@.microsoft.com...
> You will need to use FOR XML EXPLICIT instead of AUTO to get the results you
> want. The auto mode nests elements.
> --
> Bryant
>
>