December 7, 2012

XML Schemas (XML Type System) Catalog Views

Se more view version maps here: Microsoft Sql System View Version Maps

sys.column_xml_schema_collection_usages

column that is validated by an XML schema.
Column name Sql 2005 Sql 2008 Sql 2008 R2 Sql 2012 Type Description
object_id int The ID of the object to which this column belongs.
column_id int The ID of the column. Is unique within the object.
xml_collection_id int The ID of the collection that contains the validating XML schema namespace of the column.

TSQL

Sql 2005
SELECT [object_id], [column_id], [xml_collection_id] FROM sys.column_xml_schema_collection_usages
Sql 2008
SELECT [object_id], [column_id], [xml_collection_id] FROM sys.column_xml_schema_collection_usages
Sql 2008 R2
SELECT [object_id], [column_id], [xml_collection_id] FROM sys.column_xml_schema_collection_usages
Sql 2012
SELECT [object_id], [column_id], [xml_collection_id] FROM sys.column_xml_schema_collection_usages

Back to Top



sys.parameter_xml_schema_collection_usages

parameter that is validated by an XML schema.
Column name Sql 2005 Sql 2008 Sql 2008 R2 Sql 2012 Type Description
object_id int The ID of the object to which this parameter belongs.
parameter_id int The ID of the parameter. Is unique within the object.
xml_collection_id int The ID of the XML schema collection that contains the validating XML schema namespace of the parameter.

TSQL

Sql 2005
SELECT [object_id], [parameter_id], [xml_collection_id] FROM sys.parameter_xml_schema_collection_usages
Sql 2008
SELECT [object_id], [parameter_id], [xml_collection_id] FROM sys.parameter_xml_schema_collection_usages
Sql 2008 R2
SELECT [object_id], [parameter_id], [xml_collection_id] FROM sys.parameter_xml_schema_collection_usages
Sql 2012
SELECT [object_id], [parameter_id], [xml_collection_id] FROM sys.parameter_xml_schema_collection_usages

Back to Top



sys.xml_indexes

Returns one row per XML index.
Column name Sql 2005 Sql 2008 Sql 2008 R2 Sql 2012 Type Inherited From Description
object_id int sys.indexes ID of the object to which this index belongs.
name sysname sys.indexes Name of the index. name is unique only within the object.
NULL = Heap
index_id int sys.indexes ID of the index. index_id is unique only within the object.
0 = Heap
1 = Clustered index
> 1 = Nonclustered index
type tinyint sys.indexes Type of index:
0 = Heap
1 = Clustered
2 = Nonclustered
3 = XML
type_desc nvarchar(60) sys.indexes Description of index type:
HEAP
CLUSTERED
NONCLUSTERED
XML
is_unique bit sys.indexes 1 = Index is unique.
0 = Index is not unique.
data_space_id int sys.indexes ID of the data space for this index. Data space is either a filegroup or partition scheme.
0 = object_id is a table-valued function.
ignore_dup_key bit sys.indexes 1 = IGNORE_DUP_KEY is ON.
0 = IGNORE_DUP_KEY is OFF.
is_primary_key bit sys.indexes 1 = Index is part of a PRIMARY KEY constraint.
is_unique_constraint bit sys.indexes 1 = Index is part of a UNIQUE constraint.
fill_factor tinyint sys.indexes > 0 = FILLFACTOR percentage used when the index was created or rebuilt.
0 = Default value
is_padded bit sys.indexes 1 = PADINDEX is ON.
0 = PADINDEX is OFF.
is_disabled bit sys.indexes 1 = Index is disabled.
0 = Index is not disabled.
is_hypothetical bit sys.indexes 1 = Index is hypothetical and cannot be used directly as a data access path. Hypothetical indexes hold column-level statistics.
0 = Index is not hypothetical.
allow_row_locks bit sys.indexes 1 = Index allows row locks.
0 = Index does not allow row locks.
allow_page_locks bit sys.indexes 1 = Index allows page locks.
0 = Index does not allow page locks.
using_xml_index_id int   NULL = Primary XML index.
Nonnull = Secondary XML index.
Nonnull is a self-join reference to the primary XML index.
secondary_type char(1)   Type description of secondary index:
P = PATH secondary XML index
V = VALUE secondary XML index
R = PROPERTY secondary XML index
NULL = Primary XML index
secondary_type_desc nvarchar(60)   Type description of secondary index:
PATH = PATH secondary XML index
VALUE = VALUE secondary XML index
PROPERTY = PROPERTY secondary xml indexes.
NULL = Primary XML index
has_filter   bit sys.indexes 1 = Index has a filter and only contains rows that satisfy the filter definition.
0 = Index does not have a filter.
filter_definition   nvarchar(max) sys.indexes Expression for the subset of rows included in the filtered index.
NULL for heap or non-filtered index.

TSQL

