How to detect if a schema if exists or not so that I will not create same schema agai

How to detect if a schema if exists or not so that I will not create same
schema again?
AND create schema statement should be the first statement of a bach?
--Frank, SQL2005devHello, Frank

> How to detect if a schema if exists or not so that I will not create same
> schema again?
Look into sys.schemas

> AND create schema statement should be the first statement of a bach?
Yes.
For example, you can use something like this:
IF NOT EXISTS (SELECT * FROM sys.schemas WHERE name='YourSchema')
EXEC('CREATE SCHEMA YourSchema')
Razvan|||... or use
SELECT SCHEMA_NAME FROM INFORMATION_SCHEMA.SCHEMATA
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Razvan Socol" <rsocol@.gmail.com> wrote in message
news:1137403952.648035.289210@.g49g2000cwa.googlegroups.com...
> Hello, Frank
>
> Look into sys.schemas
>
> Yes.
> For example, you can use something like this:
> IF NOT EXISTS (SELECT * FROM sys.schemas WHERE name='YourSchema')
> EXEC('CREATE SCHEMA YourSchema')
> Razvan
>|||I got it, thanks.
"Razvan Socol" <rsocol@.gmail.com>
'?:1137403952.648035.289210@.g49g2000cwa.googlegroups.com...
> Hello, Frank
>
> Look into sys.schemas
>
> Yes.
> For example, you can use something like this:
> IF NOT EXISTS (SELECT * FROM sys.schemas WHERE name='YourSchema')
> EXEC('CREATE SCHEMA YourSchema')
> Razvan
>

how to design string type datawarehouse?

