MS SQL XML - Output the results of an SP to XML doc

Author

Output the results of an SP to XML doc

2006-11-08, 7:14 pm

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(DateToP
rocess);

//write the XML string to file

WriteXMLToFile(XML,D
ateToProcess);

//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=& #91;username];Passwo
rd=& #91;password];Initia
l 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(strXM
L,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;

}

Dave Z.

2006-11-09, 7:21 pm

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(DateToP
rocess);
>
>
>
> //write the XML string to file
>
> WriteXMLToFile(XML,D
ateToProcess);
>
>
>
> //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=& #91;username];Passwo
rd=& #91;password];Initia
l 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(strXM
L,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;
>
>
> }
>
>
>

Sponsored Links