Sql 2005
SELECT [object_id], [name], [index_id], [type], [type_desc], [is_unique], [data_space_id], [ignore_dup_key], [is_primary_key], [is_unique_constraint], [fill_factor], [is_padded], [is_disabled], [is_hypothetical], [allow_row_locks], [allow_page_locks], [using_xml_index_id], [secondary_type], [secondary_type_desc] FROM sys.xml_indexes
Sql 2008
SELECT [object_id], [name], [index_id], [type], [type_desc], [is_unique], [data_space_id], [ignore_dup_key], [is_primary_key], [is_unique_constraint], [fill_factor], [is_padded], [is_disabled], [is_hypothetical], [allow_row_locks], [allow_page_locks], [has_filter], [filter_definition], [using_xml_index_id], [secondary_type], [secondary_type_desc] FROM sys.xml_indexes
Sql 2008 R2
SELECT [object_id], [name], [index_id], [type], [type_desc], [is_unique], [data_space_id], [ignore_dup_key], [is_primary_key], [is_unique_constraint], [fill_factor], [is_padded], [is_disabled], [is_hypothetical], [allow_row_locks], [allow_page_locks], [has_filter], [filter_definition], [using_xml_index_id], [secondary_type], [secondary_type_desc] FROM sys.xml_indexes
Sql 2012
SELECT [object_id], [name], [index_id], [type], [type_desc], [is_unique], [data_space_id], [ignore_dup_key], [is_primary_key], [is_unique_constraint], [fill_factor], [is_padded], [is_disabled], [is_hypothetical], [allow_row_locks], [allow_page_locks], [has_filter], [filter_definition], [using_xml_index_id], [secondary_type], [secondary_type_desc] FROM sys.xml_indexes

Back to Top



sys.xml_schema_attributes

Returns a row per XML schema component that is an attribute, symbol_space of A.
Column name Sql 2005 Sql 2008 Sql 2008 R2 Sql 2012 Type Inherited From Description
xml_component_id int sys.xml_schema_components Unique ID of the XML schema component in the database.
xml_collection_id int sys.xml_schema_components ID of the XML schema collection that contains the namespace of this component.
xml_namespace_id int sys.xml_schema_components ID of the XML namespace within the collection.
is_qualified bit sys.xml_schema_components 1 = This component has an explicit namespace qualifier.
0 = This is a locally scoped component. In this case, the pair, namespace_id, collection_id, refers to the "no namespace" targetNamespace.
For wildcard components this value will be equal to 1.
name nvarchar (4000) sys.xml_schema_components Unique name of the XML schema component. Is NULL if the component is unnamed.
symbol_space char(1) sys.xml_schema_components Space in which this symbol name is unique, based on kind:
N = None
T = Type
E = Element
M = Model-Group
A = Attribute
G = Attribute-Group
symbol_space_desc nvarchar (60) sys.xml_schema_components Description of space in which this symbol name is unique, based on kind:
NONE

TYPE

ELEMENT

MODEL_GROUP

ATTRIBUTE

ATTRIBUTE_GROUP
kind char(1) sys.xml_schema_components Kind of XML schema component.
N = Any Type (special intrinsic component)
Z = Any Simple Type (special intrinsic component)
P = Primitive Type (intrinsic types)
S = Simple Type
L = List Type
U = Union Type
C = Complex Simple Type (derived from Simple)
K = Complex Type
E = Element
M = Model-Group
W = Element-Wildcard
A = Attribute
G = Attribute-Group
V = Attribute-Wildcard
kind_desc nvarchar (60) sys.xml_schema_components Description of the kind of XML schema component:
ANY_TYPE

ANY_SIMPLE_TYPE

PRIMITIVE_TYPE

SIMPLE_TYPE

LIST_TYPE

UNION_TYPE

COMPLEX_SIMPLE_TYPE

COMPLEX_TYPE

ELEMENT

MODEL_GROUP

ELEMENT_WILDCARD

ATTRIBUTE

ATTRIBUTE_GROUP

ATTRIBUTE_WILDCARD
derivation char(1) sys.xml_schema_components Derivation method for derived types:
N = None (not derived)
X = Extension
R = Restriction
S = Substitution
derivation_desc nvarchar (60) sys.xml_schema_components Description of derivation method for derived types:
NONE

EXTENSION

RESTRICTION

SUBSTITUTION
base_xml_component_id int sys.xml_schema_components ID of the component from which this component is derived. NULL if there is none.
scoping_xml_component_id int sys.xml_schema_components Unique ID of the scoping component. NULL if there is none (global scope).
is_default_fixed bit   1 = The default value is a fixed value. This value cannot be overridden in an XML instance.
0 = The default value is not a fixed value for the attribute. (default)
must_be_qualified bit   1 = The attribute must be explicitly namespace qualified.
0 = The attribute may be implicitly namespace qualified. (default)
default_value nvarchar (4000)   Default value of the attribute. Is NULL if a default value is not supplied.

TSQL

Sql 2005
SELECT [xml_component_id], [xml_collection_id], [xml_namespace_id], [is_qualified], [name], [symbol_space], [symbol_space_desc], [kind], [kind_desc], [derivation], [derivation_desc], [base_xml_component_id], [scoping_xml_component_id], [is_default_fixed], [must_be_qualified], [default_value] FROM sys.xml_schema_attributes
Sql 2008
SELECT [xml_component_id], [xml_collection_id], [xml_namespace_id], [is_qualified], [name], [symbol_space], [symbol_space_desc], [kind], [kind_desc], [derivation], [derivation_desc], [base_xml_component_id], [scoping_xml_component_id], [is_default_fixed], [must_be_qualified], [default_value] FROM sys.xml_schema_attributes
Sql 2008 R2
SELECT [xml_component_id], [xml_collection_id], [xml_namespace_id], [is_qualified], [name], [symbol_space], [symbol_space_desc], [kind], [kind_desc], [derivation], [derivation_desc], [base_xml_component_id], [scoping_xml_component_id], [is_default_fixed], [must_be_qualified], [default_value] FROM sys.xml_schema_attributes
Sql 2012
SELECT [xml_component_id], [xml_collection_id], [xml_namespace_id], [is_qualified], [name], [symbol_space], [symbol_space_desc], [kind], [kind_desc], [derivation], [derivation_desc], [base_xml_component_id], [scoping_xml_component_id], [is_default_fixed], [must_be_qualified], [default_value] FROM sys.xml_schema_attributes

