If you use the FOR XML syntax in SQL Server it can be a little tricky to get the XML formatted the way you really want it.
For every table that you have in a join it becomes a child of the table before. What if you have a 3 table join and you want one table to be the parent and the two tables that you are joining to be on the same level, I will show you how you can accomplish that.
First let’s create some tables and insert some data

T-SQL
1
2
3
4
5
6
7
8
9
10
11
CREATE TABLE #tempCustomer (CustomerID INT)
CREATE TABLE #tempAddress (CustomerID INT,FullAddress varchar(100))
CREATE TABLE #tempPhone (CustomerID INT,PhoneNumber varchar(100))
 
INSERT #tempCustomer VALUES(1)
INSERT #tempAddress VALUES(1,'Some Address')
INSERT #tempPhone VALUES(1,'212-111-2222')
 
INSERT #tempCustomer VALUES(2)
INSERT #tempAddress VALUES(2,'Other Address')
INSERT #tempPhone VALUES(2,'212-777-8888')
CREATE TABLE #tempCustomer (CustomerID INT)
CREATE TABLE #tempAddress (CustomerID INT,FullAddress varchar(100))
CREATE TABLE #tempPhone (CustomerID INT,PhoneNumber varchar(100))

INSERT #tempCustomer VALUES(1)
INSERT #tempAddress VALUES(1,'Some Address')
INSERT #tempPhone VALUES(1,'212-111-2222')

INSERT #tempCustomer VALUES(2)
INSERT #tempAddress VALUES(2,'Other Address')
INSERT #tempPhone VALUES(2,'212-777-8888')

Now let’s see what we have by running the following select statement

T-SQL
1
2
3
4
SELECT Customer.CustomerID,Address.FullAddress,Phone.PhoneNumber
FROM #tempCustomer Customer
JOIN #tempAddress Address ON Address.CustomerID= Customer.CustomerID
JOIN #tempPhone Phone ON Phone.CustomerID= Customer.CustomerID
SELECT Customer.CustomerID,Address.FullAddress,Phone.PhoneNumber
FROM #tempCustomer Customer
JOIN #tempAddress Address ON Address.CustomerID= Customer.CustomerID
JOIN #tempPhone Phone ON Phone.CustomerID= Customer.CustomerID

Output

--------------------------------------------
CustomerID	FullAddress	PhoneNumber
1		Some Address	212-111-2222
2		Other Address	212-777-8888

That is a very simple data set

Now run the following query to get some XML

T-SQL
1
2
3
4
5
SELECT Customer.CustomerID,Address.FullAddress,Phone.PhoneNumber
FROM #tempCustomer Customer
JOIN #tempAddress Address ON Address.CustomerID= Customer.CustomerID
JOIN #tempPhone Phone ON Phone.CustomerID= Customer.CustomerID
FOR XML AUTO, ELEMENTS
SELECT Customer.CustomerID,Address.FullAddress,Phone.PhoneNumber
FROM #tempCustomer Customer
JOIN #tempAddress Address ON Address.CustomerID= Customer.CustomerID
JOIN #tempPhone Phone ON Phone.CustomerID= Customer.CustomerID
FOR XML AUTO, ELEMENTS

Here is what the XML will look like.

XML
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
<Customer>
    <CustomerID>1</CustomerID>
    <Address>
        <FullAddress>Some Address</FullAddress>
        <Phone>
            <PhoneNumber>212-111-2222</PhoneNumber>
        </Phone>
    </Address>
</Customer>
<Customer>
    <CustomerID>2</CustomerID>
    <Address>
        <FullAddress>Other Address</FullAddress>
        <Phone>
            <PhoneNumber>212-777-8888</PhoneNumber>
        </Phone>
    </Address>
</Customer>
<Customer>
	<CustomerID>1</CustomerID>
	<Address>
		<FullAddress>Some Address</FullAddress>
		<Phone>
			<PhoneNumber>212-111-2222</PhoneNumber>
		</Phone>
	</Address>
</Customer>
<Customer>
	<CustomerID>2</CustomerID>
	<Address>
		<FullAddress>Other Address</FullAddress>
		<Phone>
			<PhoneNumber>212-777-8888</PhoneNumber>
		</Phone>
	</Address>
</Customer>

Do you notice that the Phone is inside Address? What if I want this output?

XML
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
<Customer>
    <CustomerID>1</CustomerID>
    <Address>
        <FullAddress>Some Address</FullAddress>
    </Address>
    <Phone>
        <PhoneNumber>212-111-2222</PhoneNumber>
    </Phone>
</Customer>
<Customer>
    <CustomerID>2</CustomerID>
    <Address>
        <FullAddress>Other Address</FullAddress>
    </Address>
    <Phone>
        <PhoneNumber>212-777-8888</PhoneNumber>
    </Phone>
</Customer>
<Customer>
	<CustomerID>1</CustomerID>
	<Address>
		<FullAddress>Some Address</FullAddress>
	</Address>
	<Phone>
		<PhoneNumber>212-111-2222</PhoneNumber>
	</Phone>
