Create tables in SQL2K from XSD schema?

I am in need of some help!
Relatively new to the whole XML thing as far as SQL server goes...
I have an XSD schema that describes a bunch of tables with various
relationships. Is there a way for me to create tables and relationships based
on the XSD file.
I have tried the net but seem to be unable to find anything. Any help would
be greatly appreciated.
Matt
If at first you don't succeed... Hide the evidence that you tried!
In theory yes. Please see the link
http://support.microsoft.com/default...;en-us;316005. You have to
set SchemaGen property SQLXMLBulkLoad to true. BTW creating a schema from
hand is a very daunting task and I never able to make it work for complex
shcemas. Are you using some tool to generate the XSD shcema annotaion?
"Director - Minvent" wrote:

> I am in need of some help!
> Relatively new to the whole XML thing as far as SQL server goes...
> I have an XSD schema that describes a bunch of tables with various
> relationships. Is there a way for me to create tables and relationships based
> on the XSD file.
> I have tried the net but seem to be unable to find anything. Any help would
> be greatly appreciated.
> Matt
> --
> If at first you don't succeed... Hide the evidence that you tried!
|||The schema was written using XMLspy by one of the guys at Agilent
technologies (HP) as part of one of their products... Unfortunately it is not
intended for use and therefore is not supported, but hopefully tested prior
to release!
Basically it is the definition of an xml output file from an analytical
instrument which I want to capture data from. Hence trying to create a
database based on its definition.
I'm using VB.net and desperately trying to find a namespace containing
sqlxml?!? I am probably being really stupid... which wouldn't suprise me!.
Cheers
"Rashid" wrote:
[vbcol=seagreen]
> In theory yes. Please see the link
> http://support.microsoft.com/default...;en-us;316005. You have to
> set SchemaGen property SQLXMLBulkLoad to true. BTW creating a schema from
> hand is a very daunting task and I never able to make it work for complex
> shcemas. Are you using some tool to generate the XSD shcema annotaion?
> "Director - Minvent" wrote:

Create tables from master table

Hello all, I am looking for some help with the following:

I need to create individual tables from a master table, all columns will be copied and the selection is based on the values within one column - I need to create as many tables as there are distinct values in this column (there are around 60 distinct values).

I have a PL/SQL command to do this but this doesn't work in Transact-SQL, can this be converted to work? Is there a convert facility somewhere for this?

The PL/SQL command I have is:

set serveroutput on size 1000000

declare
cursor c_dept is
select distinct deptno
from emp;

v_sql varchar2(1000);
begin
for r_dept in c_dept loop
dbms_output.put_line ('dept: '||r_dept.deptno);

