How to make this xml query faster

Question

I have this query:

DECLARE @DatatypenNEN3610 NVARCHAR(256)
DECLARE @Objecten NVARCHAR(256)

SET @DatatypenNEN3610 = '''http://www.kadaster.nl/schemas/lvbag/imbag/datatypennen3610/v20200601'' as DatatypenNEN361'
SET @Objecten = ''' http://www.kadaster.nl/schemas/lvbag/imbag/objecten/v20200601'' as Objecten'

DECLARE @Filepath varchar(100)

SET @Filepath = 'C:\Test\Test.xml'

DECLARE @sql NVARCHAR(MAX);

SET @sql = '
;WITH XMLNAMESPACES ('+ @DatatypenNEN3610 +' , '+ @Objecten +')

select
    r.x.value(''*:identificatie[1]'', ''Nvarchar(50)'') AS Identifictie,
    r.x.value(''*:oorspronkelijkBouwjaar[1]'',''Nvarchar(10)'') As Bouwjaar
FROM (  
        SELECT CAST(MY_XML AS xml)  
        FROM OPENROWSET(BULK ''' + @Filepath + ''', SINGLE_BLOB) AS T(MY_XML)  
    ) AS T(MY_XML)

CROSS APPLY t.MY_XML.nodes(''/*:bagStand/*:standBestand/*:stand/*:bagObject/*:Pand'') r ( x )
';

exec sp_executesql @sql;

It works fine. But it is very, very very slow. Can someone help me trick to make it faster?

Answer 1

Check it out how to shred XML efficiently.

SQL

/*
Step #1: Load XML file into single row db table
*/
DECLARE @tbl TABLE (XMLColumn XML);
DECLARE @Filepath VARCHAR(100) = 'C:\Test\Test.xml';

DECLARE @sql NVARCHAR(MAX) = 
N'SELECT BulkColumn 
   FROM Openrowset(Bulk ' + QUOTENAME(@Filepath, NCHAR(39)) + ', Single_Blob) x;';

PRINT @sql;

INSERT INTO @tbl (XMLColumn)
EXEC sys.sp_executesql @sql;

-- just to see
SELECT * FROM @tbl;

/*
Step #2: Shred XML without dynamic SQL
*/
WITH XMLNAMESPACES (/* we need to see your XML sample to declare and use XML namespaces*/)
SELECT c.value('(*:identificatie/text())[1]', 'NVARCHAR(50)') AS Identifictie,
    c.value('(*:oorspronkelijkBouwjaar/text())[1]', 'NVARCHAR(10)') As Bouwjaar
FROM @tbl
CROSS APPLY XMLColumn.nodes('/*:bagStand/*:standBestand/*:stand/*:bagObject/*:Pand') AS t(c);