</Customer>
<Customer>
	<CustomerID>2</CustomerID>
	<Address>
		<FullAddress>Other Address</FullAddress>
	</Address>
	<Phone>
		<PhoneNumber>212-777-8888</PhoneNumber>
	</Phone>
</Customer>

As you can see Phone and Address are on the same level

Here is the query that will accomplish that

T-SQL
1
2
3
4
5
6
7
8
9
10
11
SELECT Customer.CustomerID,
(SELECT FullAddress
FROM #tempAddress Address
WHERE Address.CustomerID= Customer.CustomerID
FOR XML AUTO,TYPE, ELEMENTS),
(SELECT PhoneNumber
FROM #tempPhone Phone
WHERE Phone.CustomerID= Customer.CustomerID
FOR XML AUTO,TYPE, ELEMENTS)
FROM #tempCustomer Customer
FOR XML AUTO,TYPE, ELEMENTS
SELECT Customer.CustomerID,
(SELECT FullAddress
FROM #tempAddress Address
WHERE Address.CustomerID= Customer.CustomerID
FOR XML AUTO,TYPE, ELEMENTS),
(SELECT PhoneNumber
FROM #tempPhone Phone
WHERE Phone.CustomerID= Customer.CustomerID
FOR XML AUTO,TYPE, ELEMENTS)
FROM #tempCustomer Customer
FOR XML AUTO,TYPE, ELEMENTS

As you can see we used some subqueries to accomplish that.
Actually we can eliminate one of the subqueries and use a join between Customer and Address and all we need is put Phone in a subquery in the select of Customer to make it the same level as address.

T-SQL
1
2
3
4
5
6
7
8
SELECT Customer.CustomerID,Address.FullAddress,
(SELECT PhoneNumber
FROM #tempPhone Phone
WHERE Phone.CustomerID= Customer.CustomerID
FOR XML AUTO,TYPE, ELEMENTS)
FROM #tempCustomer Customer
JOIN #tempAddress Address ON Address.CustomerID= Customer.CustomerID
FOR XML AUTO,TYPE, ELEMENTS
SELECT Customer.CustomerID,Address.FullAddress,
(SELECT PhoneNumber
FROM #tempPhone Phone
WHERE Phone.CustomerID= Customer.CustomerID
FOR XML AUTO,TYPE, ELEMENTS)
FROM #tempCustomer Customer
JOIN #tempAddress Address ON Address.CustomerID= Customer.CustomerID
FOR XML AUTO,TYPE, ELEMENTS

Did you notice we used FOR XML AUTO,TYPE, ELEMENTS? We need to use TYPE in the subquery otherwise you will get &lt ;Phone&gt ; instead of <Phone> ( I had to put a space between &gt and ; because it would show > in this post, same for the XML below )

Run this query

T-SQL
1
2
3
4
5
6
7
8
SELECT Customer.CustomerID,Address.FullAddress,
(SELECT PhoneNumber
FROM #tempPhone Phone
WHERE Phone.CustomerID= Customer.CustomerID
FOR XML AUTO, ELEMENTS)
FROM #tempCustomer Customer
JOIN #tempAddress Address ON Address.CustomerID= Customer.CustomerID
FOR XML AUTO, ELEMENTS
SELECT Customer.CustomerID,Address.FullAddress,
(SELECT PhoneNumber
FROM #tempPhone Phone
WHERE Phone.CustomerID= Customer.CustomerID
FOR XML AUTO, ELEMENTS)
FROM #tempCustomer Customer
JOIN #tempAddress Address ON Address.CustomerID= Customer.CustomerID
FOR XML AUTO, ELEMENTS

Here is the output

XML
1
2
3
4
5
6
7
8
9
10
11
12
13
14
<Customer>
    <CustomerID>1</CustomerID>
    <Address>
        <FullAddress>Some Address</FullAddress>
        &lt ;Phone&gt ;&lt ;PhoneNumber&gt ;212-111-2222&lt ;/PhoneNumber&gt ;&lt ;/Phone&gt ;
    </Address>
</Customer>
<Customer>
    <CustomerID>2</CustomerID>
    <Address>
        <FullAddress>Other Address</FullAddress>
        &lt ;Phone&gt ;&lt ;PhoneNumber&gt ;212-777-8888&lt ;/PhoneNumber&gt ;&lt;/Phone&gt ;
    </Address>
</Customer>
<Customer>
	<CustomerID>1</CustomerID>
	<Address>
		<FullAddress>Some Address</FullAddress>
		&lt ;Phone&gt ;&lt ;PhoneNumber&gt ;212-111-2222&lt ;/PhoneNumber&gt ;&lt ;/Phone&gt ;
	</Address>
</Customer>
<Customer>
	<CustomerID>2</CustomerID>
	<Address>
		<FullAddress>Other Address</FullAddress>
		&lt ;Phone&gt ;&lt ;PhoneNumber&gt ;212-777-8888&lt ;/PhoneNumber&gt ;&lt;/Phone&gt ;
	</Address>
</Customer>

*** If you have a SQL related question try our Microsoft SQL Server Programming forum or our Microsoft SQL Server Admin forum