Hi everyone, I am fairly new with SQL Server and need a little bit of help in regards to boosting my SQL servers performance. I have been advised that defraging SQL Server will definitely help solve my issue however I have never performed this function before. Can someone please help guide me through the necessary steps to execute this task. Thanks in advance.I have been advised that defraging SQL Server will definitely help solve my issue however I have never performed this function before.
Only one setting cannot solve your problem, you have to consider lots of things like server memory settings, disk space, indexes, query optimizing etc.
You can use database maintenance wizard from Enterprise Manager-> Tools-> Database maintenance planner.
You can fragment your indexes for better performance, syntax is given below.
DBCC INDEXDEFRAG
( { database_name | database_id | 0 }
, { table_name | table_id | 'view_name' | view_id }
, { index_name | index_id }
) [ WITH NO_INFOMSGS ]|||Only one setting cannot solve your problem, you have to consider lots of things like server memory settings, disk space, indexes, query optimizing etc.
You can use database maintenance wizard from Enterprise Manager-> Tools-> Database maintenance planner.
You can fragment your indexes for better performance, syntax is given below.
DBCC INDEXDEFRAG
( { database_name | database_id | 0 }
, { table_name | table_id | 'view_name' | view_id }
, { index_name | index_id }
) [ WITH NO_INFOMSGS ]
I am curious; is there any value in doing a backup/restore?
I have a daily scheduled run of Executive Software's Diskkeeper on all the servers. That keeps the files defragged on the file-system level, but of course doesn't reorder anything within the database.
As I understand it, the concept of Defragmenetation offers an optimization of physical aspects of the disk drive (rotations, head movements) and the software activities of piecing together the fragments. It follows that having all the bits of an index in order would have a similar effect (as you described above).
I guess in a database there's also a matter of eliminating all the holes left by prior deletes and of spreading indexes out more intelligently.
So then; I'm displaying a complete ignorance of "database layer fragmentation". Am I missing a lot of fundamentals in my thinking?
Question: Would it be a benifit to backup-then-restore a database?|||Backing up and restoring a database has no effect on fragmentation. Database fragmentation that is, the DBA's nerves will become highly fragmented if this sort of thing is implemented. In Oracle, you can export and import tables to remove fragmentation, which may be what you are thinking of. In SQL Server, a backup collects all pages that have data on them, and stashes them away. On a restore, the data pages are simply rewritten in place. without any moving of data around the pages.
Database fragmentation happens mainly with deletes, sometimes with updates, and somewhat less frequently with inserts (depending on your indexes).
Suppose you have a data page that originally has 20 entries (rows) in it. When you read in that page, you get 20 rows in memory. Suppose further that 19 of these rows are deleted. Now when you read in the same 8KB page, you only get 1 row of data. The space taken up by the rows that were there is not reclaimed automatically, and depending on insert/update activity and clustered index layout may not ever be reclaimed unless you rebuild the indexes. Rebuilding the indexes has the effect of re-arranging, or regenerating the entries packed closer together making read operations more efficient.
Here is a link to a decent paper about it. Note, users tend to not notice the difference, until tables get above 10,000 pages or so.
http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx|||I am curious; is there any value in doing a backup/restore?
yes, there is some value|||Check this, a single web page consist of various subjects for SQL Server Maintenance.
http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/default.mspx
Showing posts with label fairly. Show all posts
Showing posts with label fairly. Show all posts
Sunday, February 19, 2012
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
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
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
Subscribe to:
Posts (Atom)