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

Question on case table and nested table

Hi, all here,

As we are allowed to select one table as both case table and nested table, however what is the benefit of using one table as both case table and nested table? Thanks in advance for your advices.

I am looking forward to hearing from you shortly.

With best regards,

Yours sincerely,

Selecting the same table as both case and nested table makes sense when the table actually has two keys and represents a one-to-many relationship. An example is a transaction table, which contains products purchased by each customer. It could look like below:
CustID ProductID
1 Beer
1 Milk
1 Coke
1 Chips
2 Oreo
2 Milk
...

As you see, there is a one-to-many relationship between CustID and ProductID.
Typical modeling with nested tables would require a separate table, with customer information (containing the distinct customers, their IDs and possibly other information) and a relationship from that table to this one, with CustID acting as a Foreign Key inside this transaction table.

However, when:
- no Customer table is present OR
- no additional useful information is available in the Customer table
it is helpful to use only the transaction table for mining.

Internally, the implementation issues two queries, one of them selecting all distinct customers.
Hope this clarifies the scenario and any benefits|||

Hi, Bogdan,

Thanks a lot for your very helpful advices.

With best regards,

Yours sincerely,