v_sql := 'create table kl_'||r_dept.deptno||
' as (select * from emp where deptno = '''||r_dept.deptno||''')';

dbms_output.put_line(v_sql);

execute immediate v_sql;
end loop; -- dept
end;
/

Any help would be greatly appreciated.

In sql server also you can do this,

Code Snippet

Create Table Emp

(

EmpId int,

DeptName varchar(10)

)

Insert Into Emp Values(1,'Sales')

Insert Into Emp Values(2,'Sales')

Insert Into Emp Values(3,'Sales')

Insert Into Emp Values(4,'Finance')

Insert Into Emp Values(5,'IT')

Code Snippet

--SQL Server 2005

Declare @.SQL as Varchar(max);

Declare @.PreparedSQL as Varchar(8000);

Set @.PreparedSQL=

'If exists (select * from dbo.sysobjects where id = object_id(N''[dbo].[kl_?]'') and OBJECTPROPERTY(id, N''IsTable'') = 1)

Drop Table [dbo].[kl_?];

Select * Into [kl_?] From Emp Where Deptname=''?'';'

Set @.SQL=''

Select @.SQL = @.SQL + Replace(@.PreparedSQL,'?',deptname) From

(

Select Distinct Deptname from Emp

) as Depts

Exec (@.SQL);

Code Snippet

--SQL Server 2000

Declare @.Depts table (RowId int Identity(1,1), Deptname varchar(10));

Declare @.SQL as Varchar(8000);

Declare @.PreparedSQL as Varchar(8000);

Declare @.I as int;

Set @.PreparedSQL=

'If exists (select * from dbo.sysobjects where id = object_id(N''[dbo].[kl_?]'') and OBJECTPROPERTY(id, N''IsTable'') = 1)

Drop Table [dbo].[kl_?]

Select * Into [kl_?] From Emp Where Deptname=''?'';'

Insert into @.Depts

Select Distinct Deptname From Emp;

Select @.I = 1;

While Exists(Select 1 From @.Depts Where RowId=@.I)

Begin

Select @.SQL = Replace(@.PreparedSQL,'?',deptname)

From @.Depts

Where Rowid=@.I;

--Print(@.SQL);

Exec(@.SQL)

Set @.I=@.I+1;

End

You can utilize the Indexed Views also. See Indexed views on Books Online.

|||Absolute star, thanks very much. Problem solved.

Create Tables Dynamically

Hi! I am using VS 2003 (v 1.1)

I need to generate tables with the values from the Backend (SQLServer 2000) database in C#.Net.

How can i create the tables, tablerows, cells, etc. from the codebehind page of C#. I am having a very little knowledge about dynamic generation.

Give me the complete code with can example (if possible)

Thanks & Regrads

Jai Shankar

Here's anexample of how to create dynamic controls and add events. You can apply this to any type of control you need (in you case a Table).

create tables and insert data in sql server mobile on dekstop

Hello (sorry my bad english, im brazilian)

I was using Visual Studio 2003 and SQL Server CE 2.0 for C# mobile applications. The .sdf database were created in the emulator or in the mobile device itself using Query Analizer.

The application developed need some initial data to run, and this data is obtained executing one service that reads a postgree database, and insert the data in the SQL CE database of the mobile device. But, given the size of the database (maybe 10.000 rows), it tooks too much time (sometimes 6 hours).

Now we are migrating to Visual Studio 2005 and SQL Server 2005 Mobile Edition.

I want to know if its possible to create the .sdf database and load the data into this database on the desktop. Maybe through the execution of a .sql script, or through a service executed on the desktop.

After this, its just upload de .sdf file to the mobile device.

Thanks

Robson

Yes, you can create and populate your SQL Mobile database on the desktop as long as that desktop or server meets one of these criteria:

1. it contains a licensed copy of Visual Studio 2005

2. it contains a licesed copy of SQL Server 2005

3. it runs Windows XP Tablet PC edition

The code to do so is covered in the SQL Mobile Books Online.

There are other approaches as well, including third party tools like those at www.primeworks.pt, using SQL Server 2005 Integration Services, or creating and populating the database within SQL Server 2005 management studio.

-Darren

|||

Daren,

thanks for the help... I have found the way to create a sql server mobile 2005 database and insert data on the desktop using c# (running on desktop off course) at these forum topics:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=213973&SiteID=1

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=16369&SiteID=1

I′ve used my licensed copy of Visual Studio 2005 to do it. Now I′ll test the solution to make a benchmark... I hope that now i will be able to create the database for my application faster....

thanks again...

|||

Please let us know how it works out for you.

Darren

|||

I have created the following table ('cidade' means city in portuguese):

CREATE TABLE cidade ( idcidade numeric(18,0) NOT NULL, codigo integer NOT NULL, descricao nvarchar(80) NOT NULL, ddd nvarchar(3), naturalidade nvarchar(80), idunidadefederativa numeric(18,0) NOT NULL )

The program (written in c# with visual studio 2005 and sql server 2005 mobile edition) insert 5565 rows in this table. It reads a sql insert line from a text file and execute the sql, eg:

INSERT INTO cidade VALUES (1, 1, 'ALTA FLORESTA D OESTE', NULL, NULL, 21)

It tooks 6 seconds to do it (running on a HP notebook with celeron processor).So, 927.5 rows per second.

Before, when we insert data on a database located at a pocket pc, this operation took 20 - 30 minutes (using c# compact framework from visual studio 2003 and sql ce 2.0).

|||

thanks for sharing your benchmark results - that's very good news.

-Darren

sql

Create table?

Hi All
I want to create a table with a name as a varible. That variable contains the table name by doing some string operations.
say: create table @.var
@.var contains the table name that is generated.
how could i do this?
plz help me .You will need to use dynamic sql to build your CREATE TABLE command, and then EXEC your dynamic sql.|||Why on earth would you ever want to do that? You are opening yourself up for a complete world of hurt. Whatever you are hoping to accomplish with this, you are most certainly solving the problem completely wrong.
Instead of asking fora horrible hack, you need to ask for advise on how to come up with an effective solution.|||Thanks a lot......... it solved my query.

CREATE TABLE/VIEW from stored procedure or SELECT...

Can anyone tell me how can I create a table in (SQL Server 2000) direct from a stored procedure execution or from a SELECT result?

I need something like this: CREATE TABLE < t > FROM <sp_name p1, p2, ...>or like this:

CREATE TABLE < t > FROM SELECT id, name FROM < w > ...

Thank you!

Look at SELECT ... INTO command.|||

use northwind

select * into #tablex from employees

select * from #tablex

|||

Sorry joeydj but your example create a copy of another table! I need to create a table that contains only a few columns from another table, so that why I need to use a SELECT or a stored procedure that build and execute a SELECT.

Can I do that?

Thanks!

|||Sorry gavrilenko_s but I miss your post! You are right! That is the solution! Thanks!|||

hi,

first you have to create a table that has a similar

structure with the Sp

and then you can use

insert into temp

exec sp1

here's a sample snippet

USE NORTHWIND
select 'my name.........................12345' as productname, 10000.00000
as unitprice, 10000.0000 as quantiTY,
10000.0000 as discount, 10000.0000 as extendedprice
into tempx

truncate table tempx

insert into tempx
exec dbo.CustOrdersDetail '10248'

select * from tempx
drop table tempx

also suggest you make use of UDFs

cheers :)

Create table,fields come from csv file

I want to create a table automatically,and fields come from a csv file

any idea? TIA

One way is using two packages and a configuration file.

I'm assuming that you are constructing the name of the table on the fly. Note that the table MUST always have the same format.

First create a sample of what you want your table to look like.

Package 1:
Create an SSIS package that loads data from a CSV file into that table.
Make the tablename come from a variable.
Put the variable in a configuration table

Package 2:
Create a variable that will contain the table name.
Create a variable expressions that has the sql to create the table using the variable previously defined.
Create a variable expression that has the sql to update the variable in the configuration table from Package 1
Create a SQL Task to create the table
Create a SQL Task to update the configuration table variable
Create an Execute SSIS package task to execute Package 1

Similarly you can extend this to the source by scanning directories for files and loading different CSV files into different tables.

Hope this helps,

Larry