x

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.

more ▼

asked Oct 21, 2009 at 07:37 AM in Default

Pranav gravatar image

Pranav
11 1 1 1

(comments are locked)
10|1200 characters needed characters left

2 answers: sort voted first

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.

more ▼

answered Oct 21, 2009 at 07:46 AM

Melvyn Harbour 1 gravatar image

Melvyn Harbour 1 ♦♦
1.4k 18 20 22

(comments are locked)
10|1200 characters needed characters left

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.

more ▼

answered Oct 21, 2009 at 09:50 AM

Tom Staab gravatar image

Tom Staab
5.8k 6 8 10

Your above query gives me the result shown below. which is same as what i was getting. Can you tell me if it is SQL version problem? I am using SQL 2000 and 2005.

My output is as below. 1 1 1 1
Oct 21, 2009 at 10:30 AM Pranav
Can you please copy/paste the query you used to get that output? If you ran the query I wrote, I don't understand why it was formatted that way or why you are missing all of the details fields.
Oct 21, 2009 at 11:51 AM Tom Staab
The output i have given in previous comment is not having * in select query, rather the elements i wanted are selected were regestrationid and all cistomerids.
Oct 22, 2009 at 01:27 AM Pranav

The output i get from your above query is as below:

1 CustInfo details 1 CustAcctInfo details 1 CreditInfo details 1 CreditInfoExtention details
Oct 22, 2009 at 01:28 AM Pranav
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x991
x151

asked: Oct 21, 2009 at 07:37 AM

Seen: 2339 times

Last Updated: Oct 21, 2009 at 07:40 AM