How can I save PL / SQL output (clob) as an XML file?

Question

At this stage, I feel pretty stupid, because it doesn’t look like the task I have to fight, but after a little sleep and with the thousands of thoughts that pass through my mind, I think I'm a frog in a pot, a virus tunnel- vision, blurred decision. Here:

I have a query in PL / SQL that selects data from several tables in XML format (1 row, 1 column - CLOB type) using DBMS_XMLGEN.getxml. It works great.

My problem is returning this XML output to C # and asking the user where he would like to save this output as an output.xml file on his personal computer.

I am using ASP.net with jQuery (AJAX), thus sending to the .aspx page when the link is clicked and using the returned data (xml coming from the request in this instance) to save. Using the OLEDB command with an OLEDB datareader does not seem to be the right way ...

What is the best way to do this? I worked a lot with serialization inside .net, but I guess this is the best way (according to my research)? Does anyone have an example of how this can be done in my situation?

My thoughts basically create this output / file in memory, asking the user where he wants to save it, saving it as an .xml file and clearing the memory.

UPDATE: Ok I was able to create an XMLDocument that just simply accepts the xml output from my request. How can I get information from the user about where to save this file on my computer?

My code is:

Javascript:

function XML_ExportAppRestrictions() { try { var data = {"action": "exportAppRestrictions"}; var returned = AjaxLoad('Modules/Common/Common.aspx', null, data, null, null, false); if (returned.toString().toUpperCase().substring(0, 5) == "ERROR") { return; } else { // should the file be saved here or in my aspx page using xmldoc.save? // Using xmldoc.save will only be able to save the file on the server yes? } } catch (ex) { alert(ex.Message); } } 

ASP:

 if (Request["action"] == "exportAppRestrictions") { // Create and open DB connection conn.ConnectionString = SG_Common.CommonClass.NetRegUDL; common.OpenDBConnection(ref conn); // Create command and set SQL statement cmd.Connection = conn; cmd.CommandType = System.Data.CommandType.Text; // select text was removed below due to it length cmd.CommandText = "select x as AppRestrictions from dual"; rd = cmd.ExecuteReader(); if (rd.HasRows) { if (rd.Read()) { str = rd["AppRestrictions"].ToString(); } } rd.Close(); System.Xml.XmlDocument xmldoc = new System.Xml.XmlDocument(); xmldoc.InnerXml = str; xmldoc.Save("c:\\xmltest.xml"); goto Done; } // Done runs in a seperate try/catch statement and only returns data to the calling js function. Done: Response.Write(str); 

Answer 1

Using jQuery (AJAX) has restrictions related to user interaction (with a specific link to the save dialog - that is, sending a request to the asp page in my example and waiting for the file [or memory stream in my case] back and you want ask the user where he would like to save it - thus NOT on the server).

I found a workaround for this, since using AJAX for internet-related calls is my forte. There is room for improvement, as mentioned in this post, but it worked fine for me, which allowed me to return my XML data using response.write (x) and this “hack” to allow the user to save the XML file wherever he wants

Javascript Source:

 jQuery.download = function (url, data, method) { //url and data options required if (url && data) { //data can be string of parameters or array/object data = typeof data == 'string' ? data : jQuery.param(data); //split params into form inputs var inputs = ''; jQuery.each(data.split('&'), function () { var pair = this.split('='); inputs += '<input type="hidden" name="' + pair[0] + '" value="' + pair[1] + '" />'; }); //send request jQuery('<form action="' + url + '" method="' + (method || 'post') + '">' + inputs + '</form>') .appendTo('body').submit().remove(); }; }; 

ASP Return:

 Response.Clear(); Response.ContentType = "text/xml"; Response.AddHeader("Content-disposition", "attachment; filename=test.xml"); Response.Write(yourXMLstring); 

Usage example (javascript call):

 $.download("your_url_here", html_url_based_parameters_if_needed_here); 

Get this useful post here: http://www.filamentgroup.com/lab/jquery_plugin_for_requesting_ajax_like_file_downloads/

Hope this helps someone in the future!

Answer 2

XML should be just plain text UTF8. I used serialization for objects. Perhaps someone will correct me, but I did not consider it necessary to use serialization for text or binary data.

To transfer this data through memory, there is a good fooobar.com/questions/12683 / ... , which shows how to use MemoryStream memory buffers and bytes.

Because if you can get it in the buffer [], you can do something like this :

 public bool SaveDocument( Byte[] docbinaryarray, string docname) { string strdocPath; strdocPath = "C:\\DocumentDirectory\\" + docname; FileStream objfilestream =new FileStream(strdocPath,FileMode.Create,FileAccess.ReadWrite); objfilestream.Write(docbinaryarray,0,docbinaryarray.Length); objfilestream.Close(); return true; } 

Contact here for more information on System.IO methods , for example:

 StreamWriter writer = new StreamWriter("c:\\KBTest.txt"); writer.WriteLine("File created using StreamWriter class."); writer.Close(); this.listbox1.Items.Clear(); addListItem("File Written to C:\\KBTest.txt"); 

UPDATE 1 (Windows):

(I thought you were looking for System.Windows.Forms.SaveFileDialog )

Here is an example MSDN code for this:

 private void button2_Click(object sender, System.EventArgs e) { // Displays a SaveFileDialog so the user can save the Image // assigned to Button2. SaveFileDialog saveFileDialog1 = new SaveFileDialog(); saveFileDialog1.Filter = "JPeg Image|*.jpg|Bitmap Image|*.bmp|Gif Image|*.gif"; saveFileDialog1.Title = "Save an Image File"; saveFileDialog1.ShowDialog(); // If the file name is not an empty string open it for saving. if(saveFileDialog1.FileName != "") { // Saves the Image via a FileStream created by the OpenFile method. System.IO.FileStream fs = (System.IO.FileStream)saveFileDialog1.OpenFile(); // Saves the Image in the appropriate ImageFormat based upon the // File type selected in the dialog box. // NOTE that the FilterIndex property is one-based. switch(saveFileDialog1.FilterIndex) { case 1 : this.button2.Image.Save(fs, System.Drawing.Imaging.ImageFormat.Jpeg); break; case 2 : this.button2.Image.Save(fs, System.Drawing.Imaging.ImageFormat.Bmp); break; case 3 : this.button2.Image.Save(fs, System.Drawing.Imaging.ImageFormat.Gif); break; } fs.Close(); } } 

And as they say, you need to register an event handler:

 this.button2.Click += new System.EventHandler(this.button2_Click); 

UPDATE 2 (Internet):

Ok, I see that you are really looking for a web solution. I was thinking about Windows instead of the Web. Here is what you need to do on the web page. This is an example of VB.NET, but I assume you can translate:

 string FileName = "Hello.doc"; string Filename = strFileName.Substring(0, FileName.LastIndexOf(".")); Response.AppendHeader("Content-Disposition", "attachment; filename=" + FileName); Response.TransmitFile(Server.MapPath("~/Folder_Name/" + FileName)); Response.End(); 

I have made variations of this approach several times. You send the XML file back to the user's browser. As soon as he gets there, the user must decide what to do with him (often, "open this file or save it?"). This is under the control of the browser.

You can help the browser by including a content type header that tells the browser which file it is looking for and what it should try to open. Therefore, this header will ask him to call Excel:

 Response.AppendHeader("Content-Type", "application/vnd.ms-excel"); 

You will often see the / pdf application that pulls out Adobe Reader or any other PDF reader that the user uses. Here you will want to use the content type "text / xml". The type of content is less critical here, because we intend to save the file, and not open it using the application.

There is some issue with the content of attachment compared to inline. Try both ways and see which one is best for you. One of them (I can't remember) basically ignores the suggested file name that you send it to, and instead uses the name of its ASP page. Very annoying. Firefox at least documents this as a bug, IE seems to just consider this another “feature”.