RE: Output the results of an SP to XML doc

I'm not sure how many XML documents your sp is supposed to return, but one
thing I noticed was that you don't increment your recordset index:
while(!rs.eof)

{

XMLToReturn = XMLToReturn + rs(0);

rs.movenext

}
In that block you are always concatenating row 0 to row 0 until you get to
the end of the recordset. I don't know if that's the problem, but it's the
only thing that jumped out at me...
--
Dave Zentrich
Data and Security Administrator
Wick Building Systems, Inc.


"JJ" wrote:

Hi,
I am trying to automate the output of a SQL table to a valid XML document. I
have created a stored procedure that returns valid XML code using FOR XML
EXPLICIT. The next step would be to schedule this uotput into a .xml
document. I have tried to use some javascript, but although the file is
created, it remains empty.
Can anyone help me out here as my VB or .NET skills are not at all
good......
The script looks like this:
//set date

var DateToProcess = GetDate();



//run SP to get XML string

var XML = GetXMLString(DateToProcess);



//write the XML string to file

WriteXMLToFile(XML,DateToProcess);



//exit script reporting success

WScript.Quit(0);



/*

====================================================================

= FUNCTIONS =

====================================================================

*/



function GetDate(){

if (WScript.Arguments.length < 1)

{

//if no date was passed default to yesterday

var dt = new Date();

dt.addDate(-1);

dt.setHours(0,0,0,0);

var DateToReturn = dt.toString();

} else {

//otherwise use the date passed

var DateToReturn = WScript.Arguments(0);

}



WScript.Echo("PROCESSING DATE : " + DateToReturn);

return DateToReturn;

}



function GetXMLString(strDate) {



var XMLToReturn = "";



WScript.Echo("OPENING CONNECTION");

//PROVIDE CONNECTION INFO AND OPEN CONNECTION

var connString = "Provider=SQLOLEDB.1;Persist Security
Info=False;User ID=[username];Password=[password];Initial Catalog=[database
name];Data Source=[sql server]";

var conn = new ActiveXObject("ADODB.Connection");

conn.Open(connString); // open connection

//WScript.Echo(conn);



var rs = new ActiveXObject("ADODB.Recordset");

rs.Open("EXEC sp_export_xml '"+strDate+"'", conn);

while(!rs.eof)

{

XMLToReturn = XMLToReturn + rs(0);

rs.movenext

}





WScript.Echo("FINISHED EXECUTING COMMAND");

//CLOSE THE CONNECTION AND CLEAN UP OBJECTS NOW THAT WE ARE
FINISHED

rs.close();

conn.close();



return XMLToReturn;

}



function WriteXMLToFile(strXML,strDate){



WScript.Echo("WRITING FILE " + "C:\\TEST_"+strDate+".xml");

//CREATE FILE TO WRITE TO

var oFS = new ActiveXObject("Scripting.FileSystemObject");
//file system object

var oFile = oFS.CreateTextFile("c:\\TEST_" + strDate + ".xml",
true); //file to write to, overwites present file



//WRITE XML TO THE FILE


oFile.WriteLine(strXML);

//CLOSE THE FILE AND CLEAN UP OBJECTS NOW THAT WE ARE FINISHED

oFile.close();

oFile = null;

oFS = null;


}



.

Relevant Pages
Loading