Back to Top



sys.xml_schema_collections

Returns a row per XML schema collection. An XML schema collection is a named set of XSD definitions. The XML schema collection itself is contained in a relational schema, and it is identified by a schema-scoped Transact-SQL name. The following tuples are unique: xml_collection_id, and schema_id and name.
Column name Sql 2005 Sql 2008 Sql 2008 R2 Sql 2012 Type Description
xml_collection_id int ID of the XML schema collection. Unique within the database.
schema_id int ID of the relational schema that contains this XML schema collection.
principal_id int ID of the individual owner if different from the schema owner. By default, schema-contained objects are owned by the schema owner. However, an alternate owner may be specified by using the ALTER AUTHORIZATION statement to change ownership.
NULL = No alternate individual owner.
name sysname Name of the XML schema collection.
create_date datetime Date the XML schema collection was created.
modify_date datetime Date the XML schema collection was last altered.

TSQL

Sql 2005
SELECT [xml_collection_id], [schema_id], [principal_id], [name], [create_date], [modify_date] FROM sys.xml_schema_collections
Sql 2008
SELECT [xml_collection_id], [schema_id], [principal_id], [name], [create_date], [modify_date] FROM sys.xml_schema_collections
Sql 2008 R2
SELECT [xml_collection_id], [schema_id], [principal_id], [name], [create_date], [modify_date] FROM sys.xml_schema_collections
Sql 2012
SELECT [xml_collection_id], [schema_id], [principal_id], [name], [create_date], [modify_date] FROM sys.xml_schema_collections

Back to Top



sys.xml_schema_component_placements

Returns a row per placement for XML schema components.
Column name Sql 2005 Sql 2008 Sql 2008 R2 Sql 2012 Type Description
xml_component_id int ID of the XML schema component that owns this placement.
placement_id int ID of the placement. This is unique within the owning XML schema component.
placed_xml_component_id int ID of the placed XML schema component.
is_default_fixed bit 1 = The default value is a fixed value. This value cannot be overridden in an XML instance.
0 = The value can be overridden.(default)
min_occurrences int Minimum number of placed component occurs.
max_occurrences int Maximum number of placed component occurs.
default_value nvarchar (4000) Default value if one is supplied. Is NULL if a default value is not supplied.

TSQL

Sql 2005
SELECT [xml_component_id], [placement_id], [placed_xml_component_id], [is_default_fixed], [min_occurrences], [max_occurrences], [default_value] FROM sys.xml_schema_component_placements
Sql 2008
SELECT [xml_component_id], [placement_id], [placed_xml_component_id], [is_default_fixed], [min_occurrences], [max_occurrences], [default_value] FROM sys.xml_schema_component_placements
Sql 2008 R2
SELECT [xml_component_id], [placement_id], [placed_xml_component_id], [is_default_fixed], [min_occurrences], [max_occurrences], [default_value] FROM sys.xml_schema_component_placements
Sql 2012
SELECT [xml_component_id], [placement_id], [placed_xml_component_id], [is_default_fixed], [min_occurrences], [max_occurrences], [default_value] FROM sys.xml_schema_component_placements

Back to Top



sys.xml_schema_components

Returns a row per component of an XML schema. The pair (collection_id, namespace_id) is a compound foreign key to the containing namespace. For named components, the values for symbol_space, name, scoping_xml_component_id, is_qualified, xml_namespace_id, xml_collection_id are unique.
Column name Sql 2005 Sql 2008 Sql 2008 R2 Sql 2012 Type Description
xml_component_id int Unique ID of the XML schema component in the database.
xml_collection_id int ID of the XML schema collection that contains the namespace of this component.
xml_namespace_id int ID of the XML namespace within the collection.
is_qualified bit 1 = This component has an explicit namespace qualifier.
0 = This is a locally scoped component. In this case, the pair, namespace_id, collection_id, refers to the "no namespace" targetNamespace.
For wildcard components this value will be equal to 1.
name nvarchar (4000) Unique name of the XML schema component. Is NULL if the component is unnamed.
symbol_space char(1) Space in which this symbol name is unique, based on kind:
N = None
T = Type
E = Element
M = Model-Group
A = Attribute
G = Attribute-Group
symbol_space_desc nvarchar (60) Description of space in which this symbol name is unique, based on kind:
NONE

TYPE

ELEMENT

MODEL_GROUP

ATTRIBUTE

ATTRIBUTE_GROUP
kind char(1) Kind of XML schema component.
N = Any Type (special intrinsic component)
Z = Any Simple Type (special intrinsic component)
P = Primitive Type (intrinsic types)
S = Simple Type
L = List Type
U = Union Type
C = Complex Simple Type (derived from Simple)
K = Complex Type
E = Element
M = Model-Group
W = Element-Wildcard
A = Attribute
G = Attribute-Group
V = Attribute-Wildcard
kind_desc nvarchar (60) Description of the kind of XML schema component:
ANY_TYPE

ANY_SIMPLE_TYPE

PRIMITIVE_TYPE

SIMPLE_TYPE

LIST_TYPE

UNION_TYPE

COMPLEX_SIMPLE_TYPE

COMPLEX_TYPE

ELEMENT

MODEL_GROUP

ELEMENT_WILDCARD

ATTRIBUTE

ATTRIBUTE_GROUP

