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

3 kommentarer:

  1. Good stuff. Just a note to anyone using this method. If you set this up in a production environment to run scheduled then make sure to use try catch around the OPENXML/select statement and remove the document in memory even if the select for some reason errors (more probable if you use insert into than select into). It is really frustrating that you cannot remove the document from memory later, the only way to free up that memory is to restart the SQL Server Service.

    SvaraRadera
  2. Msg 6365, Level 16, State 1, Line 1
    An XML operation resulted an XML data type exceeding 2GB in size. Operation aborted.


    how i soluve this prob

    SvaraRadera