I have this query:

declare @s1 varchar(1000), @s2 varchar(1000), @s3 varchar(1000), @s4 varchar(1000), @s5 varchar(1000), @s6 varchar(1000), @s7 varchar(1000),
@s8 varchar(1000), @s9 varchar(1000), @s10 varchar(1000),  @s11 varchar(1000), @s12 varchar(1000), @s13 varchar(1000), @var int, @avar varchar(15)
select @var = 1632
select @avar = cast(@var as varchar(15))
select @s1 =  'set nocount on'
select @s2 =  'select ''<?xml version="1.0" encoding="utf-8"?>'''
select @s3 =  'select ''<SOAP-ENV:Envelope xmlns:SOAP-ENV="http://schemas.xmlsoap.org/soap/envelope/" xmlns:ns1="urn:UsedProductService" xmlns:SOAP-ENC=" http://schemas.xmlsoap.org/soap/encoding/" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" SOAP-ENV:encodingStyle="http://schemas.xmlsoap.org/soap/encoding/">'''
select @s4 =  'select ''<SOAP-ENV:Body>'''
select @s5 =  'select ''<ns1:catchUsedProducts>'''
select @s6 =  'select ''<usedProductItems SOAP-ENC:arrayType="ns1:UsedProductItem['
select @s7 =  ']"'''
select @s8 =  'select ''xsi:type="ns1:ArrayOfUsedProductItem">'''
select @s9 =  'select stockNumber, [description], manufacturer, usedCategoryName, price from PCT_RJ_WEB_ITEMS [item] ORDER BY stockNumber FOR XML AUTO, ELEMENTS;'
select @s10 = 'select ''</usedProductItems>'''
select @s11 = 'select ''</ns1:catchUsedProducts>'''
select @s12 = 'select ''</SOAP-ENV:Envelope'''
select @s13 = 'select ''</SOAP-ENV:Envelope'''
print @s1
print @s2
print @s3
print @s4
print @s5
print @s6 + @avar + @s7
print @s8
print @s9
print @s10
print @s11
print @s12
print @s13declare @s1 varchar(1000), @s2 varchar(1000), @s3 varchar(1000), @s4 varchar(1000), @s5 varchar(1000), @s6 varchar(1000), @s7 varchar(1000),
@s8 varchar(1000), @s9 varchar(1000), @s10 varchar(1000),  @s11 varchar(1000), @s12 varchar(1000), @s13 varchar(1000), @var int, @avar varchar(15)
select @var = 1632
select @avar = cast(@var as varchar(15))
select @s1 =  'set nocount on'
select @s2 =  'select ''<?xml version="1.0" encoding="utf-8"?>'''
select @s3 =  'select ''<SOAP-ENV:Envelope xmlns:SOAP-ENV="http://schemas.xmlsoap.org/soap/envelope/" xmlns:ns1="urn:UsedProductService" xmlns:SOAP-ENC=" http://schemas.xmlsoap.org/soap/encoding/" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" SOAP-ENV:encodingStyle="http://schemas.xmlsoap.org/soap/encoding/">'''
select @s4 =  'select ''<SOAP-ENV:Body>'''
select @s5 =  'select ''<ns1:catchUsedProducts>'''
select @s6 =  'select ''<usedProductItems SOAP-ENC:arrayType="ns1:UsedProductItem['
select @s7 =  ']"'''
select @s8 =  'select ''xsi:type="ns1:ArrayOfUsedProductItem">'''
select @s9 =  'select stockNumber, [description], manufacturer, usedCategoryName, price from PCT_RJ_WEB_ITEMS [item] ORDER BY stockNumber FOR XML AUTO, ELEMENTS;'
select @s10 = 'select ''</usedProductItems>'''
select @s11 = 'select ''</ns1:catchUsedProducts>'''
select @s12 = 'select ''</SOAP-ENV:Envelope'''
select @s13 = 'select ''</SOAP-ENV:Envelope'''
print @s1
print @s2
print @s3
print @s4
print @s5
print @s6 + @avar + @s7
print @s8
print @s9
print @s10
print @s11
print @s12
print @s13

Everything is static except for the value in the @var variable, which changes.  Right now I set Management Studio to save to file and when the window pops up I select All Files as Type and then I save it as GenerateXML.sql.

Then, when I run this query, which references the GenerateXML.sql file, I get the results I want, including the value in the @var variable:

use master
declare @sqlstring varchar(8000)
select @sqlstring = 'sqlcmd -S RJRGNSN -s "      " -d MCHN -i c:\GenerateXML.sql  -U sa -P 123! -h-1 -w 2000 -o C:\XML_OUTPUT_TEST.txt'
execute master..xp_cmdshell @sqlstring

This all works perfectly.  But I need to set this up to run as an SQL Agent job.  I can create the first query as a stored procedure and pass in the @var variable as a parameter, but I don't know how to automate the process of saving the query as a .sql file.  Can anyone help?

T