Jaa

ALTER XML SCHEMA COLLECTION (Transact-SQL)

  • Artikkeli

Adds new schema components to an existing XML schema collection.

Topic link iconTransact-SQL Syntax Conventions

Syntax

ALTER XML SCHEMA COLLECTION [ relational_schema. ]sql_identifier ADD 'Schema Component'

Arguments

  • relational_schema
    Identifies the relational schema name. If not specified, the default relational schema is assumed.
  • sql_identifier
    Is the SQL identifier for the XML schema collection.
  • 'Schema Component'
    Is the schema component to insert.

Remarks

Use the ALTER XML SCHEMA COLLECTION to add new XML schemas whose namespaces are not already in the XML schema collection, or add new components to existing namespaces in the collection.

The following example adds a new <element> to the existing namespace http://MySchema/test_xml_schema in the collection MyColl.

-- First create an XML schema collection.
CREATE XML SCHEMA COLLECTION MyColl AS '
   <schema 
    xmlns="http://www.w3.org/2001/XMLSchema" 
    targetNamespace="http://MySchema/test_xml_schema">
      <element name="root" type="string"/> 
  </schema>'
-- Modify the collection. 
ALTER XML SCHEMA COLLECTION MyColl ADD '
  <schema xmlns="http://www.w3.org/2001/XMLSchema" 
         targetNamespace="http://MySchema/test_xml_schema"> 
     <element name="anotherElement" type="byte"/> 
 </schema>'

ALTER XML SCHEMA adds element <anotherElement> to the previously defined namespace http://MySchema/test_xml_schema.

Note that if some of the components you want to add in the collection reference components that are already in the same collection, you must use <import namespace="referenced_component_namespace" />. However, it is not valid to use the current schema namespace in <xsd:import>, and therefore components from the same target namespace as the current schema namespace are automatically imported.

To remove collections, use DROP XML SCHEMA COLLECTION (Transact-SQL).

Permissions

To alter an XML SCHEMA COLLECTION requires ALTER permission on the collection.

For more information, see Permissions on an XML Schema Collection.

Examples

A. Creating XML schema collection in the database

The following example creates the XML schema collection ManuInstructionsSchemaCollection. The collection has only one schema namespace.

-- Create a sample database in which to load the XML schema collection.
CREATE DATABASE SampleDB
GO
USE SampleDB
GO
CREATE XML SCHEMA COLLECTION ManuInstructionsSchemaCollection AS
N'<?xml version="1.0" encoding="UTF-16"?>
<xsd:schema targetNamespace="https://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelManuInstructions" 
   xmlns          ="https://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelManuInstructions" 
   elementFormDefault="qualified" 
   attributeFormDefault="unqualified"
   xmlns:xsd="http://www.w3.org/2001/XMLSchema" >

    <xsd:complexType name="StepType" mixed="true" >
        <xsd:choice  minOccurs="0" maxOccurs="unbounded" > 
            <xsd:element name="tool" type="xsd:string" />
            <xsd:element name="material" type="xsd:string" />
            <xsd:element name="blueprint" type="xsd:string" />
            <xsd:element name="specs" type="xsd:string" />
            <xsd:element name="diag" type="xsd:string" />
        </xsd:choice> 
    </xsd:complexType>

    <xsd:element  name="root">
        <xsd:complexType mixed="true">
            <xsd:sequence>
                <xsd:element name="Location" minOccurs="1" maxOccurs="unbounded">
                    <xsd:complexType mixed="true">
                        <xsd:sequence>
                            <xsd:element name="step" type="StepType" minOccurs="1" maxOccurs="unbounded" />
                        </xsd:sequence>
                        <xsd:attribute name="LocationID" type="xsd:integer" use="required"/>
                        <xsd:attribute name="SetupHours" type="xsd:decimal" use="optional"/>
                        <xsd:attribute name="MachineHours" type="xsd:decimal" use="optional"/>
                        <xsd:attribute name="LaborHours" type="xsd:decimal" use="optional"/>
                        <xsd:attribute name="LotSize" type="xsd:decimal" use="optional"/>
                    </xsd:complexType>
                </xsd:element>
            </xsd:sequence>
        </xsd:complexType>
    </xsd:element>
</xsd:schema>' ;
GO
-- Verify - list of collections in the database.
SELECT *
FROM sys.xml_schema_collections
-- Verify - list of namespaces in the database.
SELECT name
FROM sys.xml_schema_namespaces

-- Use it. Create a typed xml variable. Note the collection name 
-- that is specified.
DECLARE @x xml (ManuInstructionsSchemaCollection)
GO
--Or create a typed xml column.
CREATE TABLE T (
        i int primary key, 
        x xml (ManuInstructionsSchemaCollection))
GO
-- Clean up.
DROP TABLE T
GO
DROP XML SCHEMA COLLECTION ManuInstructionsSchemaCollection
Go
USE master
GO
DROP DATABASE SampleDB

Alternatively, you can assign the schema collection to a variable and specify the variable in the CREATE XML SCHEMA COLLECTION statement as follows:

DECLARE @MySchemaCollection nvarchar(max)
Set @MySchemaCollection  = N' copy the schema collection here'
CREATE XML SCHEMA COLLECTION AS @MySchemaCollection

The variable in the example is of nvarchar(max) type. The variable can also be of xml data type, in which case, it is implicitly converted to a string.

For more information, see Viewing a Stored XML Schema Collection.

You can store schema collections in an xml type column. In this case, to create XML schema collection, perform the following steps:

  1. Retrieve the schema collection from the column by using a SELECT statement and assign it to a variable of xml type, or a varchar type.
  2. Specify the variable name in the CREATE XML SCHEMA COLLECTION statement.

The CREATE XML SCHEMA COLLECTION stores only the schema components that SQL Server understands; everything in the XML schema is not stored in the database. Therefore, if you want the XML schema collection back exactly the way it was supplied, we recommend that you save your XML schemas in a database column or some other folder on your computer.

B. Specifying multiple schema namespaces in a schema collection

You can specify multiple XML schemas when you create an XML schema collection. For example:

CREATE XML SCHEMA COLLECTION N'
<xsd:schema>....</xsd:schema>
<xsd:schema>...</xsd:schema>'

The following example creates the XML schema collection ProductDescriptionSchemaCollection that includes two XML schema namespaces.

CREATE XML SCHEMA COLLECTION ProductDescriptionSchemaCollection AS 
'<xsd:schema targetNamespace="https://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelWarrAndMain"
    xmlns="https://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelWarrAndMain" 
    elementFormDefault="qualified" 
    xmlns:xsd="http://www.w3.org/2001/XMLSchema" >
    <xsd:element name="Warranty"  >
        <xsd:complexType>
            <xsd:sequence>
                <xsd:element name="WarrantyPeriod" type="xsd:string"  />
                <xsd:element name="Description" type="xsd:string"  />
            </xsd:sequence>
        </xsd:complexType>
    </xsd:element>
</xsd:schema>
 <xs:schema targetNamespace="https://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelDescription" 
    xmlns="https://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelDescription" 
    elementFormDefault="qualified" 
    xmlns:mstns="http://tempuri.org/XMLSchema.xsd" 
    xmlns:xs="http://www.w3.org/2001/XMLSchema"
    xmlns:wm="https://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelWarrAndMain" >
    <xs:import 
namespace="https://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelWarrAndMain" />
    <xs:element name="ProductDescription" type="ProductDescription" />
        <xs:complexType name="ProductDescription">
            <xs:sequence>
                <xs:element name="Summary" type="Summary" minOccurs="0" />
            </xs:sequence>
            <xs:attribute name="ProductModelID" type="xs:string" />
            <xs:attribute name="ProductModelName" type="xs:string" />
        </xs:complexType>
        <xs:complexType name="Summary" mixed="true" >
            <xs:sequence>
                <xs:any processContents="skip" namespace="http://www.w3.org/1999/xhtml" minOccurs="0" maxOccurs="unbounded" />
            </xs:sequence>
        </xs:complexType>
</xs:schema>'
;
GO 
-- Clean up
DROP XML SCHEMA COLLECTION ProductDescriptionSchemaCollection
GO

C. Importing a schema that does not specify a target namespace

If a schema that does not contain a targetNamespace attribute is imported in a collection, its components are associated with the empty string target namespace as shown in the following example. Note that not associating one or more schemas imported in the collection results in multiple schema components (potentially unrelated) being associated with the default empty string namespace.

-- Create a collection that contains a schema with no target namespace.
CREATE XML SCHEMA COLLECTION MySampleCollection AS '
<schema xmlns="http://www.w3.org/2001/XMLSchema"  xmlns:ns="http://ns">
<element name="e" type="dateTime"/>
</schema>'
GO
-- query will return the names of all the collections that 
--contain a schema with no target namespace
SELECT sys.xml_schema_collections.name 
FROM   sys.xml_schema_collections 
JOIN   sys.xml_schema_namespaces 
ON     sys.xml_schema_collections.xml_collection_id = 
       sys.xml_schema_namespaces.xml_collection_id 
WHERE  sys.xml_schema_namespaces.name=''

See Also

Reference

CREATE XML SCHEMA COLLECTION (Transact-SQL)
DROP XML SCHEMA COLLECTION (Transact-SQL)
EVENTDATA (Transact-SQL)

Other Resources

xml Data Type
Typed vs. Untyped XML
Dropping an XML Schema Collection
Modifying an XML Schema Collection
Guidelines and Limitations in Using XML Schema Collections on the Server

Help and Information

Getting SQL Server 2005 Assistance