ATTRIBUTE_WILDCARD
derivation char(1) Derivation method for derived types:
N = None (not derived)
X = Extension
R = Restriction
S = Substitution
derivation_desc nvarchar (60) Description of derivation method for derived types:
NONE

EXTENSION

RESTRICTION

SUBSTITUTION
base_xml_component_id int ID of the component from which this component is derived. NULL if there is none.
scoping_xml_component_id int Unique ID of the scoping component. NULL if there is none (global scope).

TSQL

Sql 2005
SELECT [xml_component_id], [xml_collection_id], [xml_namespace_id], [is_qualified], [name], [symbol_space], [symbol_space_desc], [kind], [kind_desc], [derivation], [derivation_desc], [base_xml_component_id], [scoping_xml_component_id] FROM sys.xml_schema_components
Sql 2008
SELECT [xml_component_id], [xml_collection_id], [xml_namespace_id], [is_qualified], [name], [symbol_space], [symbol_space_desc], [kind], [kind_desc], [derivation], [derivation_desc], [base_xml_component_id], [scoping_xml_component_id] FROM sys.xml_schema_components
Sql 2008 R2
SELECT [xml_component_id], [xml_collection_id], [xml_namespace_id], [is_qualified], [name], [symbol_space], [symbol_space_desc], [kind], [kind_desc], [derivation], [derivation_desc], [base_xml_component_id], [scoping_xml_component_id] FROM sys.xml_schema_components
Sql 2012
SELECT [xml_component_id], [xml_collection_id], [xml_namespace_id], [is_qualified], [name], [symbol_space], [symbol_space_desc], [kind], [kind_desc], [derivation], [derivation_desc], [base_xml_component_id], [scoping_xml_component_id] FROM sys.xml_schema_components

Back to Top



sys.xml_schema_elements

Returns a row per XML schema component that is a Type, symbol_space of E.
Column name Sql 2005 Sql 2008 Sql 2008 R2 Sql 2012 Type Inherited From Description
xml_component_id int sys.xml_schema_components Unique ID of the XML schema component in the database.
xml_collection_id int sys.xml_schema_components ID of the XML schema collection that contains the namespace of this component.
xml_namespace_id int sys.xml_schema_components ID of the XML namespace within the collection.
is_qualified bit sys.xml_schema_components 1 = This component has an explicit namespace qualifier.
0 = This is a locally scoped component. In this case, the pair, namespace_id, collection_id, refers to the "no namespace" targetNamespace.
For wildcard components this value will be equal to 1.
name nvarchar (4000) sys.xml_schema_components Unique name of the XML schema component. Is NULL if the component is unnamed.
symbol_space char(1) sys.xml_schema_components Space in which this symbol name is unique, based on kind:
N = None
T = Type
E = Element
M = Model-Group
A = Attribute
G = Attribute-Group
symbol_space_desc nvarchar (60) sys.xml_schema_components Description of space in which this symbol name is unique, based on kind:
NONE

TYPE

ELEMENT

MODEL_GROUP

ATTRIBUTE

ATTRIBUTE_GROUP
kind char(1) sys.xml_schema_components Kind of XML schema component.
N = Any Type (special intrinsic component)
Z = Any Simple Type (special intrinsic component)
P = Primitive Type (intrinsic types)
S = Simple Type
L = List Type
U = Union Type
C = Complex Simple Type (derived from Simple)
K = Complex Type
E = Element
M = Model-Group
W = Element-Wildcard
A = Attribute
G = Attribute-Group
V = Attribute-Wildcard
kind_desc nvarchar (60) sys.xml_schema_components Description of the kind of XML schema component:
ANY_TYPE

ANY_SIMPLE_TYPE

PRIMITIVE_TYPE

SIMPLE_TYPE

LIST_TYPE

UNION_TYPE

COMPLEX_SIMPLE_TYPE

COMPLEX_TYPE

ELEMENT

MODEL_GROUP

ELEMENT_WILDCARD

ATTRIBUTE

ATTRIBUTE_GROUP

ATTRIBUTE_WILDCARD
derivation char(1) sys.xml_schema_components Derivation method for derived types:
N = None (not derived)
X = Extension
R = Restriction
S = Substitution
derivation_desc nvarchar (60) sys.xml_schema_components Description of derivation method for derived types:
NONE

EXTENSION

RESTRICTION

SUBSTITUTION
base_xml_component_id int sys.xml_schema_components ID of the component from which this component is derived. NULL if there is none.
scoping_xml_component_id int sys.xml_schema_components Unique ID of the scoping component. NULL if there is none (global scope).
is_default_fixed bit   1 = Default value is a fixed value. This value cannot be overridden in XML instance.
0 = Default value is not a fixed value for the element. (default).
is_abstract bit   1 = Element is abstract and cannot be used in an instance document. A member of the substitution group of the element must appear in the instance document.
0 = Element is not abstract. (default).
is_nillable bit   1 = Element is nillable.
0 = Element is not nillable. (default)
must_be_qualified bit   1 = Element must be explicitly namespace qualified.
0 = Element may be implicitly namespace qualified. (default)
is_extension_blocked bit   1 = Replacement with an instance of an extension type is blocked.
0 = Replacement with extension type is allowed. (default)
is_restriction_blocked bit   1 = Replacement with an instance of a restriction type is blocked.
0 = Replacement with restriction type is allowed. (default)
is_substitution_blocked bit   1 = Instance of a substitution group cannot be used.
0 = Replacement with substitution group is permitted. (default)
is_final_extension bit   1 = Replacement with an instance of an extension type is disallowed.
0 = Replacement in an instance of an extension type is allowed. (default)
is_final_restriction bit   1 = Replacement with an instance of a restriction type is disallowed.
0 = Replacement in an instance of a restriction type is allowed. (default)
default_value nvarchar (4000)   Default value of the element. NULL if a default value is not supplied.

