Formatting Data With FOR XML And Sp_makewebtask?

Hi all,

I have a question about using FOR XML and sp_makewebtask. This (see below) is a simplified example where I have a table with 3 columns, the two first columns hold varius data and the third column (Subdata3) holds duplicated data.

My question is: Is it possible to transform the hierachy of data (see "Prefered XML output" for details) when transforming from SQL to XML using either FOR XML EXPLICIT or by using some sort of more advanced sp_makewebtask template (or some other technique perhaps)? And if, how? Any help welcome!

Cheers,
Christian


----------------------


--- sp_makewebtask ---
EXEC sp_makewebtask
@outputfile = c: empoutput.xml,
@query = 'SELECT Subdata1, Subdata2, Subdata3 FROM Data WHERE Subdata3 = X FOR XML AUTO, ELEMENTS',
@templatefile ='c: emp emplate.tpl'


--- c: emp emplate.tpl ---
<?xml version="1.0" encoding="UTF-8"?>
<Data>
<%begindetail%>
<%insert_data_here%>
<%enddetail%>
</Data>


--- c: empoutput.xml ---
<?xml version="1.0" encoding="UTF-8"?>
<Data>
<Subdata>
<Subdata1>455</Subdata1>
<Subdata2>12312</Subdata2>
<Subdata3>1</Subdata3>
</Subdata>
<Subdata>
<Subdata1>435345</Subdata1>
<Subdata2>675</Subdata2>
<Subdata3>1</Subdata3>
</Subdata>
<Subdata>
<Subdata1>133323</Subdata1>
<Subdata2>976</Subdata2>
<Subdata3>1</Subdata3>
</Subdata>
</Data>


--- Prefered XML output ---
<?xml version="1.0" encoding="UTF-8"?>
<Data Subdata3="1">
<Subdata>
<Subdata1>455</Subdata1>
<Subdata2>12312</Subdata2>
</Subdata>
<Subdata>
<Subdata1>435345</Subdata1>
<Subdata2>675</Subdata2>
</Subdata>
<Subdata>
<Subdata1>133323</Subdata1>
<Subdata2>976</Subdata2>
</Subdata>
</Data>