All geek questions in one place

Sql server openrowset for reading huge xml files in one step

This is my first post ever ... and I really need help with this, so anyone who has any knowledge on this subject, please help!

What I need to do is read the XML file in the sql server data tables. I searched many times for solutions for this and found some relevant ones. The problem is the size of the downloadable xml. It weighs 2 GB (and will be 10 GB). I managed to do this, but I saw one specific solution that seems great to me, but I can't figure it out.

Ok lets go to the point. I am currently doing this as follows:

  • I read all the XML using openrowset in a variable. (it takes up all the RAM memory)
  • next I use the .node () object to pull data and populate it with tables.

This is a two-step process. I was wondering if I could do this in just one step. I saw that there are files such as format files, and there are Numus examples on how to use this to extract data from flat files or even Excel documents at the base of the record (instead of sucking it all into a variable), but I CANNOT find any example that would show how to read this huge XML file into a table, analyzing the data on the fly (based on the format file). Is it possible? I would really appreciate help, or for finding a good example.

Forgive my English - it was a long time since I had to write so much in this language :-) Thanks in advance!

+4
source share
1 answer

For very large files, you can use SSIS: Loading XML data in SQL Server 2008

This gives you the flexibility to transform XML data, and also reduces the amount of memory for very large files. Of course, this can be slower compared to using OPENROWSET in BULK mode.

+5
source

Source: https://habr.com/ru/post/1336072/

More articles:


All Articles