TSQL

Sql 2005
SELECT [xml_component_id], [xml_collection_id], [xml_namespace_id], [is_qualified], [name], [symbol_space], [symbol_space_desc], [kind], [kind_desc], [derivation], [derivation_desc], [base_xml_component_id], [scoping_xml_component_id], [is_default_fixed], [is_abstract], [is_nillable], [must_be_qualified], [is_extension_blocked], [is_restriction_blocked], [is_substitution_blocked], [is_final_extension], [is_final_restriction], [default_value] FROM sys.xml_schema_elements
Sql 2008
SELECT [xml_component_id], [xml_collection_id], [xml_namespace_id], [is_qualified], [name], [symbol_space], [symbol_space_desc], [kind], [kind_desc], [derivation], [derivation_desc], [base_xml_component_id], [scoping_xml_component_id], [is_default_fixed], [is_abstract], [is_nillable], [must_be_qualified], [is_extension_blocked], [is_restriction_blocked], [is_substitution_blocked], [is_final_extension], [is_final_restriction], [default_value] FROM sys.xml_schema_elements
Sql 2008 R2
SELECT [xml_component_id], [xml_collection_id], [xml_namespace_id], [is_qualified], [name], [symbol_space], [symbol_space_desc], [kind], [kind_desc], [derivation], [derivation_desc], [base_xml_component_id], [scoping_xml_component_id], [is_default_fixed], [is_abstract], [is_nillable], [must_be_qualified], [is_extension_blocked], [is_restriction_blocked], [is_substitution_blocked], [is_final_extension], [is_final_restriction], [default_value] FROM sys.xml_schema_elements
Sql 2012
SELECT [xml_component_id], [xml_collection_id], [xml_namespace_id], [is_qualified], [name], [symbol_space], [symbol_space_desc], [kind], [kind_desc], [derivation], [derivation_desc], [base_xml_component_id], [scoping_xml_component_id], [is_default_fixed], [is_abstract], [is_nillable], [must_be_qualified], [is_extension_blocked], [is_restriction_blocked], [is_substitution_blocked], [is_final_extension], [is_final_restriction], [default_value] FROM sys.xml_schema_elements

Back to Top



sys.xml_schema_facets

Returns a row per facet (restriction) of an xml-type definition (corresponds to sys.xml_types).
Column name Sql 2005 Sql 2008 Sql 2008 R2 Sql 2012 Type Description
xml_component_id int ID of XML component (type) to which this facet belongs.
facet_id int ID (1-based ordinal) of facet, unique within component-id.
kind char(2) Kind of facet:
LG = Length
LN = Minimum Length
LX = Maximum Length
PT = Pattern (regular expression)
EU = Enumeration
IN = Minimum Inclusive value
IX = Maximum Inclusive value
EN = Minimum Exclusive value
EX = Maximum Exclusive value
DT = Total Digits
DF = Fraction Digits
WS = White Space normalization
kind_desc nvarchar (60) Description of kind of facet:
LENGTH
MINIMUM_LENGTH
MAXIMUM_LENGTH
PATTERN
ENUMERATION
MINIMUM_INCLUSIVE_VALUE
MAXIMUM_INCLUSIVE_VALUE
MINIMUM_EXCLUSIVE_VALUE
MAXIMUM_EXCLUSIVE_VALUE
TOTAL_DIGITS
FRACTION_DIGITS
WHITESPACE_NORMALIZATION
is_fixed bit 1 = Facet has a fixed, prespecified value.
0 = No fixed value. (default)
value nvarchar (4000) Fixed, pre-specified value of the facet.

TSQL

Sql 2005
SELECT [xml_component_id], [facet_id], [kind], [kind_desc], [is_fixed], [value] FROM sys.xml_schema_facets
Sql 2008
SELECT [xml_component_id], [facet_id], [kind], [kind_desc], [is_fixed], [value] FROM sys.xml_schema_facets
Sql 2008 R2
SELECT [xml_component_id], [facet_id], [kind], [kind_desc], [is_fixed], [value] FROM sys.xml_schema_facets
Sql 2012
SELECT [xml_component_id], [facet_id], [kind], [kind_desc], [is_fixed], [value] FROM sys.xml_schema_facets

Back to Top



sys.xml_schema_model_groups

Returns a row per XML schema component that is a Model-Group, symbol_space of M..
Column name Sql 2005 Sql 2008 Sql 2008 R2 Sql 2012 Type Inherited From Description
xml_component_id int sys.xml_schema_components Unique ID of the XML schema component in the database.
xml_collection_id int sys.xml_schema_components ID of the XML schema collection that contains the namespace of this component.
xml_namespace_id int sys.xml_schema_components ID of the XML namespace within the collection.
is_qualified bit sys.xml_schema_components 1 = This component has an explicit namespace qualifier.
0 = This is a locally scoped component. In this case, the pair, namespace_id, collection_id, refers to the "no namespace" targetNamespace.
For wildcard components this value will be equal to 1.
name nvarchar (4000) sys.xml_schema_components Unique name of the XML schema component. Is NULL if the component is unnamed.
symbol_space char(1) sys.xml_schema_components Space in which this symbol name is unique, based on kind:
N = None
T = Type
E = Element
M = Model-Group
A = Attribute
G = Attribute-Group
symbol_space_desc nvarchar (60) sys.xml_schema_components Description of space in which this symbol name is unique, based on kind:
NONE

