How to use a variable to change the output filename

[bobpv]

Hello all,
I am new to XML, and have looked for a solution in the list, maybe I am asking the question wrong.
I have created a select in SQL 2000 to output data to a folder. All is working fine. Sample below:
sp_makewebtask @outputfile =
'C:\ Hot Folder\filename.xml',
@query = '
select
jobnumber, (plus a bunch of other data)
from jobtable
where jobnumber = '' 15''
for xml auto',
@templatefile = 'C:\Hot Folder\template.tpl'

I am looking for help on 2 items still,
1. what is the syntax to add a variable to this select?
I want to use it in the where clause to select specific job number. So @jobnumber would be passed to the query)

2. Can this same variable be passed to the output file? I would want the @jobnumber to replace the filname.xml (to be called Job15.xml)

Can this be done? Much obliged for any direction.

 

[philhege]

You'll have to construct the template file paths and SQL statement, place them in variables, and pass them to the parameters in your SP call.

So, something like
set @myquery = 'select
jobnumber, (plus a bunch of other data)
from jobtable
where jobnumber = '''+@jobnumber+'''
for xml auto'
set @myfile = 'C:\ Hot Folder\'+@jobnumber+'.xml'

sp_makewebtask @outputfile =
@myfile,
@query = @myquery,
@templatefile = 'C:\Hot Folder\template.tpl'

HTH.







-------++NO CARRIER++-------