Improve performance of converting a large xml file (~300 MB) to relational table in SQL Server
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.