Creating XML Schema Collection

• The purpose of an XML Schema is to define the legal building blocks of an XML document:
  • The elements and attributes that can appear in a document.
  • The number of (and order of) child elements.
  • Data types for elements and attributes.
  • Default and fixed values for elements and attributes.

CREATE XML SCHEMA COLLECTION [MyInstructionsSchemaCollection] AS
N'<?xml version="1.0" encoding="UTF-16"?>
<xsd:schema 
	targetNamespace="<http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelManuInstructions>"
	xmlns="<http://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 * FROM sys.xml_schema_namespaces;

-- Use it. Create a typed XML variable. Note collection name specified.
DECLARE @MyXml XML ([MyInstructionsSchemaCollection]);
GO

-- Or create a typed XML column.
/*
CREATE TABLE [MyTable]
(
	[Id] INT PRIMARY KEY,
	[Data] XML (MyInstructionsSchemaCollection)
);
*/

-- Clean up
DROP XML SCHEMA COLLECTION [MyInstructionsSchemaCollection];

XML Data Type

• XML is a good choice if you want a platform-independent model in order to ensure portability of the data by using structural and semantic markup.
• It is an appropriate option if some of the following properties are satisfied:
  • Your data is sparse or you do not know the structure of the data, or the structure of your data may change significantly in the future.
  • Your data represents containment hierarchy, instead of references among entities, and may be recursive.
  • Order is inherent in your data.
  • You want to query into the data or update parts of it, based on its structure.
• SQL Server preserves the content of the XML instance, but does not preserve aspects of the XML instance that are not considered to be significant in the XML data model.
  • The XML declaration in an instance is not preserved.
  • The order of attributes in an XML instance is not preserved.
  • Single quotation marks and double quotations marks around attribute values are not preserved.
  • Namespace prefixes are not preserved.

Limitations of the XML Data Type

• The stored representation of xml data type instances cannot exceed 2 GB.
• It cannot be compared or sorted.
  • This means an xml data type cannot be used in a GROUP BY statement.
• It cannot be used as a parameter to any scalar, built-in functions other than ISNULL, COALESCE, and DATALENGTH.
• It cannot be used as a key column in an index.
  • However, it can be included as data in a clustered index or explicitly added to a non-clustered index by using the INCLUDE keyword when the nonclustered index is created.
• It cannot be used as a subtype of a sql_variant instance.
• It does not support casting or converting to either text or ntext.
  • Use varchar(max) or nvarchar(max) instead.

Select Data as XML

• By default, the XML parser discards insignificant white space when it converts string data to XML if either of the following is true:
  • The xml:space attribute is not defined on an element or its ancestor elements.
  • The xml:space attribute in effect on an element, or one of its ancestor elements, has the value of default.
• To preserve white space for an xml DT instance, use the CONVERT operator and its optional style parameter set to a value of 1.

SELECT CONVERT(XML, N'<root> <child/> </root>', 1)

• You can use the FOR XML clause in a SELECT statement to return results as XML.

DECLARE @XmlDoc XML = 
(
	SELECT [ProductModelID], [Name]
	FROM [Production].[ProductModel]
	FOR XML AUTO
)