What is the best way to import this very large xml file into a SQL Server database

Kim Lahn Nielsen 0
Viewed 29

I have a XML file with 95 gb of data (1444 mio rows). I need to import some of the data into a SQL Server table.

I have made a sample file that I'm trying to import into my SQL Server with the following code. I don't get any errors, but I also don't get any data in the table.

Sample file: https://1drv.ms/u/s!AnJeuk8W8KbEjblueb6bjKaDWJ5XAw?e=2molDZ

CREATE DATABASE DMR_DB2
GO

USE DMR_DB2
GO

CREATE TABLE XMLwithOpenXML
(
    Id INT IDENTITY PRIMARY KEY,
    XMLData XML,
    LoadedDateTime DATETIME
)

INSERT INTO XMLwithOpenXML(XMLData, LoadedDateTime)
    SELECT CONVERT(XML, BulkColumn) AS BulkColumn, GETDATE() 
    FROM OPENROWSET(BULK 'C:\Users\kn\Desktop\ESStatistikListeModtag-20220911-222128\Test.xml', SINGLE_BLOB) AS x;

--SELECT * FROM XMLwithOpenXML


DECLARE @XML AS XML, @hDoc AS INT, @SQL NVARCHAR (MAX)

SELECT @XML = XMLData FROM XMLwithOpenXML

EXEC sp_xml_preparedocument @hDoc OUTPUT, @XML

SELECT KoeretoejIdent, KoeretoejArtNummer, KoeretoejArtNavn
FROM OPENXML(@hDoc, 'ESStatistikListeModtag_I/StatistikSamling/Statistik')
WITH 
(
KoeretoejIdent [varchar](50) '@KoeretoejIdent',
KoeretoejArtNummer [varchar](100) '@KoeretoejArtNummer',
KoeretoejArtNavn [varchar](100) 'KoeretoejArtNavn'
)

EXEC sp_xml_removedocument @hDoc
GO
xml import sql-server
1 Answers

Please try the following solution.

It works for your small XML sample.

Microsoft proprietary OPENXML() and its companions sp_xml_preparedocument and sp_xml_removedocument are kept just for backward compatibility with the obsolete SQL Server 2000. Their use is diminished just to very few fringe cases. Starting from SQL Server 2005 onwards, it is strongly recommended to re-write your SQL and switch it to XQuery.

As @Lamu already pointed out, SQL Server XML type column can hold up to 2 GB XML.

For 95 GB you would need to use SSQL Server Integration Services (SSIS).

SSIS has no limitation on the XML fie size. It will handle whatever the Operation System (OS) file system allows.

SQL

USE tempdb;
GO

DROP TABLE IF EXISTS dbo.tbl;

CREATE TABLE tbl (
    ID INT IDENTITY(1, 1) PRIMARY KEY,
    XmlColumn XML
);

INSERT INTO tbl(XmlColumn)
SELECT * FROM OPENROWSET(BULK N'c:\Downloads\Test.xml', SINGLE_BLOB) AS x;

WITH XMLNAMESPACES(DEFAULT 'http://skat.dk/dmr/2007/05/31/')
SELECT c.value('(KoeretoejIdent/text())[1]', 'VARCHAR(50)') as KoeretoejIdent
    , c.value('(KoeretoejArtNummer/text())[1]', 'INT') as KoeretoejArtNummer
    , c.value('(KoeretoejArtNavn/text())[1]', 'NVARCHAR(50)') as KoeretoejArtNavn
FROM tbl
   CROSS APPLY XmlColumn.nodes('/ESStatistikListeModtag_I/StatistikSamling/Statistik') AS t(c);
Yitzhak Khabinsky0
Related