Improve performance of converting a large xml file (~300 MB) to relational table in SQL Server So this is what I have so far:

--Read xml content into a XML data type variable
DECLARE @FileData XML
SELECT @FileData = CONVERT(XML, BulkColumn)
FROM OPENROWSET(BULK '\\file_path\test.xml', SINGLE_BLOB) AS x
--Read from the XML variable to create Entity-Attribute-Value table
SELECT N1.Id.value('@Id', 'varchar(50)') as Id
    , N1.Id.value('@Name', 'varchar(100)') as Name
    , N2.AttributeLongName.value('@AttributeName', 'varchar(100)') as AttributeName
    , N3.AttributeValue.value('.', 'varchar(MAX)') as AttributeValue
FROM @FileData.nodes('/Data/Entities/Entity') as N1(Id) ---1st lvl Node contains the Entity
cross apply Id.nodes('Attributes/Attribute') as N2(AttributeName) --2nd lvl Node contains AttributeName
cross apply AttributeName.nodes('Values/Value') as N3(AttributeValue) --3rd lvl Node contains AttributeValue


This code performs decently when run against an 8 MB XML file (~15s). However, when I try to run it against a 300 MB XML file of the same structure, it takes hours. 

Does this mean that the XQuery nodes() method does not scale linearly or scales worst?

Is there any other way I can improve the performance on this?
I had another look at this and can reproduce your issue.  Try adding OPTION ( MAXDOP 1 ) to your query.  In my test rig with a 300MB file this ran in 1 min 42 seconds.  The unhinted version ran for 30 minutes at 100% CPU before I killed it.

You could also have a look at OPENXML.  People often say it's faster with large XML files and it appears to be in this case.  However you should be aware of the known issues with OPENXML ( eg can take 1/8th of buffer pool, is an old-fashioned COM .dll, you must call sp_xml_removedocument etc ).  Once you've researched the pros and cons of OPENXML, you could try something like this:

DECLARE @FileData XML

SELECT @FileData = BulkColumn
FROM OPENROWSET(BULK 'd:\temp\temp.xml', SINGLE_BLOB) AS x

DECLARE @hDoc int

EXEC sp_xml_preparedocument @hDoc OUTPUT, @FileData 

SELECT *
INTO #tmp
FROM OPENXML( @hDoc, '/Data/Entities/Entity/Attributes/Attribute/Values/Value', 1 ) 
WITH
    (
    Id VARCHAR(50) '../../../../@Id',
    Name VARCHAR(100) '../../../../@Name',
    AttributeName VARCHAR(100)  '../../@AttributeName',
    AttributeValue VARCHAR(MAX) '.'
    )

EXEC sp_xml_removedocument @hDoc


Honestly I try and avoid it these days due to those issues; what's the point of one query going faster when you've just lopped 1/8th of your buffer pool?

Finally, the quickest and most scalable method (IMHO) is SSIS.  This version ran in about 10 seconds for the same file as the above methods in my rig.

SSIS XML Import Method

Create a package, add a Data Flow task, add an XML Source and then each table. I created a 300MB file with the same structure as yours and it loaded in about 10 seconds, eg



Obviously you'll need extra time to query the tables you have imported but I think this is a safer method.  You could even scale it out with multiple packages.  Post back if you need more help with the package.