Import XML Tables Into SQLExpress

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 2005 Forums

Express Edition and Compact Edition (2005)

Import XML Tables Into SQLExpress

Author

Topic

cbjroms
Starting Member

13 Posts

Posted - 2008-02-21 : 08:01:57

I am an SQL newbie and despite 3 hours on the Internet this morning I am still confused!

I have some XML tables that I want to import into my SQLExpress database.

I have followed the example at http://support.microsoft.com/kb/316005 How to import XML into SQL Server with the XML Bulk Load component.

I understand the example but when I run the vb code:

Set objBL = CreateObject("SQLXMLBulkLoad.SQLXMLBulkLoad")
objBL.ConnectionString = "provider=SQLOLEDB.1;data source=MySQLServer;database=MyDatabase;uid=MyAccount;pwd=MyPassword"
objBL.ErrorLogFile = "c:\error.log"
objBL.Execute "c:\customermapping.xml", "c:\customers.xml"
Set objBL = Nothing

I get an error message saying that ActiveX cannot create object 'SQLXMLBulkLoad.SQLXMLBulkLoad'.

I have SSMSE and the databse on my PC harddrive.

What am I missing here?

Thanks in antcipation.

Chris

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2008-02-21 : 08:18:30

Seems like you don't have SQLXML installed on your box. Download and install SQLXML 2.0 or 3.0 from MS website.

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

cbjroms
Starting Member

13 Posts

Posted - 2008-02-21 : 08:53:27

Thanks Harsh,

I feel very stupid that I had not realised this!

Can I also ask about the connection string that I should be using?

The example that I have is:
objBL.ConnectionString = "provider=SQLOLEDB.1;data source=MySQLServer;database=MyDatabase;uid=MyAccount;pwd=MyPassword"

What should I enter as my:
Provider - does it stay as SQLOLEDB?
Data source - my server name is WS04\SQLEXPRESS
Database - name is MyDatabase so I guess that I leave this
UID & Password - when I look at my database through MSSMSE username is DC\Admin and databse is set to Windows authentication.

Thanks in anticipation.

Chris

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2008-02-21 : 08:57:21

You can choose appropriate connection string for you here:

http://www.connectionstrings.com/?carrier=sqlserver2005

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2008-02-21 : 08:58:53

you could also do it from the server
http://weblogs.sqlteam.com/mladenp/archive/2007/06/18/60235.aspx

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp
SSMS Add-in that does a few things: www.ssmstoolspack.com

cbjroms
Starting Member

13 Posts

Posted - 2008-02-21 : 09:34:51

Thank you both very much for your responses.

I ended-up using the Data Link Properties tool.

I have looked at
http://weblogs.sqlteam.com/mladenp/archive/2007/06/18/60235.aspx but am note sure that I understand it!

If I stick with the method that I have been trialing then I need to map the format of the data source XML to the format of the SQL table that I want to put it into.

Are there any simple tools out there to do this?

Thanks in anticipation.

Chris

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources