Slow XML import with SQL server

Question

I have a XML file with a size of 1GB. I use the following code to load the data into sql server.

DECLARE @xmlvar XML
SELECT @xmlvar = BulkColumn
FROM OPENROWSET(BULK 'C:\Data\demo.xml', SINGLE_BLOB) x;

WITH XMLNAMESPACES(DEFAULT 'ux:no::ehe:v5:actual:aver',
                            'ux:no:ehe:v5:move' AS ns4,
                            'ux:no:ehe:v5:cat:fill' as ns3,
                            'ux:no:ehe:v5:centre' as ns2)
SELECT

        zs.value(N'(../@versionCode)', 'VARCHAR(100)') as versionCode,
        zs.value(N'(@Start)', 'VARCHAR(50)') as Start_date,
        zs.value(N'(@End)', 'VARCHAR(50)') as End_date

into testtbl

FROM @xmlvar.nodes('/ns4:Dataview1/ns4:Content/ns4:gen') A(zs);

I takes now more than 2 hours to run the query and it is not finished. I have tested the query with a smaller version of the XML file and that works. Any tips on improving the loading speed?

Thank you.

Update XML file:

<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<ns4:Dataview1 xmlns="ux:no::ehe:v5:actual:aver" xmlns:ns4="ux:no:ehe:v5:move">
    <ns4:Content versionCode="16000">
        <ns4:gen start="1961-07-01" end="1961-07-01">            
        </ns4:gen>
        <ns4:gen start="2017-09-19">            
        </ns4:gen>
        <ns4:gen start="1961-07-02" end="2016-09-30">           
        </ns4:gen>
        <ns4:gen start="2016-10-01" end="2017-09-18">            
        </ns4:gen>      
    </ns4:Content>
  </ns4:Dataview1>

Answer 1

(1) As @Stu already pointed out, loading XML file first into a single row table will speed up the process of loading significantly.

(2) it is not a good idea to traverse XML up in the XPath expressions. Like here:

c.value('../@versionCode', 'VARCHAR(100)') as versionCode

But the XML structure was not shared in the question. So, it is impossible to suggest anything concrete.

2nd CROSS APPLY is simulating 1-to-many relationship in the XML hierarchy.

Check it out below.

SQL

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

INSERT INTO tbl(XmlColumn)
SELECT * FROM OPENROWSET(BULK N'C:\Data\demo.xml', SINGLE_BLOB) AS x;

WITH XMLNAMESPACES(DEFAULT 'ux:no::ehe:v5:actual:aver',
                            'ux:no:ehe:v5:move' AS ns4,
                            'ux:no:ehe:v5:cat:fill' as ns3,
                            'ux:no:ehe:v5:centre' as ns2)
SELECT c.value('@versionCode', 'VARCHAR(100)') as versionCode,
    x.value('@start', 'DATE') as Start_date,
    x.value('@end', 'DATE') as End_date
INTO dbo.testtbl
FROM tbl
    CROSS APPLY XmlColumn.nodes('/ns4:Dataview1/ns4:Content') AS t1(c)
    CROSS APPLY t1.c.nodes('ns4:gen') AS t2(x);

Answer 2

In my opinion it's better to use an SSIS Package for importing XML files. It has a component named "XML Source" for loading XML file.

There is a useful article at : https://www.sqlshack.com/import-xml-documents-into-sql-server-tables-using-ssis-packages/