Inserting data into a table from XML file

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Inserting data into a table from XML file

Author

Topic

daksri
Starting Member

6 Posts

Posted - 2008-05-28 : 12:45:49

Can you please let me know how do I insert data into a table from XML file ? I am using SQL-Server 2005.
Thank you in advance.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-05-28 : 12:52:53

You can use either the BULKINSERT or OPENROWSET. Have a look at this:-

http://msdn.microsoft.com/en-us/library/ms191184.aspx

daksri
Starting Member

6 Posts

Posted - 2008-05-28 : 13:08:11

Thank you for the link. There is no XML datatype in the server, do you know why ?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-05-28 : 13:21:51

quote:
Originally posted by daksri

Thank you for the link. There is no XML datatype in the server, do you know why ?

Are you using SQL 2000? XML datatype is availale only in SQL 2005 with compatibility level 90. You may use a text datatype to store xml data if xml datatype is not available.

daksri
Starting Member

6 Posts

Posted - 2008-05-28 : 13:31:07

I am using the following code, and throws an error..
(Msg 156, Level 15, State 1, Line 3
Incorrect syntax near the keyword 'BULK'.)

CREATE TABLE #temp (dt DATETIME,col1 text NOT NULL)

INSERT #temp
SELECT GETDATE(), *
FROM OPENROWSET('D:\PFiles\sub.xml',SINGLE_BLOB) AS x;

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-05-28 : 13:33:04

quote:
Originally posted by daksri

I am using the following code, and throws an error..
(Msg 156, Level 15, State 1, Line 3
Incorrect syntax near the keyword 'BULK'.)

CREATE TABLE #temp (dt DATETIME,col1 text NOT NULL)

INSERT #temp
SELECT GETDATE(), *
FROM OPENROWSET('D:\PFiles\sub.xml',SINGLE_BLOB) AS x;

Are you using SQL 2000?

daksri
Starting Member

6 Posts

Posted - 2008-05-28 : 13:37:18

Yes, SQL 2000

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2008-05-28 : 13:42:14

moved from Transact-SQL (2005) forum.

_______________________________________________
Causing trouble since 1980
Blog: http://weblogs.sqlteam.com/mladenp
Speed up SSMS development: www.ssmstoolspack.com <- version 1.0 out!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-05-28 : 13:47:06

quote:
Originally posted by daksri

Yes, SQL 2000

Then i dont think this will work. BLOB is not available in sql 2000.
You might have to use OPENXML then.

http://msdn.microsoft.com/en-us/library/aa276847(SQL.80).aspx

daksri
Starting Member

6 Posts

Posted - 2008-05-28 : 14:22:47

Can someone help me on this ?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources