Facing problem with xml auto in sql

Question

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
	LEFT OUTER JOIN @CreditAddress AS CreditAddress ON CreditAddress.CustomerID3 = CustomerInfo.RegistrationID
FOR xml auto, elements

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

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

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

---

Cheers, Pranav

Answer 1

Could you post some DDL, sample data and expected results please?

Answer 2

Hello Bob, The DDL looks like

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 details1, details2, details3, details4
    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

The expected result is as below.

<CustomerInfo>
  <details1>CustInfo details</details1>
  <details2>CustAcctInfo details</details2>
  <details3>CreditInfo details</details3>
  <details4>CreditInfoExtention details</details4>  
</CustomerInfo>

Can you please help me to get out of the mess as i have been stuck into it since 36 hours.

---

Cheers, Pranav

Answer 3

How do you want the XML to look when you're finished?  That is what I mean by expected results. 

Also can you confirm which version of SQL Server you are using?

Answer 4

As it was there in prevoius reply the resulted XML should look like below:

<CustomerDetails>
  <details1>CustInfo details</details1>
  <details2>CustAcctInfo details</details2>
  <details3>CreditInfo details</details3>
  <details4>CreditInfoExtention details</details4>  
</CustomerDetails>

I want only single XML parent tag inside which all elements should be there as in select statement.

I am using MS SQL 2005.

Thanks in advance.

---

Cheers, Pranav

Answer 5

When I run your example, I get exactly that result, so I can't see what the problem is.

Answer 6

Bob,

I am getting the below result:

<CustomerInfo>

  <details1>CustInfo details</details1>

  <CustAccountInfo>

    <details2>CustAcctInfo details</details2>

    <CreditInfo>

      <details3>CreditInfo details</details3>

      <CreditInfoExtention>

        <details4>CreditInfoExtention details</details4>

      </CreditInfoExtention>

    </CreditInfo>

  </CustAccountInfo>

</CustomerInfo>

Why it is showing different result?

---

Cheers, Pranav

Answer 7

Well I can reproduce your result on SQL 2000, which is why I asked which version you are using.  I'm guessing you are using SSMS 2005 to connect to a SQL 2000 box.

Workaround 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))
DECLARE @CustomerDetails TABLE ( details1 varchar(200), details2 varchar(200), details3 varchar(200), 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'

INSERT INTO @CustomerDetails
SELECT details1, details2, details3, details4
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

SELECT details1, details2, details3, details4
FROM @CustomerDetails AS CustomerDetails
FOR XML AUTO, ELEMENTS

Answer 8

Exactly, I am using SSMS 2005 to connect to a SQL 2000 box. But i also tried into both the servers independently and the result is same. 

The second aproach that you gave was nice. 

Only one problem is that i am having total 159 columns to select from these 4 tables and it increases overhead.

If something other than last one can halp me out with less efforts than its better... Hope there will be something for it in Microsoft SQL server and i tried many things with no luck. :(

---

Cheers, Pranav

Answer 9

I got the answer... :) It was due to same that I am using SSMS 2005 to connect to a SQL 2000 box. I disconnected all connection into SSMS, closed the SSMS and restarted it with only one connection with SQL 2005 and it worked for me.

I got exactly what i wanted. 

Thanks a lot Bob for your help.

---

Cheers, Pranav