Load Huge XML file into SQL Server 2005 Table

I'm fairly certain you should use SQLXMLBulkLoad for this.  

Introduction, details, examples, limitiations (and some more):
http://msdn.microsoft.com/en-us/library/ms171993.aspx

.Net implementation:
http://msdn.microsoft.com/en-us/library/ms171878.aspx

You can use OPENROWSET and XML.nodes. This is another fast method.

DECLARE @xmlInput AS XML

SET @xmlInput =
(
SELECT * FROM OPENROWSET(
   BULK 'C:\1.xml',
   SINGLE_BLOB) AS x
)

--;WITH XMLNAMESPACES (default 'http://schemas.microsoft.com/linqtosql/dbml/2007')
SELECT
       T.C.value('@groupCode', 'varchar(255)') as 'groupCode'
      ,T.C.value('@groupName', 'varchar(255)') as 'groupName'
      ,T.C.value('(arrivalCode)[1]', 'varchar(255)') as 'arrivalCode'
      ,T.C.value('(arrivalName)[1]', 'varchar(255)') as 'arrivalName'
FROM @xmlInput.nodes('/outboundArrivalPoints/arrivalPoint') T(C)

dbaSql thanks for suggestion, however a quick example would help me expediate my project.

Ram4020, my XML has 40+ columns, so that means i have to do a Select for 40 Columns? also your example does not show syntax for INSERT, its select only... please advise.

Thanks

understood, adilkhan.  i am beyond buried right now.  i will try to get something to you, but i'm not sure exactly when

adikhan, try to first execute the SELECT. Check if it is working. Compare the performance. If the performance is worse that your old method then it does not make sense to work on this.

INSERT is not big deal. Write a INSERT stmt before the SELECT.

INSERT INTO (column1, column2, ...)
SELECT
       T.C.value('@attribute1', 'varchar(255)') as 'column1'
...

Note in the SELECT the first 2 columns are atributes and the next 2 are elements.

Following Codes returns 0 Rows...

DECLARE @xml XML
SET @xml =
(
SELECT * FROM OPENROWSET(
   BULK 'C:\SpecBrands\alfco.xml',
   SINGLE_BLOB) AS x
)

Select T.C.value('brand_code[0]','varchar(1000)') as 'Brand'
From @xml.nodes('/product_data/product_specs/classification') T(C)


Sample Xml...

<?xml version="1.0" encoding="ISO-8859-1"?>
<product_data xmlns="http://www.specsserver.com/appliance_specs_xml_2_0.xsd" xsi:schemaLocation="http://www.specsserver.com/appliance_specs_xml_2_0.xsd" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
   <product_specs>
      <classification>
         <key>ALFCO:IAIC</key>
         <brand_code>ALFCO</brand_code>
         <brand_name>Alfresco</brand_name>
....

adilkhan, take a look at the .Net reference I pasted above.  There's a very detailed example from A to Z:
http://msdn.microsoft.com/en-us/library/ms171878.aspx

Pretty decent example here as well:
http://dotnetslackers.com/Community/forums/performing-bulk-insert/p/1890/26773.aspx

Great examples Thanks, is there a way to generate a Schema file quickly based upon table structure (which will accept load). My xml file has way too many elements, creating a schema could be very complex.

Thanks

You have to mention the namespace before SELECT.

DECLARE @xml XML
SET @xml =
(
SELECT * FROM OPENROWSET(
   BULK 'C:\SpecBrands\alfco.xml',
   SINGLE_BLOB) AS x
)

;WITH XMLNAMESPACES (default 'http://www.w3.org/2001/XMLSchema-instance')
Select T.C.value('brand_code[0]','varchar(1000)') as 'Brand'
From @xml.nodes('/product_data/product_specs/classification') T(C)

I dont think I can use SqlBulk, I believe for that to work I must have Exact # of Columns in my Tables VS in the xml file, sometomes few option XML elements are missing in my XML file. maybe I am wrong.

Ram4020...its still returning 0 Rows... attached is my XML file

DECLARE @xml XML
SET @xml =
(
SELECT * FROM OPENROWSET(
   BULK 'C:\SpecBrands\alfco.xml',
   SINGLE_BLOB) AS x
)
 
;WITH XMLNAMESPACES (default 'http://www.w3.org/2001/XMLSchema-instance')
Select T.C.value('brand_code[1]','varchar(1000)') as 'Brand'
From @xml.nodes('/product_data/product_specs/classification') T(C)

                                              

1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:

I would appericate a little help here...

My XML file is 500MB and OpenRowSet is not working(above code). SqlBulk Insert is complaining about xml's complex type. plus its so hard to generate a XSD for 500MB xml file.

SSIS is not accepting nested XML tags as well.

What should I do. please advise.

ok then... none of the suggestions provided above worked for me. I've ended up building my own VB.NET application and parsed every single node.

I guess when XML is complex best way is manuall coding.

Thanks