fredag 3 februari 2012

Import large XML file into SQL Server

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
<dsCost ...>
<ttXXX>
</ttXXX>
<ttYYY>
</ttYYY>
<ttHotelCost>
<hotelCostId>1<hotelCostId>
<destination>Sweden</destination>
</ttHotelCost>
</dsCost>

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