if the fact table have some string type measure,how to design start schema ?
Could you be more specific as to what you're trying to do? Why do you feel
that a string datatype measure would pose a problem?
"x" <xiaopeng@.creditbeijing.com.cn> wrote in message
news:%23vy5$UMaEHA.2944@.TK2MSFTNGP11.phx.gbl...
> if the fact table have some string type measure,how to design start schema
?
>
|||For example,the fact table record some education information: somebody in
somewhere at sometime get some education level certification.
how to design this star schema?
so no measure or only string type column(can't be aggregation) ,How to
tuning the database for ad hoc query?
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> д?
news:e9LU7qQaEHA.1248@.TK2MSFTNGP11.phx.gbl...
> Could you be more specific as to what you're trying to do? Why do you
feel[vbcol=seagreen]
> that a string datatype measure would pose a problem?
>
> "x" <xiaopeng@.creditbeijing.com.cn> wrote in message
> news:%23vy5$UMaEHA.2944@.TK2MSFTNGP11.phx.gbl...
schema
> ?
>
|||Why not make "Education Certificate" a dimension all of its own and then have a simple measure "Count".
Regards
Jamie
"x" wrote:

> For example,the fact table record some education information: somebody in
> somewhere at sometime get some education level certification.
> how to design this star schema?
> so no measure or only string type column(can't be aggregation) ,How to
> tuning the database for ad hoc query?
>
>
> "Adam Machanic" <amachanic@.hotmail._removetoemail_.com> D′è????￠
> news:e9LU7qQaEHA.1248@.TK2MSFTNGP11.phx.gbl...
> feel
> schema
>
>

how to design string type datawarehouse?

if the fact table have some string type measure,how to design start schema ?Could you be more specific as to what you're trying to do? Why do you feel
that a string datatype measure would pose a problem?
"x" <xiaopeng@.creditbeijing.com.cn> wrote in message
news:%23vy5$UMaEHA.2944@.TK2MSFTNGP11.phx.gbl...
> if the fact table have some string type measure,how to design start schema
?
>|||For example,the fact table record some education information: somebody in
somewhere at sometime get some education level certification.
how to design this star schema?
so no measure or only string type column(can't be aggregation) ,How to
tuning the database for ad hoc query?
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> д?
news:e9LU7qQaEHA.1248@.TK2MSFTNGP11.phx.gbl...
> Could you be more specific as to what you're trying to do? Why do you
feel
> that a string datatype measure would pose a problem?
>
> "x" <xiaopeng@.creditbeijing.com.cn> wrote in message
> news:%23vy5$UMaEHA.2944@.TK2MSFTNGP11.phx.gbl...
schema[vbcol=seagreen]
> ?
>|||Why not make "Education Certificate" a dimension all of its own and then hav
e a simple measure "Count".
Regards
Jamie
"x" wrote:

> For example,the fact table record some education information: somebody in
> somewhere at sometime get some education level certification.
> how to design this star schema?
> so no measure or only string type column(can't be aggregation) ,How to
> tuning the database for ad hoc query?
>
>
> "Adam Machanic" <amachanic@.hotmail._removetoemail_.com> D′è????￠
> news:e9LU7qQaEHA.1248@.TK2MSFTNGP11.phx.gbl...
> feel
> schema
>
>

How to deny information schema views...

Hello, all. I created a login and granted the user access to my sql 2005
database. well when the user creates his odbc dsn to access the database, I
discovered he can also see the INFORMATION_SCHEMA views. What gives? How
can I deny him access to these objects. He should have access to the db I
granted him.
Help!!!!!!!!
RozThe Information Schema views in SQL Server 2005 should only return for the
user information about the objects the user actually has access to. While
this was a prominent information disclosure issue in SQL Server 2000, it's
not as wide open in SQL Server 2005. They are provided for SQL-92 compliance
so that users can query the metadata/schema of the database without having
to query the system tables. Is there a reason you want to block access to
them?
K. Brian Kelley, brian underscore kelley at sqlpass dot org
http://www.truthsolutions.com/

> Hello, all. I created a login and granted the user access to my sql
> 2005 database. well when the user creates his odbc dsn to access the
> database, I discovered he can also see the INFORMATION_SCHEMA views.
> What gives? How can I deny him access to these objects. He should
> have access to the db I granted him.
> Help!!!!!!!!
> Roz|||Hello Roz,
You can't hide the fact the views exist as far as I can tell, but if you
look at what he see, it won't be much if anything. Basically he has to be
able to the see the metadata's metadata, but he shouldn't be able to see
the metadata itself unless you start granting him rights to do so (e.g.,
VIEW DEFINITION).
Thanks!
Kent Tegels
DevelopMentor
http://staff.develop.com/ktegels/|||Thanks for reply. I want to block access because as my users create their
ODBC DSNs, they can open these tables and **change** data. I've tried it an
d
it works. Very scary.
Roz
"K. Brian Kelley" wrote:

> The Information Schema views in SQL Server 2005 should only return for the
> user information about the objects the user actually has access to. While
> this was a prominent information disclosure issue in SQL Server 2000, it's
> not as wide open in SQL Server 2005. They are provided for SQL-92 complian
ce
> so that users can query the metadata/schema of the database without having
> to query the system tables. Is there a reason you want to block access to
> them?
>
> K. Brian Kelley, brian underscore kelley at sqlpass dot org
> http://www.truthsolutions.com/
>
>
>|||Kent,
Simply having the "public" role, gets him access to these tables. He (I)
was even able to open these tables say in Access thru ODBC, and potentially
change the data. Scary.
Roz
"Kent Tegels" wrote:

> Hello Roz,
> You can't hide the fact the views exist as far as I can tell, but if you
> look at what he see, it won't be much if anything. Basically he has to be
> able to the see the metadata's metadata, but he shouldn't be able to see
> the metadata itself unless you start granting him rights to do so (e.g.,
> VIEW DEFINITION).
> Thanks!
> Kent Tegels
> DevelopMentor
> http://staff.develop.com/ktegels/
>
>|||I am wondering if you are seeing something else.
Could you please give us the steps you used to open
information schema views and change the underlying data on
SQL Server 2005? Which views, data in what columns?
As far as I know, what you are saying is not possible.
If it is actually other tables you are referring too, I
think you have a permissions issue with how you have
security set up. I think that's likely the issue anyway.
-Sue
On Tue, 20 Mar 2007 16:51:05 -0700, Roz
<Roz@.discussions.microsoft.com> wrote:
[vbcol=seagreen]
>Thanks for reply. I want to block access because as my users create their
>ODBC DSNs, they can open these tables and **change** data. I've tried it a
nd
>it works. Very scary.
>Roz
>"K. Brian Kelley" wrote:
>

How to delete all rows in a DataBase

I have a database, I want to delete all rows in all tables. Only remain the
schema of all tables.
I only know to use Sql command like 'delete from aTable' to every tables.
Have there any convenient way to do that?ad
Perhaps you need to deal with DRI before your the script
DECLARE @.TruncateStatement nvarchar(4000)
DECLARE TruncateStatements CURSOR LOCAL FAST_FORWARD
FOR
SELECT
N'TRUNCATE TABLE ' +
QUOTENAME(TABLE_SCHEMA) +
N'.' +
QUOTENAME(TABLE_NAME)
FROM
INFORMATION_SCHEMA.TABLES
WHERE
TABLE_TYPE = 'BASE TABLE' AND
OBJECTPROPERTY(OBJECT_ID(QUOTENAME(TABLE_SCHEMA) +
N'.' +
QUOTENAME(TABLE_NAME)), 'IsMSShipped') = 0
OPEN TruncateStatements
WHILE 1 = 1
BEGIN
FETCH NEXT FROM TruncateStatements INTO @.TruncateStatement
IF @.@.FETCH_STATUS <> 0 BREAK
RAISERROR (@.TruncateStatement, 0, 1) WITH NOWAIT
EXEC(@.TruncateStatement)
END
CLOSE TruncateStatements
DEALLOCATE TruncateStatements
"ad" <ad@.wfes.tcc.edu.tw> wrote in message
news:%23jGv2h4gFHA.3436@.tk2msftngp13.phx.gbl...
> I have a database, I want to delete all rows in all tables. Only remain
the
> schema of all tables.
> I only know to use Sql command like 'delete from aTable' to every
tables.
> Have there any convenient way to do that?
>|||Easiest way it probably to script the database (actually, you should have the schema as source code
already). Then drop the database and run the script file(s).
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"ad" <ad@.wfes.tcc.edu.tw> wrote in message news:%23jGv2h4gFHA.3436@.tk2msftngp13.phx.gbl...
>I have a database, I want to delete all rows in all tables. Only remain the
> schema of all tables.
> I only know to use Sql command like 'delete from aTable' to every tables.
> Have there any convenient way to do that?
>

How to delete all rowa in all tables of a schema in Oracle?

Hi all,
I want to delete all records of all tables of a schema and think there should be some statement for this but I dont know how?
may you help?As this question is Oracle specific, I'd suggest that you post it in the Oracle (http://www.dbforums.com/f4) forum. One of the Oracle folks can probably answer your question definitively without even needing to look it up!

-PatP|||There is no simple statement available to delete only the tables.
U can instead use
DROP USER <username> CASCADE.
But caution....this will delete everything belonging to the user tables, views, sequences..etc.
If u want to delete only the table of a schema
then u can write a PL/SQL which will query for all the table from user_objects and then execute statements to delete the data from the tables|||PL/SQL procedure would do the work indeed.

Perhaps another suggestion - write a query and spool its output to an .sql file and then run it. Such as:

> set heading off;
> set feedback off;
>
> spool truncall.sql
>
> select 'truncate table ' || tname ||';' from tab where tabtype = 'TABLE';
>
> spool off;
>
> @.truncall

Why 'truncate' and not 'delete'? Delete saves all the deleted records in rollback segment(s) which slows things down.

However, you might need to run this script several times due to referential integrity constraints which might prevent some tables to be truncated (you can't delete parent while child exists).|||Thanx all for help,
but I want to delete all the records from all my tables not truncating all tables.Any idea?|||What difference do you see between deleting all of the rows and truncating the table?

-PatP

How to define the XML schema for a SQLXMLBulkLoad

Hello everyone. I'm fairly new to SQLXMLBulkLoad and XML-Schemas and
have a question regarding how to define an xml-schema to bulk load an
XML document that I get from our system.
Here's what the XML document looks like:
<CatalogDelta>
<RevisionID>1.0</RevisionID>
<CatalogVersion>195</CatalogVersion>
<deletes>
<album id="123" />
<song id="2345" />
<song id="4563" />
</deletes>
</CatalogDelta>
What I want to do is get the xml bulk loaded into three SQL database
tables:
Table CatalogDelta
( RevisionID varchar(50),
CatalogVersion varchar(50)
)
1.0 | 195
Table album_deletes
( id varchar(50) )
123
table song_deletes
( id varchar(50) )
2345
4563
Here's the XML-Schema I've been trying to use but can't seem to get
past the <deletes> element.
<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"
xmlns:sql="urn:schemas-microsoft-com:mapping-schema">
<xsd:element name="RevisionID" sql:field="revision_id"
sql:datatype="nvarchar(50)" />
<xsd:element name="CatalogVersion" sql:field="catalog_version"
sql:datatype="nvarchar(50)" />
<!-- CatalogDelta -->
<xsd:group name="DeltaCatalogGroup">
<xsd:sequence>
<xsd:element ref="RevisionID"/>
<xsd:element ref="CatalogVersion" />
</xsd:sequence>
</xsd:group>
<xsd:element name="CatalogDelta" sql:relation="CatalogDelta">
<xsd:complexType>
<xsd:group ref="DeltaCatalogGroup"/>
</xsd:complexType>
</xsd:element>
<!-- Deletes -->
<xsd:element name="deletes" sql:is-constant="1">
<xsd:complexType>
<xsd:all>
<!-- Delete Albums -->
<xsd:element name="album" sql:relation="album_deletes">
<xsd:complexType>
<xsd:attribute name="id" sql:field="album_id"
type="xsd:string" sql:datatype="nvarchar(05)" />
</xsd:complexType>
</xsd:element>
<!-- Delete Songs -->
<xsd:element name="song" sql:relation="song_deletes">
<xsd:complexType>
<xsd:attribute name="id" type="xsd:string"
sql:field="song_id" sql:datatype="nvarchar(50)" />
</xsd:complexType>
</xsd:element>
</xsd:all>
</xsd:complexType>
</xsd:element>
</schema>
When I bulk load using this schema, the contents get put into the
CatalogDelta table but nothing gets put into the album_deletes or
song_deletes tables.
Can someone point out what I am doing wrong, please?
Thank you for your help.
Sincerely
Steve Cummings
Hello,
Whenever you have children you have to define relationship in the schema:
<xsd:annotation>
<xsd:appinfo>
<sql:relationship name="catalog_album"
parent="CatalogDelta"
child="album_deletes"
parent-key="?"
child-key="?"/>
</xsd:appinfo>
</xsd:annotation>
Then add the deletes element to the Catalog:
<xsd:element name="CatalogDelta" sql:relation="CatalogDelta">
<xsd:complexType>
<xsd:sequence>
<xsd:group ref="DeltaCatalogGroup"/>
<xsd:element ref="deletes" />
</xsd:sequence>
</xsd:complexType>
</xsd:element>
<xsd:element name="deletes" sql:is-constant="1">
<xsd:complexType>
<xsd:all>
<xsd:element name="album" sql:relation="album_deletes"
sql:relationship="catalog_album">
<xsd:complexType>
<xsd:attribute name="id" sql:field="album_id"
type="xsd:string" sql:datatype="nvarchar(05)" />
</xsd:complexType>
</xsd:element>
You need to figure out how the tables related to each other and describe
this in the schema.
Take a look at
this:http://msdn2.microsoft.com/en-us/library/aa258644(SQL.80).aspx
I hope this helps.
Regards,
Monica Frintu
"sgcummings@.sbcglobal.net" wrote:

> Hello everyone. I'm fairly new to SQLXMLBulkLoad and XML-Schemas and
> have a question regarding how to define an xml-schema to bulk load an
> XML document that I get from our system.
> Here's what the XML document looks like:
> <CatalogDelta>
> <RevisionID>1.0</RevisionID>
> <CatalogVersion>195</CatalogVersion>
> <deletes>
> <album id="123" />
> <song id="2345" />
> <song id="4563" />
> </deletes>
> </CatalogDelta>
> What I want to do is get the xml bulk loaded into three SQL database
> tables:
> Table CatalogDelta
> ( RevisionID varchar(50),
> CatalogVersion varchar(50)
> )
> 1.0 | 195
> Table album_deletes
> ( id varchar(50) )
> 123
> table song_deletes
> ( id varchar(50) )
> 2345
> 4563
> Here's the XML-Schema I've been trying to use but can't seem to get
> past the <deletes> element.
> <xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"
> xmlns:sql="urn:schemas-microsoft-com:mapping-schema">
> <xsd:element name="RevisionID" sql:field="revision_id"
> sql:datatype="nvarchar(50)" />
> <xsd:element name="CatalogVersion" sql:field="catalog_version"
> sql:datatype="nvarchar(50)" />
> <!-- CatalogDelta -->
> <xsd:group name="DeltaCatalogGroup">
> <xsd:sequence>
> <xsd:element ref="RevisionID"/>
> <xsd:element ref="CatalogVersion" />
> </xsd:sequence>
> </xsd:group>
> <xsd:element name="CatalogDelta" sql:relation="CatalogDelta">
> <xsd:complexType>
> <xsd:group ref="DeltaCatalogGroup"/>
> </xsd:complexType>
> </xsd:element>
> <!-- Deletes -->
> <xsd:element name="deletes" sql:is-constant="1">
> <xsd:complexType>
> <xsd:all>
> <!-- Delete Albums -->
> <xsd:element name="album" sql:relation="album_deletes">
> <xsd:complexType>
> <xsd:attribute name="id" sql:field="album_id"
> type="xsd:string" sql:datatype="nvarchar(05)" />
> </xsd:complexType>
> </xsd:element>
> <!-- Delete Songs -->
> <xsd:element name="song" sql:relation="song_deletes">
> <xsd:complexType>
> <xsd:attribute name="id" type="xsd:string"
> sql:field="song_id" sql:datatype="nvarchar(50)" />
> </xsd:complexType>
> </xsd:element>
> </xsd:all>
> </xsd:complexType>
> </xsd:element>
> </schema>
> When I bulk load using this schema, the contents get put into the
> CatalogDelta table but nothing gets put into the album_deletes or
> song_deletes tables.
> Can someone point out what I am doing wrong, please?
> Thank you for your help.
> Sincerely
> Steve Cummings
>
|||On Apr 10, 2:54 pm, Monica Frintu [MSFT]
<MonicaFrintuM...@.discussions.microsoft.com> wrote:
> Hello,
> Whenever you have children you have todefinerelationship in theschema:
> <xsd:annotation>
> <xsd:appinfo>
> <sql:relationship name="catalog_album"
> parent="CatalogDelta"
> child="album_deletes"
> parent-key="?"
> child-key="?"/>
> </xsd:appinfo>
> </xsd:annotation>
> Then add the deletes element to the Catalog:
> <xsd:element name="CatalogDelta" sql:relation="CatalogDelta">
> <xsd:complexType>
> <xsd:sequence>
> <xsd:group ref="DeltaCatalogGroup"/>
> <xsd:element ref="deletes" />
> </xsd:sequence>
> </xsd:complexType>
> </xsd:element>
> <xsd:element name="deletes" sql:is-constant="1">
> <xsd:complexType>
> <xsd:all>
> <xsd:element name="album" sql:relation="album_deletes"
> sql:relationship="catalog_album">
> <xsd:complexType>
> <xsd:attribute name="id" sql:field="album_id"
> type="xsd:string" sql:datatype="nvarchar(05)" />
> </xsd:complexType>
> </xsd:element>
> You need to figure out how the tables related to each other and describe
> this in theschema.
> Take a look at
> this:http://msdn2.microsoft.com/en-us/library/aa258644(SQL.80).aspx
> I hope this helps.
> Regards,
> Monica Frintu
>
> "sgcummi...@.sbcglobal.net" wrote:
>
>
>
>
>
>
>
>
>
> - Show quoted text -
Hello Monica!
Thank you for the information. It was very helpful. I was able to
extrapolate from what you provided and have everything working now.
It only took about 20 minutes to complete all the adjustments to get
the schema bulk load to work.
With much appreciation for your help.
Steve Cummings

How to define the XML schema for a SQLXMLBulkLoad

Hello everyone. I'm fairly new to SQLXMLBulkLoad and XML-Schemas and
have a question regarding how to define an xml-schema to bulk load an
XML document that I get from our system.
Here's what the XML document looks like:
<CatalogDelta>
<RevisionID>1.0</RevisionID>
<CatalogVersion>195</CatalogVersion>
<deletes>
<album id="123" />
<song id="2345" />
<song id="4563" />
</deletes>
</CatalogDelta>
What I want to do is get the xml bulk loaded into three SQL database
tables:
Table CatalogDelta
( RevisionID varchar(50),
CatalogVersion varchar(50)
)
1.0 | 195
Table album_deletes
( id varchar(50) )
123
table song_deletes
( id varchar(50) )
2345
4563
Here's the XML-Schema I've been trying to use but can't seem to get
past the <deletes> element.
<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"
xmlns:sql="urn:schemas-microsoft-com:mapping-schema">
<xsd:element name="RevisionID" sql:field="revision_id"
sql:datatype="nvarchar(50)" />
<xsd:element name="CatalogVersion" sql:field="catalog_version"
sql:datatype="nvarchar(50)" />
<!-- CatalogDelta -->
<xsd:group name="DeltaCatalogGroup">
<xsd:sequence>
<xsd:element ref="RevisionID"/>
<xsd:element ref="CatalogVersion" />
</xsd:sequence>
</xsd:group>
<xsd:element name="CatalogDelta" sql:relation="CatalogDelta">
<xsd:complexType>
<xsd:group ref="DeltaCatalogGroup"/>
</xsd:complexType>
</xsd:element>
<!-- Deletes -->
<xsd:element name="deletes" sql:is-constant="1">
<xsd:complexType>
<xsd:all>
<!-- Delete Albums -->
<xsd:element name="album" sql:relation="album_deletes">
<xsd:complexType>
<xsd:attribute name="id" sql:field="album_id"
type="xsd:string" sql:datatype="nvarchar(05)" />
</xsd:complexType>
</xsd:element>
<!-- Delete Songs -->
<xsd:element name="song" sql:relation="song_deletes">
<xsd:complexType>
<xsd:attribute name="id" type="xsd:string"
sql:field="song_id" sql:datatype="nvarchar(50)" />
</xsd:complexType>
</xsd:element>
</xsd:all>
</xsd:complexType>
</xsd:element>
</schema>
When I bulk load using this schema, the contents get put into the
CatalogDelta table but nothing gets put into the album_deletes or
song_deletes tables.
Can someone point out what I am doing wrong, please?
Thank you for your help.
Sincerely
Steve CummingsHello,
Whenever you have children you have to define relationship in the schema:
<xsd:annotation>
<xsd:appinfo>
<sql:relationship name="catalog_album"
parent="CatalogDelta"
child="album_deletes"
parent-key="?"
child-key="?"/>
</xsd:appinfo>
</xsd:annotation>
Then add the deletes element to the Catalog:
<xsd:element name="CatalogDelta" sql:relation="CatalogDelta">
<xsd:complexType>
<xsd:sequence>
<xsd:group ref="DeltaCatalogGroup"/>
<xsd:element ref="deletes" />
</xsd:sequence>
</xsd:complexType>
</xsd:element>
<xsd:element name="deletes" sql:is-constant="1">
<xsd:complexType>
<xsd:all>
<xsd:element name="album" sql:relation="album_deletes"
sql:relationship="catalog_album">
<xsd:complexType>
<xsd:attribute name="id" sql:field="album_id"
type="xsd:string" sql:datatype="nvarchar(05)" />
</xsd:complexType>
</xsd:element>
You need to figure out how the tables related to each other and describe
this in the schema.
Take a look at
this:http://msdn2.microsoft.com/en-us/library/aa258644(SQL.80).aspx
I hope this helps.
Regards,
Monica Frintu
"sgcummings@.sbcglobal.net" wrote:

> Hello everyone. I'm fairly new to SQLXMLBulkLoad and XML-Schemas and
> have a question regarding how to define an xml-schema to bulk load an
> XML document that I get from our system.
> Here's what the XML document looks like:
> <CatalogDelta>
> <RevisionID>1.0</RevisionID>
> <CatalogVersion>195</CatalogVersion>
> <deletes>
> <album id="123" />
> <song id="2345" />
> <song id="4563" />
> </deletes>
> </CatalogDelta>
> What I want to do is get the xml bulk loaded into three SQL database
> tables:
> Table CatalogDelta
> ( RevisionID varchar(50),
> CatalogVersion varchar(50)
> )
> 1.0 | 195
> Table album_deletes
> ( id varchar(50) )
> 123
> table song_deletes
> ( id varchar(50) )
> 2345
> 4563
> Here's the XML-Schema I've been trying to use but can't seem to get
> past the <deletes> element.
> <xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"
> xmlns:sql="urn:schemas-microsoft-com:mapping-schema">
> <xsd:element name="RevisionID" sql:field="revision_id"
> sql:datatype="nvarchar(50)" />
> <xsd:element name="CatalogVersion" sql:field="catalog_version"
> sql:datatype="nvarchar(50)" />
> <!-- CatalogDelta -->
> <xsd:group name="DeltaCatalogGroup">
> <xsd:sequence>
> <xsd:element ref="RevisionID"/>
> <xsd:element ref="CatalogVersion" />
> </xsd:sequence>
> </xsd:group>
> <xsd:element name="CatalogDelta" sql:relation="CatalogDelta">
> <xsd:complexType>
> <xsd:group ref="DeltaCatalogGroup"/>
> </xsd:complexType>
> </xsd:element>
> <!-- Deletes -->
> <xsd:element name="deletes" sql:is-constant="1">
> <xsd:complexType>
> <xsd:all>
> <!-- Delete Albums -->
> <xsd:element name="album" sql:relation="album_deletes">
> <xsd:complexType>
> <xsd:attribute name="id" sql:field="album_id"
> type="xsd:string" sql:datatype="nvarchar(05)" />
> </xsd:complexType>
> </xsd:element>
> <!-- Delete Songs -->
> <xsd:element name="song" sql:relation="song_deletes">
> <xsd:complexType>
> <xsd:attribute name="id" type="xsd:string"
> sql:field="song_id" sql:datatype="nvarchar(50)" />
> </xsd:complexType>
> </xsd:element>
> </xsd:all>
> </xsd:complexType>
> </xsd:element>
> </schema>
> When I bulk load using this schema, the contents get put into the
> CatalogDelta table but nothing gets put into the album_deletes or
> song_deletes tables.
> Can someone point out what I am doing wrong, please?
> Thank you for your help.
> Sincerely
> Steve Cummings
>|||On Apr 10, 2:54 pm, Monica Frintu [MSFT]
<MonicaFrintuM...@.discussions.microsoft.com> wrote:
> Hello,
> Whenever you have children you have todefinerelationship in theschema:
> <xsd:annotation>
> <xsd:appinfo>
> <sql:relationship name="catalog_album"
> parent="CatalogDelta"
> child="album_deletes"
> parent-key="?"
> child-key="?"/>
> </xsd:appinfo>
> </xsd:annotation>
> Then add the deletes element to the Catalog:
> <xsd:element name="CatalogDelta" sql:relation="CatalogDelta">
> <xsd:complexType>
> <xsd:sequence>
> <xsd:group ref="DeltaCatalogGroup"/>
> <xsd:element ref="deletes" />
> </xsd:sequence>
> </xsd:complexType>
> </xsd:element>
> <xsd:element name="deletes" sql:is-constant="1">
> <xsd:complexType>
> <xsd:all>
> <xsd:element name="album" sql:relation="album_deletes"
> sql:relationship="catalog_album">
> <xsd:complexType>
> <xsd:attribute name="id" sql:field="album_id"
> type="xsd:string" sql:datatype="nvarchar(05)" />
> </xsd:complexType>
> </xsd:element>
> You need to figure out how the tables related to each other and describe
> this in theschema.
> Take a look at
> this:http://msdn2.microsoft.com/en-us/library/aa258644(SQL.80).aspx
> I hope this helps.
> Regards,
> Monica Frintu
>
> "sgcummi...@.sbcglobal.net" wrote:
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
> - Show quoted text -
Hello Monica!
Thank you for the information. It was very helpful. I was able to
extrapolate from what you provided and have everything working now.
It only took about 20 minutes to complete all the adjustments to get
the schema bulk load to work.
With much appreciation for your help.
Steve Cummings