Post reply

Import of huge XML file

  • December 1, 2015 at 9:05 am

    #1843238

    subhashkmsbi - Read my post (the one just above yours).

  • December 4, 2015 at 10:52 pm

    #1844193

    subhashkmsbi (11/29/2015)

    Hi,

    Please suggest me how to load such huge XML into SQL Server which is of greater than 2gb in size.

    Here my requirement is my XML file is huge in size which of greater than 2gb (Even the size will be 20gb also).

    I need to load such huge XML file into a SQL Server Table column which is of XML data type.

    is there any work around to load such huge XML files. If yes please suggest me how to proceed on this.

    Your quick response is highly appreciated.

    Thanks in advance.

    Kindly do the needful.

    Options:

    1. (offered above) Parse file outside SQL Server using SQL XML Bulk Loader into normalized tables in database, i.e. do not try loading file as a single BLOB.

    2. (offered above) Parse file outside SQL Server using SSIS into normalized tables in database, i.e. do not try loading file as a single BLOB. Depending on the hierarchy this may not be a viable option given how SSIS rationalizes XML hierarchies into Outputs from the XML Source Component.

    3. Store data in a FileTable or FILESTREAM. This way you can deliver the file to a caller as a single stream of data but you will not be able to bring the data into an XML or String variable due to the 2GB limit.

    Option 1 & 2 are ETL exercises.

    Option 3 is an exercise in configuring one of these features in your database. Loading into your database is trivial after that, except for the time it will take for your database to digest a file that size.

    There are no special teachers of virtue, because virtue is taught by the whole community.
    --Plato

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

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