Saving results of a stored procedure into a XML file

: In many real time scenarios when our database is interacting with the web application or so, we need to pass information to/from the database from/to the application. While updating/inserting such information into database, we usually pass it in the form of XML and also while getting it from the SP; we get it in the form of XML. There is couple of options available in SQL Server to read XML (OPENXML/ nodes function) and also for generating XML from the relational data.

Sometimes the XML data generated this way from the stored procedure is required to be saved into some XML files on the file system; at some specified location. And this may be required to do so without the help of some other application like C#/VB code to process this XML and save it to some XML file.

To accomplish this, we find some options in SQL Server itself to save the XML into XML file directly.

1.    Using CLR Stored Procedure.

2.    Using Command Line Utility (OSQL).

3.    Using xp_CmdShell utility of SQL Server.

4.    Creating OLE objects in SQL Server (sp_OACreate).

Let’s consider an example for generating a XML:

CREATE PROC GenerateXMLproc

AS

SELECT [Name], ProductNumber, Color

FROM dbo.Product

FOR XML raw(‘Product’), elements, root(‘Products’)

 

Now we’ll discuss the above mentioned options for saving this XML data:

1.    Using CLR Stored Procedure: In this method we can create a CLR stored procedure which will take the XML data generated, file name & location of the file as input parameters. And then it can process that XML as per the requirements and can save that XML file to the desired location.

 

Creating CLR stored procedure:

[Microsoft.SqlServer.Server.SqlProcedure]

public static void SaveXMLOutput(SqlXml XmlData, SqlString Filename)

{

             //Save the XML data being passed to the SP to a file location

      //specify the name of the file suppiled to the SP

      XmlDocument xmlDoc = new XmlDocument();

      SqlPipe output = SqlContext.Pipe;

      xmlDoc.LoadXml(XmlData.Value);

      xmlDoc.Save(Filename.Value);

}

//This will give a DLL ‘SaveXMLOutput.dll

Creating Assembly in SQL Server

CREATE ASSEMBLY SaveXMLOutputAssembly

from ‘C:\Temp\SaveXMLOutput.dll’

WITH PERMISSION_SET = EXTERNAL_ACCESS

–‘ trustworthy ’ property of the database must be set to ‘ON’ before using the EXTERNAL_ACCESS for the aseembly.

–This can be done using ALTER DATABASE [DataBaseName] SET trustworthy ON

Creating stored procedure from the Imported DLL/Assembly:

CREATE PROCEDURE SaveXMLOutput

@xmldata XML,

@filename nvarchar(1024)

AS

EXTERNAL NAME SaveXMLOutputAssembly.[XMLOutput].SaveXMLOutput

 

–XMLOutput is the name of class and SaveXMLOutput is the name of the SP as defined in the class

–Executing this SP

execute SaveXMLOutput ‘Pass the XML Data generated from other SP’

,‘ C:\Temp\MyXML.xml’

2.    Using Command Line Utility (OSQL): In this method we can use the command line utility available for executing SQL queries into SQL Server. But this we cannot use inside SQL Server Management studio or in the SP etc, we have to use it through the command prompt.

OSQL S [SERVERName] d [DATABASEName] E /Q ‘exec  GenerateXMLproc’

o ‘C:\Temp\MyXML.xml’

 

–GenerateXMLproc is the name of the procedure generting XML output, -o specifies the path of the file in which output is ti be saved.

 

3.    Using xp_CmdShell utility of SQL Server:  If we want to use OSQL command line utility inside SQL server, we can make use of xp_CmdShell utility. But this will internally make use of OSQL only, so there would be a little overhead of using xp_CmdShell over OSQL.

DECLARE @cmd VARCHAR(255)

SET @cmd = ‘osql -E /Q  “exec GenerateXMLproc”  /o C:\Temp\MyXML.xml’

 

EXEC xp_cmdshell @cmd

 

–xp_cmdshell must be enabled on the server. It can be done using either SQL Server Surface Area Configuration OR executing sp_configure ‘xp_cmdshell’, ‘1’

 

4.    Creating OLE objects in SQL Server (sp_OACreate):  Using this system SP we can create an instance of an OLE object.

CREATE PROCEDURE SaveXMLOutput

(

@String Varchar(max),

@Path VARCHAR(255),

@Filename VARCHAR(100)

)

AS

DECLARE  @objFileSystem int

        ,@objTextStream int,

              @objErrorObject int,

              @strErrorMessage Varchar(1000),

         @Command varchar(1000),

         @hr int,

              @fileAndPath varchar(80)

 

set nocount on

 

select @strErrorMessage=‘opening the File System Object’