TYPE

ELEMENT

MODEL_GROUP

ATTRIBUTE

ATTRIBUTE_GROUP
kind char(1) sys.xml_schema_components Kind of XML schema component.
N = Any Type (special intrinsic component)
Z = Any Simple Type (special intrinsic component)
P = Primitive Type (intrinsic types)
S = Simple Type
L = List Type
U = Union Type
C = Complex Simple Type (derived from Simple)
K = Complex Type
E = Element
M = Model-Group
W = Element-Wildcard
A = Attribute
G = Attribute-Group
V = Attribute-Wildcard
kind_desc nvarchar (60) sys.xml_schema_components Description of the kind of XML schema component:
ANY_TYPE

ANY_SIMPLE_TYPE

PRIMITIVE_TYPE

SIMPLE_TYPE

LIST_TYPE

UNION_TYPE

COMPLEX_SIMPLE_TYPE

COMPLEX_TYPE

ELEMENT

MODEL_GROUP

ELEMENT_WILDCARD

ATTRIBUTE

ATTRIBUTE_GROUP

ATTRIBUTE_WILDCARD
derivation char(1) sys.xml_schema_components Derivation method for derived types:
N = None (not derived)
X = Extension
R = Restriction
S = Substitution
derivation_desc nvarchar (60) sys.xml_schema_components Description of derivation method for derived types:
NONE

EXTENSION

RESTRICTION

SUBSTITUTION
base_xml_component_id int sys.xml_schema_components ID of the component from which this component is derived. NULL if there is none.
scoping_xml_component_id int sys.xml_schema_components Unique ID of the scoping component. NULL if there is none (global scope).
compositor char(1)   Compositor kind of group:
A = XSD Group
C = XSD Group
S = XSD Group
compositor_desc nvarchar (60)   Description of compositor kind of group:
XSD_ALL_GROUP

XSD_CHOICE_GROUP

XSD_SEQUENCE_GROUP

TSQL

Sql 2005
SELECT [xml_component_id], [xml_collection_id], [xml_namespace_id], [is_qualified], [name], [symbol_space], [symbol_space_desc], [kind], [kind_desc], [derivation], [derivation_desc], [base_xml_component_id], [scoping_xml_component_id], [compositor], [compositor_desc] FROM sys.xml_schema_model_groups
Sql 2008
SELECT [xml_component_id], [xml_collection_id], [xml_namespace_id], [is_qualified], [name], [symbol_space], [symbol_space_desc], [kind], [kind_desc], [derivation], [derivation_desc], [base_xml_component_id], [scoping_xml_component_id], [compositor], [compositor_desc] FROM sys.xml_schema_model_groups
Sql 2008 R2
SELECT [xml_component_id], [xml_collection_id], [xml_namespace_id], [is_qualified], [name], [symbol_space], [symbol_space_desc], [kind], [kind_desc], [derivation], [derivation_desc], [base_xml_component_id], [scoping_xml_component_id], [compositor], [compositor_desc] FROM sys.xml_schema_model_groups
Sql 2012
SELECT [xml_component_id], [xml_collection_id], [xml_namespace_id], [is_qualified], [name], [symbol_space], [symbol_space_desc], [kind], [kind_desc], [derivation], [derivation_desc], [base_xml_component_id], [scoping_xml_component_id], [compositor], [compositor_desc] FROM sys.xml_schema_model_groups

Back to Top



sys.xml_schema_namespaces

Returns a row per XSD-defined XML namespace. The following tuples are unique: collection_id, namespace_id, and collection_id, and name.
Column name Sql 2005 Sql 2008 Sql 2008 R2 Sql 2012 Type Description
xml_collection_id int ID of the XML schema collection that contains this namespace.
name nvarchar (4000) Name of XML namespace. Blank name indicates no target namespace.
xml_namespace_id int 1-based ordinal that uniquely identifies the XML namespace in the database.

TSQL

Sql 2005
SELECT [xml_collection_id], [name], [xml_namespace_id] FROM sys.xml_schema_namespaces
Sql 2008
SELECT [xml_collection_id], [name], [xml_namespace_id] FROM sys.xml_schema_namespaces
Sql 2008 R2
SELECT [xml_collection_id], [name], [xml_namespace_id] FROM sys.xml_schema_namespaces
Sql 2012
SELECT [xml_collection_id], [name], [xml_namespace_id] FROM sys.xml_schema_namespaces

Back to Top



sys.xml_schema_types

Returns a row per XML schema component that is a Type, symbol_space of T.
Column name Sql 2005 Sql 2008 Sql 2008 R2 Sql 2012 Type Inherited From Description
xml_component_id int sys.xml_schema_components Unique ID of the XML schema component in the database.
xml_collection_id int sys.xml_schema_components ID of the XML schema collection that contains the namespace of this component.
xml_namespace_id int sys.xml_schema_components ID of the XML namespace within the collection.
is_qualified bit sys.xml_schema_components 1 = This component has an explicit namespace qualifier.
0 = This is a locally scoped component. In this case, the pair, namespace_id, collection_id, refers to the "no namespace" targetNamespace.
For wildcard components this value will be equal to 1.
name nvarchar (4000) sys.xml_schema_components Unique name of the XML schema component. Is NULL if the component is unnamed.
symbol_space char(1) sys.xml_schema_components Space in which this symbol name is unique, based on kind:
N = None
T = Type
E = Element
M = Model-Group
A = Attribute
G = Attribute-Group
symbol_space_desc nvarchar (60) sys.xml_schema_components Description of space in which this symbol name is unique, based on kind:
NONE

