Post reply

Excel XML with XML Bulk Load OR OPENXML / INSERT ?

  • December 14, 2008 at 2:33 am

    #127434

    Hello,

    I need to load very large XML files in SQL Server 2005 (or 2008). These XML files are in an Excel format. I’m wondering if I should use XML Bulk Load (SQLXML 4.0) as introduced here: http://msdn.microsoft.com/en-us/library/ms171721(SQL.90).aspx?n=0. Or should I use the OPENXML (http://msdn.microsoft.com/en-us/library/ms186918.aspx) and INSERT statements.

    The XML files could be as large as 50MB, 500MB, or 20GB. The XML will be formatted like Excel XML, for example:

    <Workbook xmlns="urn:schemas-microsoft-com:office:spreadsheet"

    xmlns:o="urn:schemas-microsoft-com:office:office"

    xmlns:x="urn:schemas-microsoft-com:office:excel"

    xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet"

    xmlns:html="http://www.w3.org/TR/REC-html40">

    ...

    Does anyone have experience loading this kind of XML format using the Bulk Loader?

  • January 8, 2009 at 7:59 am

    #923343

    I don't know how far you are on with this but for what it's worth for anything over 1Gb I wouldn't use SSIS, use the bulkloader, SSIS XML parser is terrible and swallows memory like you wouldn't believe (it tries to read the whole file and then parse it). Depending on the complexity of your XSD it's all relatively simple and it runs like s**t off a shovel.

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply