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.

No comments:

Post a Comment

Comments posted on ASP.Net Ajax Tutorials Blog are moderated and will be approved only if they are on-topic and not abusive. Please email me or my team for tech-support or blogging related questions. Avoid including website URLs in your comments - Thanks Author