TYPE

ELEMENT

MODEL_GROUP

ATTRIBUTE

ATTRIBUTE_GROUP
kind char(1) sys.xml_schema_components Kind of XML schema component.
N = Any Type (special intrinsic component)
Z = Any Simple Type (special intrinsic component)
P = Primitive Type (intrinsic types)
S = Simple Type
L = List Type
U = Union Type
C = Complex Simple Type (derived from Simple)
K = Complex Type
E = Element
M = Model-Group
W = Element-Wildcard
A = Attribute
G = Attribute-Group
V = Attribute-Wildcard
kind_desc nvarchar (60) sys.xml_schema_components Description of the kind of XML schema component:
ANY_TYPE

ANY_SIMPLE_TYPE

PRIMITIVE_TYPE

SIMPLE_TYPE

LIST_TYPE

UNION_TYPE

COMPLEX_SIMPLE_TYPE

COMPLEX_TYPE

ELEMENT

MODEL_GROUP

ELEMENT_WILDCARD

ATTRIBUTE

ATTRIBUTE_GROUP

ATTRIBUTE_WILDCARD
derivation char(1) sys.xml_schema_components Derivation method for derived types:
N = None (not derived)
X = Extension
R = Restriction
S = Substitution
derivation_desc nvarchar (60) sys.xml_schema_components Description of derivation method for derived types:
NONE

EXTENSION

RESTRICTION

SUBSTITUTION
base_xml_component_id int sys.xml_schema_components ID of the component from which this component is derived. NULL if there is none.
scoping_xml_component_id int sys.xml_schema_components Unique ID of the scoping component. NULL if there is none (global scope).
is_abstract bit   1 = Type is an abstract type. All instances of an element of this type must use xsi:type to indicate a derived type that is not abstract.
0 = Type is not abstract. (default)
allows_mixed_content bit   1 = Mixed content is allowed
0 = Mixed content is not allowed. (default)
is_extension_blocked bit   1 = Replacement with an extension of the type is blocked in instances when the block attribute on the complexType definition or the blockDefault attribute of the ancestor element information item is set to "extension" or "#all".
0 =Replacement with extension is not blocked.
is_restriction_blocked bit   1 = Replacement with a restriction of the type is blocked in instances when the block attribute on the complexType definition or the blockDefault attribute of the ancestor element information item is set to "restriction" or "#all".
0 = Replacement with restriction is not blocked. (default)
is_final_extension bit   1 = Derivation by extension of the type is blocked when the final attribute on the complexType definition or the finalDefault attribute of the ancestor element information item is set to "extension" or "#all".
0 = Extension is allowed. (default)
is_final_restriction bit   1 = Derivation by restriction of the type is blocked when the final attribute on the simple or complexType definition or the finalDefault attribute of the ancestor element information item is set to "restriction" or "#all".
0 = Restriction is allowed. (default)
is_final_list_member bit   1 = This simple type cannot be used as the item type in a list.
0 = This type is a complex type, or it can be used as list item type. (default)
is_final_union_member bit   1 = This simple type cannot be used as the member type of a union type.
0 = This type is a complex type. or it can be used as union member type. (default)

TSQL

Sql 2005
SELECT [xml_component_id], [xml_collection_id], [xml_namespace_id], [is_qualified], [name], [symbol_space], [symbol_space_desc], [kind], [kind_desc], [derivation], [derivation_desc], [base_xml_component_id], [scoping_xml_component_id], [is_abstract], [allows_mixed_content], [is_extension_blocked], [is_restriction_blocked], [is_final_extension], [is_final_restriction], [is_final_list_member], [is_final_union_member] FROM sys.xml_schema_types
Sql 2008
SELECT [xml_component_id], [xml_collection_id], [xml_namespace_id], [is_qualified], [name], [symbol_space], [symbol_space_desc], [kind], [kind_desc], [derivation], [derivation_desc], [base_xml_component_id], [scoping_xml_component_id], [is_abstract], [allows_mixed_content], [is_extension_blocked], [is_restriction_blocked], [is_final_extension], [is_final_restriction], [is_final_list_member], [is_final_union_member] FROM sys.xml_schema_types
Sql 2008 R2
SELECT [xml_component_id], [xml_collection_id], [xml_namespace_id], [is_qualified], [name], [symbol_space], [symbol_space_desc], [kind], [kind_desc], [derivation], [derivation_desc], [base_xml_component_id], [scoping_xml_component_id], [is_abstract], [allows_mixed_content], [is_extension_blocked], [is_restriction_blocked], [is_final_extension], [is_final_restriction], [is_final_list_member], [is_final_union_member] FROM sys.xml_schema_types
Sql 2012
SELECT [xml_component_id], [xml_collection_id], [xml_namespace_id], [is_qualified], [name], [symbol_space], [symbol_space_desc], [kind], [kind_desc], [derivation], [derivation_desc], [base_xml_component_id], [scoping_xml_component_id], [is_abstract], [allows_mixed_content], [is_extension_blocked], [is_restriction_blocked], [is_final_extension], [is_final_restriction], [is_final_list_member], [is_final_union_member] FROM sys.xml_schema_types

Back to Top



sys.xml_schema_wildcard_namespaces

Returns a row per enumerated namespace for an XML schema wildcard.
Column name Sql 2005 Sql 2008 Sql 2008 R2 Sql 2012 Type Description
xml_component_id int ID of the XML schema component (wildcard) to which this applies.
namespace nvarchar(4000) Name or URI of the namespace that is used by the XML wildcard.

