Monday, December 14, 2009

Read XML In to the SQL table.

We can read whole xml file into the sql table using Bulk Copy command in SQL server. some time this command gives Exception "you do not have permission to use the bulk load statement". It says that you must have assigned rights to execute bulk load statement.this can be assigned by database administrator.

After reading file from specific location using bulk load command we can query it using built-in SQL commands, which are shipped with installation sql server 2005.

As sql server 2005 supports XML datatype as native datatype. it will be very easy to convert xml into SQL table.

Here is the code to read from xml file and out them as sql table.

declare @xml as xml

declare @xml xml SELECT @xml = BulkColumn FROM OPENROWSET(BULK 'c:\states.xml', SINGLE_BLOB) TempXML; SELECT Tab.Col.value('@ID','varchar(2)') as ID,Tab.Col.value('@Name','varchar(20)') from @xml.nodes('states/state') Tab(Col)

hope this will help. Thanks.