FOR XML AUTO tries to guess what to do but making a hierarchy of your tables. When I have had to accomplish something similar to what you are doing, I wrapped my query inside another SELECT statement like this:
DECLARE @CustomerInfo TABLE (RegistrationID int, details1 varchar(200))
DECLARE @CustAccountInfo TABLE (CustomerID int, details2 varchar(200))
DECLARE @CreditInfo TABLE (CustomerID1 int, details3 varchar(200))
DECLARE @CreditInfoExtention TABLE (CustomerID2 int, details4 varchar(200))
INSERT INTO @CustomerInfo
SELECT 1,'CustInfo details'
INSERT INTO @CustAccountInfo
SELECT 1, 'CustAcctInfo details'
INSERT INTO @CreditInfo
SELECT 1,'CreditInfo details'
INSERT INTO @CreditInfoExtention
SELECT 1, 'CreditInfoExtention details'
FROM @CustomerInfo AS CustomerInfo
INNER JOIN @CustAccountInfo AS CustAccountInfo
ON CustomerInfo.RegistrationID = CustAccountInfo.CustomerID
INNER JOIN @CreditInfo AS CreditInfo
ON CreditInfo.CustomerID1 = CustomerInfo.RegistrationID
INNER JOIN @CreditInfoExtention AS CreditInfoExtention
ON CreditInfoExtention.CustomerID2 = CustomerInfo.RegistrationID
FOR xml auto, elements
As a general note, however, I agree with Melvyn that you should avoid SELECT in your query. However, to use the technique I used, specify the specific fields you want in the subquery and then use SELECT in the outer query.
Oct 21, 2009 at 09:50 AM
Tom Staab ♦