SQL Server: create db table from xml schema

grahamor

Hi,

Just wondering if anyone has any tips/advice on doing this.

I heard xmlSpy is good for this too, has anyone used it ?

Thanks for any advice

FindingNemo

grahamor wrote: »

Hi,

Just wondering if anyone has any tips/advice on doing this.

I heard xmlSpy is good for this too, has anyone used it ?

Thanks for any advice

http://www.informit.com/articles/article.aspx?p=102307&seqNum=13&rl=1
have a look near the bottom where it talks about generating the table itself with an example

Generating Database Schemas
In addition to loading data into existing tables, the XML Bulk Copy component can also create target tables for you if they do not already exist, or drop and recreate them if they do exist. To create nonexistent tables, set the component's SchemaGen property to True, as shown in Listing 18.80.

Listing 18.80
Set objBulkLoad = CreateObject("SQLXMLBulkLoad.SQLXMLBulkLoad")
objBulkLoad.ConnectionString = _
"provider=SQLOLEDB;data source=KUFNATHE;database=Northwind;" & _
"Integrated Security=SSPI;"
objBulkLoad.SchemaGen = True
objBulkLoad.Execute "d:\xml\OrdersSchema.xml",
"d:\xml\OrdersData.xml"
Set objBulkLoad = Nothing

grahamor

Thanks for the link. Here is my code so far :

<%@ Language=VBScript %>
<%
Set objBulkLoad = Server.CreateObject("SQLXMLBulkLoad.SQLXMLBulkLoad")



objBulkLoad.ConnectionString = "Provider=SQLOLEDB;Server=xxx\SQLEXPRESS;Database=Gtest;Trusted_Connection=yes"



objBulkLoad.ErrorLogFile = "c:\xml\error.log" 

objBulkLoad.SchemaGen = True
objBulkLoad.SGDropTables = True
objBulkLoad.Execute "c:\xml\countriesSchema.xml","c:\xml\countriesInfo.xml"


Set objBulkLoad = Nothing
%>

This seems to run without an errors but nothing is getting created in the DB ?

Could this be a permission issue ? im trying to load it into a sql2000 db on shared hosting

Thanks again

FindingNemo

grahamor wrote: »

Thanks for the link. Here is my code so far :

<%@ Language=VBScript %>
<%
Set objBulkLoad = Server.CreateObject("SQLXMLBulkLoad.SQLXMLBulkLoad")



objBulkLoad.ConnectionString = "Provider=SQLOLEDB;Server=xxx\SQLEXPRESS;Database=Gtest;Trusted_Connection=yes"



objBulkLoad.ErrorLogFile = "c:\xml\error.log" 

objBulkLoad.SchemaGen = True
objBulkLoad.SGDropTables = True
objBulkLoad.Execute "c:\xml\countriesSchema.xml","c:\xml\countriesInfo.xml"


Set objBulkLoad = Nothing
%>

This seems to run without an errors but nothing is getting created in the DB ?

Could this be a permission issue ? im trying to load it into a sql2000 db on shared hosting

Thanks again

You say you're trying to load into a sql 2000 db but your connection string looks like it's for sql 2005 ?

grahamor

ok, i changed the connection string to the following but still no luck.

objBulkLoad.ConnectionString = "Provider=SQLOLEDB;Server=mssql.hosting365.ie;Database=xxx;Uid=xxx;Pwd=xxx"

I was getting an error before when the connection string was incorrect

FindingNemo

grahamor wrote: »

ok, i changed the connection string to the following but still no luck.

objBulkLoad.ConnectionString = "Provider=SQLOLEDB;Server=mssql.hosting365.ie;Database=xxx;Uid=xxx;Pwd=xxx"

I was getting an error before when the connection string was incorrect

Ok, so it is a sql 2000 database then ?

Are you getting any errors in the event viewer ?

grahamor

im connecting to a hosting365 mssql 2000 db through the 2005 management studio program.

i checked the event viewer on my local machine but the errors would get logged on the server where the db is , right ?

FindingNemo

grahamor wrote: »

im connecting to a hosting365 mssql 2000 db through the 2005 management studio program.

i checked the event viewer on my local machine but the errors would get logged on the server where the db is , right ?

That's correct,

It's very strange if you get no error that the tables are not created !

Have you tried the sample on the microsoft site, where you actually create the tables and then import the data to see if a simple one works ?

grahamor

Thanks for all the help FindingNemo

I tried the microsoft one and it worked perfect, so it must be my schema or something similar that causes issues.

FindingNemo

Unforunately it must be the schema alright if the samples work ok.

Good luck with it