Facing problem with xml auto in sql

Question

question

Pranav asked Oct 21, '09

Facing problem with xml auto in sql

Hello all,

I have select statement as below:

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

When I get XML file its not like what I want. The elements are nested.

<CustomerInfo>
    <Details>Hello</Details >
    <CustAccountInfo>
        <Details>Hello</Details >
        <CreditInfo>
            <Details>Hello</Details >
            <CreditInfoExtention>
                <Details>Hello</Details >
            </CreditInfoExtention>
        </CreditInfo>
    </CustAccountInfo>
</CustomerInfo>

What i want is all the <Details>Hello</Details > elements under single node. Please Advise.

t-sql xml

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Answer 1 · 2009-10-21T07:46:38Z

Melvyn Harbour 1 answered Oct 21, '09

I suggest you give BOL a read. Here's the article that discusses how to use FOR XML AUTO:

http://msdn.microsoft.com/en-us/library/ms188273.aspx

I think my first step would be to avoid using SELECT *. I think you need better, more explicit, control of the fields being returned.

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Answer 2 · 2009-10-21T09:50:53Z

Tom Staab answered Oct 21, '09

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'

SELECT * FROM
(
    SELECT *
    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    
) CustomerDetails
FOR xml auto, elements

Output:

<CustomerDetails>
  <RegistrationID>1</RegistrationID>
  <details1>CustInfo details</details1>
  <CustomerID>1</CustomerID>
  <details2>CustAcctInfo details</details2>
  <CustomerID1>1</CustomerID1>
  <details3>CreditInfo details</details3>
  <CustomerID2>1</CustomerID2>
  <details4>CreditInfoExtention details</details4>
</CustomerDetails>

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.

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

question