a small problem

hi people,
my query goes something like this...
select a.*,b.*,c.* from table1 a ,table2 b,table3 c where a.col1 = b.col2
and a.col1 = c.col4 for xml auto, elements,base binary64
everything works fine except tht the elements of tables table2,table3 are
being added as child/nested nodes...instead of coming in the main flow..
ex- this is the output iam getting
<a>
<a-fld1>...</a-fld1>
<a-fld2>...</a-fld2>
<a-fld3>...</a-fld3>
<b>
<b-fld1>...</b-fld1>
<b-fld2>...</b-fld2>
<b-fld3>...</b-fld3>
<c>
<c-fld1>...</c-fld1>
<c-fld2>...</c-fld2>
<c-fld3>...</c-fld3>
</c>
</b
</a>
but i want only one top level & one child level...like
<a>
<a-fld1>...</a-fld1>
<a-fld2>...</a-fld2>
<a-fld3>...</a-fld3>
<b>
<b-fld1>...</b-fld1>
<b-fld2>...</b-fld2>
<b-fld3>...</b-fld3>
<c-fld1>...</c-fld1>
<c-fld2>...</c-fld2>
<c-fld3>...</c-fld3>
.....
.....
</b>
</a>....is it possible...please suggest metry Using XML Explicit|||xml explicit needs that all the select statements have the joining
columns...but my joining columns as not the same in all the tables as u can
see
its where a.col1 = b.col2
and a.col1 = c.col4 for xml auto, elements,base binary64
not
where a.col1 = b.col1
and a.col1 = c.col1 for xml auto, elements,base binary64...
so problem with it...atleast please tell me wether we can construct xml
from raw string in SQL Stored procedures...i will read all the neccessary
data and format it accordingly in xml
"Omnibuzz" wrote:

> try Using XML Explicit|||Can you post the DDL, insert script for some sample data and the expected
result for the sample data?
Also I presume u have SQL Server 2000 right?|||It's hard to come up with a good answer if we can't see the tables, so pleas
e
post DDL (and maybe sample data).
ML
http://milambda.blogspot.com/|||Anyways.. try this and let me know if this is what you wanted...
create table xmltbl1
(t1col1 int, t1col2 varchar(10))
create table xmltbl2
(t2col1 int, t2col2 varchar(10))
create table xmltbl3
(t3col1 int, t3col2 varchar(10),t3col3 int)
insert into xmltbl1 values (1,'abcde')
insert into xmltbl2 values (1,'fghij')
insert into xmltbl3 values (2,'klmno',1)
select a.*,b.* from xmltbl1 a,
(select b.* ,c.* from xmltbl2 b,xmltbl3 c
where b.t2col1 = c.t3col3) as b
where a.t1col1 = b.t2col1
for xml auto, elements,BINARY BASE64