I am having trouble in writing code to save the SQL query output to an XML file.

A simple example like below is working fine and it creates an output a.xml.

------Begin Code-----
Declare @fNameTemp varchar(50)
Declare @SQL varchar(3000)
Set @fNameTemp = 'c:\XMLDump\a.xml'

Set @SQL = 'bcp "Select top 10 * From test..month For XML Auto, Elements" QueryOut "' + @fNameTemp + '" -c -t, -T -S'
Exec master..xp_cmdshell @SQL
------End Code ------

But I am having trouble in writing when I change the Select query to a more complex one as in the example below. The code is part of SP.

------Begin Code-----USE [test]
GO
Declare @TblName1 varchar(10)
Declare @TblName2 varchar(10)
Declare @District varchar(6)
Declare @Month varchar(3)
Declare @TblType varchar(10)
Declare @Thisyear varchar(10)
Declare @Lastyear varchar(10)

SET @TblName1 = '2006exp'
SET @TblName2 = '2005exp'
SET @District = 1
SET @Month = 2
SET @TblType = 'exp1'
set @Thisyear = '2006'
set @Lastyear = '2005'

set @TblName1 = '[' + @TblName1 + ']'
set @TblName2 = '[' + @TblName2 + ']'
set @TblType = '[' + @TblType + ']'

Declare @fNameTemp varchar(50)
SET @fNameTemp = 'C:\XMLDump\2.xml'
Declare @SQuery nvarchar(3000)

SET @SQuery = 'bcp "Select d.descrip_1 as [Commodity Description], ty.HS4, ty.Amount1 as ['+ @ThisYear +' Value ($)], (ty.Amount1/ty.Total1)*100 as ['+ @ThisYear +' Share (%)] ,
            py.Amount2 as ['+ @LastYear +' Value ($)], (py.Amount2/py.Total2)*100 as ['+ @LastYear +' Share (%)]
            FROM
            (Select top 10 a.commodity1 as HS4, Sum(a.all_val_mo) as Amount1,
            (select Sum(a1.all_val_mo) FROM test..' + @TblName1 + ' a1 where a1.stat_month <=' + @Month + ' and a1.district=' + @District +' ) as Total1
            FROM test..' + @TblName1 + ' a where a.stat_month <=' + @Month + ' and a.district=' + @District +'
            Group by a.commodity1 Order by Amount1 DESC) ty
            JOIN
            (Select b.commodity1 as HS4, Sum(b.all_val_mo) as Amount2,
            (select Sum(b1.all_val_mo) FROM test..' + @TblName2 + ' b1 where b1.stat_month <=' + @Month + ' and b1.district=' + @District +' ) as Total2
            FROM test..' + @TblName2 + ' b where b.stat_month <=' + @Month + ' and b.district=' + @District +'
            Group by b.commodity1) py on ty.HS4=py.HS4 LEFT OUTER JOIN '+ @TblType +' d on ty.HS4=d.commodity1
            Order by Amount1 DESC FOR XML AUTO, Elements" QueryOut "' + @fNameTemp + '" -c -t, -T -S'

Exec master..xp_cmdshell @SQuery
------End Code ------

Output for above code is shown below:
usage: bcp {dbtable | query} {in | out | queryout | format} datafile
  [-m maxerrors] [-f formatfile] [-e errfile]
  [-F firstrow] [-L lastrow] [-b batchsize]
  [-n native type] [-c character type] [-w wide character type]
  [-N keep non-text native] [-V file format version] [-q quoted identifier]
  [-C code page specifier] [-t field terminator] [-r row terminator]
  [-i inputfile] [-o outfile] [-a packetsize]
  [-S server name] [-U username] [-P password]
  [-T trusted connection] [-v version] [-R regional enable]
  [-k keep null values] [-E keep identity values]
  [-h "load hints"] [-x generate xml format file]
NULL


I don't know what is the mistake I am doing. I would appreciate if anyone can help me figure the error.

http://www.fiu.edu/~atmakurk/ustrade/SQL_XML1.jpg

hi there.. can you confirm that the query works???

HTH

Gonzalo

================================================== =========
Read this if you want to know how to get a correct reply for your question:
http://www.catb.org/~esr/faqs/smart-questions.html
^^Took that from dparsons signature and he Took that from planoie's profile
================================================== =========
My programs achieved a new certification (can you say the same?):
WORKS ON MY MACHINE
http://www.codinghorror.com/blog/archives/000818.html
================================================== =========
I know that CVS was evil, and now i got the proof:
http://worsethanfailure.com/Articles...-Hate-You.aspx
================================================== =========

Yes the query works perfectly.
I just removed the BCP and For XML Auto part and here is a screen shot of Successful query results.

http://fiu.edu/~atmakurk/ustrade/working_example.jpg

Hi, instead of
Order by Amount1 DESC FOR XML AUTO, Elements" QueryOut "' + @fNameTemp + '" -c -t, -T -S'


Try using "Order by Amount1 DESC FOR XML AUTO, Elements" QueryOut "' + @fNameTemp + '"-S<servername> -c -r -t
"