StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p><em><strong>As of 2013...</em></strong></p> <p>The only time saving option in my opinion to load large/huge XML files in SQL Server is (as someone previously briefly mentioned) to use the <strong>SQLXML 4.0</strong> library.</p> <p>This is the solution I adopted to load huge XML files (<strong>7GB in size</strong>) on a daily basis. The previous process which was using C# manipulation in the Script Task took hours to complete. Using SQLXML 4.0 takes <strong>15-20 minutes</strong>. How to install SQLXML 4.0. step by step <a href="http://dwbiexpert.com/sqlxmlbulkload-ssis-task">here</a>. For practical examples in how to do it end to end <a href="http://msdn.microsoft.com/en-us/library/ms171806.aspx">follow this MSDN link</a>. </p> <p>My XML has also nested elements, so it's quite complex, the result is 10 tables with 2.5 to 4 million rows each (the daily file sometimes is more than 7GB). My work was based purely on information I learned and applied from the two links provided above.</p> <ul> <li><p><strong>Advantages</strong>:</p> <ul> <li>it's fast</li> <li>it's Microsoft (<a href="http://www.microsoft.com/en-gb/download/details.aspx?id=30403">http://www.microsoft.com/en-gb/download/details.aspx?id=30403</a>)</li> <li>SSIS package will be very much simplified</li> <li>you don't need to spend hours and hours to change the SSIS package if your XML schema changes. SQLXML is able to create the tables in the SQL Server for you every time you run the package, based on the XSD relationships you provide.</li> </ul></li> <li><p><strong>Disadvantages</strong></p> <ul> <li>creating the XSD may take a while and requires some knowledge. When I did it I learned something new, so this was not a real a disadvantage for me.</li> <li>when seeing how simple the SSIS package is, your manager will have the impression that you didn't do any work.</li> </ul></li> </ul> <p>To view large files use <a href="http://www.swiftgear.com/ltfviewer/features.html">Large Text File Viewer</a>, nice little gem.</p> <p><em><strong>Note: The question is quite old, but the "issue" remains hot. I added this post for the developers who Google how to BULK LOAD XML files in SSIS and land here.</em></strong></p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload