How to import a large xml file into SQL Server 2008. After some struggling with poor performance using xQuery, I found this way for parsing a large xml file without having a schema file.
The file was approximately 200 MB, containing ~500 000 rows in total and took 25s to import.
The file was more or less in the format of
Declare @xmlfile xml
Declare @docHandle int
SELECT @xmlfile=InvoicesXML FROM (
SELECT * FROM OPENROWSET(BULK 'C:\Documents and Settings\XPMUser\Skrivbord\cost_export.xml',
SINGLE_BLOB) AS Invoices ) AS Invoices(InvoicesXML)
EXEC sp_xml_preparedocument @docHandle OUTPUT, @xmlfile
SELECT * INTO Costs
FROM OPENXML(@docHandle, N'/dsCost/ttHotelCost',2)
WITH (hotelCostId int, costTypeId int ,destination varchar(200))
EXEC sp_xml_removedocument @docHandle