Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
miamikk
Starting Member
19 Posts |
 Posted - 2007-08-23 : 21:51:02
|
| 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]GODeclare @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 = 1SET @Month = 2SET @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]NULLI 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.jpgThe above query works perfectly if I remove BCP & For XML Auto part. Here is a screen shot of Successful query results.http://fiu.edu/~atmakurk/ustrade/working_example.jpg |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
|
|
|
|
|
|
|
SQL Query output to XML using BCP HELP!!