EXECUTE @hr = sp_OACreate  ‘Scripting.FileSystemObject’ , @objFileSystem OUT

 

Select @FileAndPath=@path+‘\’+@filename

if @HR=0 Select @objErrorObject=@objFileSystem , @strErrorMessage=‘Creating file “‘+@FileAndPath+‘”‘

if @HR=0 execute @hr = sp_OAMethod   @objFileSystem   , ‘CreateTextFile’

     , @objTextStream OUT, @FileAndPath,2,True

 

if @HR=0 Select @objErrorObject=@objTextStream,

     @strErrorMessage=‘writing to the file “‘+@FileAndPath+‘”‘

if @HR=0 execute @hr = sp_OAMethod  @objTextStream, ‘Write’, Null, @String

 

if @HR=0 Select @objErrorObject=@objTextStream, @strErrorMessage=‘closing the file “‘+@FileAndPath+‘”‘

if @HR=0 execute @hr = sp_OAMethod  @objTextStream, ‘Close’

 

if @hr<>0

     begin

     Declare

              @Source varchar(255),

              @Description Varchar(255),

              @Helpfile Varchar(255),

              @HelpID int

    

     EXECUTE sp_OAGetErrorInfo  @objErrorObject,

              @source output,@Description output,@Helpfile output,@HelpID output

     Select @strErrorMessage=‘Error whilst ‘

                        +coalesce(@strErrorMessage,‘doing something’)

                        +‘, ‘+coalesce(@Description,)

     raiserror (@strErrorMessage,16,1)

     end

EXECUTE  sp_OADestroy @objTextStream

EXECUTE sp_OADestroy @objTextStream

 

–Executing this SP

execute SaveXMLOutput ‘Pass the XML Data generated from other SP’

,‘ C:\Temp’, ‘MyXML.xml’

 

So depending upon the feasibilty as per the enviroment available, performance as per the complications of the data & the requirement we can choose any one of them.

Advertisements
This entry was posted in SQL Server. Bookmark the permalink.

9 Responses to Saving results of a stored procedure into a XML file

  1. P.C.KUMARAN says:

    Dear Munish
    The article was a very useful one for programmers like me. I had tried the OLE Automation Objects way of converting a text file to XML file. But I got the message
    “Error whilst creating file -xmlfilename”
    Any solutions ?

    Thanks for your article

    P.C.KUMARAN

  2. openshac says:

    When using a CLR Stored Procedure it might be better to use the StreamWriter object (especially if you’re not going to validate the XML by using a try catch).

    For large xml StreamWriter is going to be a lot quicker than XmlDocument because it doesn’t load the whole XML into memory and then parse it.

    You could use this instead:

    [Microsoft.SqlServer.Server.SqlProcedure]
    public static void SaveXmlOutput(SqlXml xmlData, SqlString filename)
    {
    StreamWriter writer = new StreamWriter(filename.ToString());
    writer.Write(xmlData.Value);
    writer.Close();

    SqlContext.Pipe.Send(String.Format
    (“XML text successfully saved to file ‘{0}'”, filename));
    }

  3. ANISHPS says:

    hi munish,
    how can we pass a varchar input parameter to the sp
    in below line for section 3
    Using xp_CmdShell utility of SQL Server

    SET @cmd = ‘osql -E /Q “exec GenerateXMLproc” /o C:\Temp\MyXML.xml’
    -here suppose GenerateXMLproc accepts a varchar input parameter .how to pass it?

    please help

  4. ivan says:

    Thanks for the article!

    At the end of proc SaveXMLOutput

    you have two destroy statments:

    EXECUTE sp_OADestroy @objTextStream

    EXECUTE sp_OADestroy @objTextStream

    should the second destroy statement be

    EXECUTE sp_OADestroy @objFileSystem ?

  5. Vikash says:

    Hi Munish,
    I am looking for an approach to query SSAS cubes and pass the result in XML format to a down stream application.

    I am trying this using Stored Procedure [through Open Query & Linked Server] but the result is not coming in proper XML format .

    I just used FOR XML in the OPEN Query in that Stored Procedure.

    Can you please suggest if this is the correct approach or is there any other better approach to implement this?

    I am exploring the other option of querying the cubes using Webservices and retruning the result in XML format .

    Kindly suggest what will be the better and robust option ?

    Thanks
    Vikash

  6. very good one says:

    very good one

  7. Bobb says:

    I have website hosted on Godaddy can i save xml file using these solutions to remote host.

  8. rahmo says:

    can i save the xml file through TRIGGERS ? not stored procedur ?

  9. Suvidhya says:

    very nice. perfect solution .. thanks

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s