Jacob, Sorry for not giving all the details.

Here it is.

The business information is in one table and Account info is in second table.

I need to join these two tables to get the result.

Business table Structure

Crate table Business

(

Business_ID varchar(50),

Company Name varchar(50),

Address_Line1 varchar(50),

City varchar(50),

State char(2))

Let say the data looks like this in this table

Business_ID Business_name Address City State

12345 ABC St1 Dallas TX

12356 HP ST2 Austin TX

Account Table Structure

(

Business_ID varchar(50),

Account_ID Varchar(50),

Account_Number varchar(50),

Description Varchar(50),

Account_Type char(1)

)

Business_ID Account_ID Account_Number Description Account_Type

12345 111 5678 Corporation C

12345 111 4567 business Checking C

12345 111 7655 Basic Savings S

12356 122 7788 Personal Checking C

12356 122 8899 Checking C

My XML should look like this

<XMl xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">

<Business_Type>

<Small_Business>

<Business_ID> 12345 </Business_ID>

<Business_Name> ABC </Business_Name>

<Address1> St1 </Address1>

<City> Dallas </City>

<State> TX </State>

<Accounts>

<Account>

<Account_Number>5678 </Account_Number>

<Description> Corporation<Description>

<Account_Type> C</Account_Type>

</Account>

<Account>

<Account_Number> 4567</Account_Number>

<Description> business Checking<Description>

<Account_Type> C</Account_Type>

</Account>

<Account>

<Account_Number> 7655</Account_Number>

<Description>Basic Savings <Description>

<Account_Type>S </Account_Type>

</Account>

</Accounts>

<Business_ID> 12356</Business_ID>

<Business_Name> HP </Business_Name>

<Address1>ST2 </Address1>

<City>Austin </City>

<State> TX</State>

<Accounts>

<Account>

<Account_Number>7788</Account_Number>

<Description>Personal Checking <Description>

<Account_Type>C </Account_Type>

</Account>

<Account>

<Account_Number>8899 </Account_Number>

<Description>Checking <Description>

<Account_Type> C</Account_Type>

</Account>

</Accounts>

</Small_Business>

<Business_Type>

</XMl>

Here the complex elements are

BusinessType, Small_Business and Accounts . I am having difficulties to have these elements in my XML.

Jacob, This is only sample of my whole XML. I have got a Very Complex XSD with many complex and simple elements. I have Data in 10 tables that i need to pull information from, which means i should use 10 joins. I need to generate an XML from these tables. So i was just trying to see if it is possible in SQL Server.