TSQL

Sql 2005
SELECT [xml_component_id], [namespace] FROM sys.xml_schema_wildcard_namespaces
Sql 2008
SELECT [xml_component_id], [namespace] FROM sys.xml_schema_wildcard_namespaces
Sql 2008 R2
SELECT [xml_component_id], [namespace] FROM sys.xml_schema_wildcard_namespaces
Sql 2012
SELECT [xml_component_id], [namespace] FROM sys.xml_schema_wildcard_namespaces

Back to Top



sys.xml_schema_wildcards

Returns a row per XML schema component that is an Attribute-Wildcard (kind of V) or Element-Wildcard (kind of W), both with symbol_space of N.
Column name Sql 2005 Sql 2008 Sql 2008 R2 Sql 2012 Type Inherited From Description
xml_component_id int sys.xml_schema_components Unique ID of the XML schema component in the database.
xml_collection_id int sys.xml_schema_components ID of the XML schema collection that contains the namespace of this component.
xml_namespace_id int sys.xml_schema_components ID of the XML namespace within the collection.
is_qualified bit sys.xml_schema_components 1 = This component has an explicit namespace qualifier.
0 = This is a locally scoped component. In this case, the pair, namespace_id, collection_id, refers to the "no namespace" targetNamespace.
For wildcard components this value will be equal to 1.
name nvarchar (4000) sys.xml_schema_components Unique name of the XML schema component. Is NULL if the component is unnamed.
symbol_space char(1) sys.xml_schema_components Space in which this symbol name is unique, based on kind:
N = None
T = Type
E = Element
M = Model-Group
A = Attribute
G = Attribute-Group
symbol_space_desc nvarchar (60) sys.xml_schema_components Description of space in which this symbol name is unique, based on kind:
NONE

TYPE

ELEMENT

MODEL_GROUP

ATTRIBUTE

ATTRIBUTE_GROUP
kind char(1) sys.xml_schema_components Kind of XML schema component.
N = Any Type (special intrinsic component)
Z = Any Simple Type (special intrinsic component)
P = Primitive Type (intrinsic types)
S = Simple Type
L = List Type
U = Union Type
C = Complex Simple Type (derived from Simple)
K = Complex Type
E = Element
M = Model-Group
W = Element-Wildcard
A = Attribute
G = Attribute-Group
V = Attribute-Wildcard
kind_desc nvarchar (60) sys.xml_schema_components Description of the kind of XML schema component:
ANY_TYPE

ANY_SIMPLE_TYPE

PRIMITIVE_TYPE

SIMPLE_TYPE

LIST_TYPE

UNION_TYPE

COMPLEX_SIMPLE_TYPE

COMPLEX_TYPE

ELEMENT

MODEL_GROUP

ELEMENT_WILDCARD

ATTRIBUTE

ATTRIBUTE_GROUP

ATTRIBUTE_WILDCARD
derivation char(1) sys.xml_schema_components Derivation method for derived types:
N = None (not derived)
X = Extension
R = Restriction
S = Substitution
derivation_desc nvarchar (60) sys.xml_schema_components Description of derivation method for derived types:
NONE

EXTENSION

RESTRICTION

SUBSTITUTION
base_xml_component_id int sys.xml_schema_components ID of the component from which this component is derived. NULL if there is none.
scoping_xml_component_id int sys.xml_schema_components Unique ID of the scoping component. NULL if there is none (global scope).
process_content char(1)   Indicates how contents are processed.
S = Strict validation (must validate)
L = Lax validation (validate if possible)
P = Skip validation
process_content_desc nvarchar(60)   Description of how contents are processed:
STRICT_VALIDATION

LAX_VALIDATION

SKIP_VALIDATION
disallow_namespaces bit   0 = Namespaces enumerated in sys.xml_schema_wildcard_namespaces2 are the only ones allowed.
1 = Namespaces are the only ones disallowed.

TSQL

Sql 2005
SELECT [xml_component_id], [xml_collection_id], [xml_namespace_id], [is_qualified], [name], [symbol_space], [symbol_space_desc], [kind], [kind_desc], [derivation], [derivation_desc], [base_xml_component_id], [scoping_xml_component_id], [process_content], [process_content_desc], [disallow_namespaces] FROM sys.xml_schema_wildcards
Sql 2008
SELECT [xml_component_id], [xml_collection_id], [xml_namespace_id], [is_qualified], [name], [symbol_space], [symbol_space_desc], [kind], [kind_desc], [derivation], [derivation_desc], [base_xml_component_id], [scoping_xml_component_id], [process_content], [process_content_desc], [disallow_namespaces] FROM sys.xml_schema_wildcards
Sql 2008 R2
SELECT [xml_component_id], [xml_collection_id], [xml_namespace_id], [is_qualified], [name], [symbol_space], [symbol_space_desc], [kind], [kind_desc], [derivation], [derivation_desc], [base_xml_component_id], [scoping_xml_component_id], [process_content], [process_content_desc], [disallow_namespaces] FROM sys.xml_schema_wildcards
Sql 2012
SELECT [xml_component_id], [xml_collection_id], [xml_namespace_id], [is_qualified], [name], [symbol_space], [symbol_space_desc], [kind], [kind_desc], [derivation], [derivation_desc], [base_xml_component_id], [scoping_xml_component_id], [process_content], [process_content_desc], [disallow_namespaces] FROM sys.xml_schema_wildcards

Back to Top



Total Pageviews