June 18, 2003 at 6:05 pm
I have 2 tables
Manager
Manager_id varchar(10)
Manager_name Varchar(40)
Data:
1,Jose Cruz
2,Al Davids
SubManger
Manager_id varchar(10)
SubManager_id varchar(10)
SubManager_name varchar(40)
Data:
1,1,Justin_pomp
1,2,David A
1,3,Manci T
1,4,Terry G
2,5,Terrence F
2,6,Yan P
2,7,Bill S
2,8, John S
Employee
Employee_id varchar(10)
Manager_id varchar(10)
SubManager_id varchar(10)
Employee_name varchar(60)
Data is a combination of the above tables with employee info.
I want to formulate an XML file Which looks like below where
count is the count of Employees reporting to subManager and Manager
<Manager manager_name="Jose Cruz" counts="12">
<SubManager subManager_name="Justin Pomp" count="2"/>
<SubManager subManager_name="David A" count="3"/>
<SubManager subManager_name="Manci T" count="4"/>
<SubManager subManager_name="Terry G" count="3"/>
</Manager>
<Manager manager_name="Al Davids" counts="14">
<SubManager subManager_name="Terrence F" count="4"/>
<SubManager subManager_name="Yan P" count="2"/>
<SubManager subManager_name="Bill S" count="3"/>
<SubManager subManager_name="John S" count="5"/>
</Manager>
My Question: How do I write a sql statement with for auto clause? It does not allow me to write a Group by and have XML AUTO.
June 24, 2003 at 8:00 am
This was removed by the editor as SPAM
June 24, 2003 at 8:51 am
Just treat it as a derived table, like this:
select * from (your query here) as a for xml auto.
Remember to alias any aggregate columns in your group by.
Andy
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply