locked
Best way to store the large xml files RRS feed

  • Question

  • Hi,

     we have large xml files with sensitive information. what is the best practice to store the large xml files as it is Sql server database or file system?. why one vs another?. If the best way is database what is the datatype needs to be used. Right now we tried storing in database with next(max), nvarchar(max) as a text but having issues while retrieving the large file over 200 MB.

    Thanks


    Raga Kandimalla

    Tuesday, October 4, 2016 2:21 PM

Answers

All replies

  • 1.Depends of what you need... if you need to be able do resend the file as is once in a while. I would save the file on the hd and save the location in the db. You could also save it in a text column.

    But if you need to be able to search the xml file then I would suggest you import it in a normalized model in the db... That way it becomes easy to query the data.

    2.

    It probably depends on whether you'll be processing the XML files individually or if you need to process many XML files together.  If the latter, importing it into a normalized model in the DB is probably the way to go as you'll be able to use the power of SQL for comparisons, JOINs, etc.  However, if all you'll be doing is processing each XML file by itself, store it in a TEXT field and when you're ready to use the XML, read it from the DB and load the XML into an MSXML document object.  I have a system where the user goes thru a "wizard" to complete a 40+ page document, each section of which is an individual node of an XML file.  I store the XML in a SQL TEXT file and then extract it and call the loadXML method of the DOMDocument object to get an XML document object to work with.  

    http://www.sqlservercentral.com/Forums/Topic183115-9-1.aspx



     


    Please click Mark As Answer if my post helped.

    Tuesday, October 4, 2016 2:32 PM
  • storing xml into sql text file means as ntext data type. that is what we are doing now but we have an issue with the retrieval for large xml documents (over 200 mb). I am reading the text from sql server and parsing it as an xml and reading through the nodes to populate the data.

    Raga Kandimalla

    Tuesday, October 4, 2016 6:54 PM
  • Store them using a filestream column. With FileStream the contents can be returned outside of SQL Server's memory space if you use the SQLFileStream class.

    https://msdn.microsoft.com/en-us/library/cc716724%28v=vs.110%29.aspx?f=255&MSPPError=-2147217396


    looking for a book on SQL Server 2008 Administration? http://www.amazon.com/Microsoft-Server-2008-Management-Administration/dp/067233044X looking for a book on SQL Server 2008 Full-Text Search? http://www.amazon.com/Pro-Full-Text-Search-Server-2008/dp/1430215941

    Tuesday, October 4, 2016 7:10 PM