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

avatar image

Pranav
11 1 1 3

(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

avatar image

Melvyn Harbour 1 ♦♦
1.4k 19 40 26

(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

avatar image

Tom Staab ♦
14.5k 7 14 18

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

x1066
x186

asked: Oct 21, 2009 at 07:37 AM

Seen: 2555 times

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

Copyright 2016 Redgate Software. Privacy Policy