CODEWITHSUNDEEP
xmlsql-server-2008
cdiazal
cdiazal

Reputation: 1008

Load large XML into SQL Server 2008

I have to load a xml into a sql server table. The xml size is about 17 Mb. I am trying with the following code:

DECLARE @tempTable TABLE (
codesthot NVARCHAR(50),
codpobhot NVARCHAR(50),
hot_codigo NVARCHAR(50),
hot_codcobol NVARCHAR(50),
hot_coddup NVARCHAR(50),
hot_afiliacion NVARCHAR(50)
)

DECLARE @xml XML
SET @xml='<hotel>
                <codesthot><![CDATA[ABALK]]></codesthot>
                <codpobhot><![CDATA[34815]]></codpobhot>
                <hot_codigo><![CDATA[265369]]></hot_codigo>
                <hot_codcobol><![CDATA[724224]]></hot_codcobol>
                <hot_coddup><![CDATA[724224]]></hot_coddup>
                <hot_afiliacion><![CDATA[RS]]></hot_afiliacion>
            </hotel>
...   

INSERT INTO @tempTable
SELECT Tbl.Col.value('codesthot[1]', 'NVARCHAR(50)'),
Tbl.Col.value('codpobhot[1]', 'NVARCHAR(50)'),
Tbl.Col.value('hot_codigo[1]', 'NVARCHAR(50)'),
Tbl.Col.value('hot_codcobol[1]', 'NVARCHAR(50)'),
Tbl.Col.value('hot_coddup[1]', 'NVARCHAR(50)'),
Tbl.Col.value('hot_afiliacion[1]', 'NVARCHAR(50)')
FROM   @xml.nodes('//hotel') Tbl(Col)

SELECT * FROM @tempTable

It is running for 5 hours and I don´t know how long could it take. What other way can I try?

Thanks!

Upvotes: 1

Views: 2702

Answers (1)

Bassam Mehanni
Bassam Mehanni

Reputation: 14944

You could try to make use of SqlBulkCopy

http://blogs.microsoft.co.il/blogs/kim/archive/2009/06/15/importing-large-xml-files-to-sql-server-using-sqlbulkcopy.aspx

Upvotes: 2

Related Questions