Assigning the output of a command to a variable in a batch file

[muttevivenkatvenu]

Hi,

I am creating a batch file which uses the OSQL utility to execute a stored procedure. I would like to pass a parameter to the stored procedure. The parameter should be populated with the output from an xml document.

I am explaining this with an example:

set /P xmltext = :: contents of abcd.xml

and then execute the OSQL like this:

OSQL -S server_name -E -d database_name -w 500 -h-1 -n -Q "exec Usp_ReadXml %xmltext%" -o targetfile.txt

to copy the contents of abcd.xml to xmltext I have tried different ways like;

type abcd.xml > %xmltext% etc.

I would like to know if this is possible and if yes how?

Any help would be greatly appreciated...

[muttevivenkatvenu]

I also tried using this statement:


For /F "tokens=* " %%A in ('type abcd.xml') Do Set MyVar=%%A

its reading by tokens...which is what i donot want...I want the bulk of the xml contents inside the variable at once........

any help would be greatly appreciated

[Geek-9pm]

Bummer. Maybe line returns can not be in a variable string.

[gpl]

The line return signifies the end of input

[muttevivenkatvenu]

Is there any way for me to capture the entire xml content into a single variable?

[GuruGary]

How big is your XML file?  There might be a work-around to what you want to do, but environment space is very limited.  I think it is maybe 64k, and there is probably 2k used with typical Windows stuff.  

[GuruGary]

Also, what OS are you running?  Assuming Windows XP, I think the max command line length is 8192 chars, and previous versions of windows are like 2048.  So the command plus the variable would have to be less than that.

Have you looked into alternatives, like importing your XML file into your database directly?

[muttevivenkatvenu]

I am doing all this because I donot have permissions to use OPENROWSET function.

My task is to open the xml file and read its contents into a table variable and generate an insert script.

the OS i am using is windows XP.

The size of the xml file varies, however at this point of time it is 6MB.

I am using OSQL  to call a stored procedure from a batch file and want to pass the contents of the xml as a parameter.

If you could think of a better way of accomplishing this task please do suggest.

[GuruGary]

If you can accomplish your task with the OPENROWSET function, then talk to the database admin to get permissions to do that by using a login instead of a trusted connection.

Can your stored procedure take a path to an XML file instead of file data?

[muttevivenkatvenu]

Hi,

I could not be granted permission to use OPENROWSET, I can definitely modify the SP to take a path instead of file contents, however I need to use the OPENROWSET to open and read the contents of the file isnt it???...or is there any other method you would suggest.....