Bind Schema with XML in sql Server

Following is the example of how to create schema, where it is store schema information which we've created,
and how to bind schema with xml column and sample stores proc that is would consume typed xml.

PS: When the XML data is associated with an XML schema collection, it is called typed XML; otherwise it is called untyped XML.


-- Step [1] -----------------------------------------------------------------------------------

CREATE XML SCHEMA COLLECTION DestinationSchemaCollection AS
'<xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema" elementFormDefault="qualified">
<xs:element name="CustomerRS">
<xs:complexType>
<xs:sequence>
<xs:element ref="Destinations"/>
</xs:sequence>
</xs:complexType>
</xs:element>
<xs:element name="Destinations">
<xs:complexType>
<xs:sequence>
<xs:element maxOccurs="unbounded" ref="Destination"/>
</xs:sequence>
</xs:complexType>
</xs:element>
<xs:element name="Destination">
<xs:complexType>
<xs:sequence>
<xs:element ref="Type" maxOccurs="1" minOccurs="0"/>
<xs:element ref="Code" maxOccurs="1" minOccurs="1"/>
<xs:element ref="Description" maxOccurs="1" minOccurs="1"/>
</xs:sequence>
</xs:complexType>
</xs:element>
<xs:element name="Type" type="xs:integer"/>
<xs:element name="Code" type="xs:integer"/>
<xs:element name="Description" type="xs:string"/>
</xs:schema>
'

-- Step [2] -----------------------------------------------------------------------------------

SELECT *
FROM sys.xml_schema_collections
WHERE name = 'DestinationSchemaCollection'

-- Step [3] -----------------------------------------------------------------------------------

CREATE TABLE dbo.Destinations
(
ID INT IDENTITY (1,1),
DestinationDescription XML ( DestinationSchemaCollection ) NOT NULL --Assign schema to xml column
)

-- Step [4] -----------------------------------------------------------------------------------
CREATE PROCEDURE dbo.InsertDestination
(
@DestinationDescription XML
)
AS
BEGIN

BEGIN TRY

BEGIN TRANSACTION InsertDestinations

INSERT INTO dbo.Destinations ( DestinationDescription )
SELECT @DestinationDescription

COMMIT TRANSACTION InsertDestinations

END TRY

BEGIN CATCH

ROLLBACK TRANSACTION InsertDestinations

DECLARE
@ErrorMessage NVARCHAR(4000),
@ErrorNumber INT,
@ErrorSeverity INT,
@ErrorState INT,
@ErrorLine INT,
@ErrorProcedure NVARCHAR(200);

SELECT
@ErrorNumber = Error_NUMBER(),
@ErrorSeverity = Error_SEVERITY(),
@ErrorState = Error_STATE(),
@ErrorLine = Error_LINE(),
@ErrorProcedure = Error_PROCEDURE(),
@ErrorMessage = Error_MESSAGE();

RAISError
(
@ErrorMessage,
@ErrorSeverity,
1,
@ErrorNumber,
@ErrorSeverity,
@ErrorState,
@ErrorProcedure,
@ErrorLine
);

END CATCH

END

-- Step [5] -----------------------------------------------------------------------------------

/* Below are the samples to execute above sp */

DECLARE @DestinationXML XML
SET @DestinationXML = '
<CustomerRS>
<Destinations>
<Destination>
<Type>3</Type>
<Code>97</Code>
<Description>Europe</Description>
</Destination>
<Destination>
<Type>4</Type>
<Code>98</Code>
<Description>Australia</Description>
</Destination>
</Destinations>
</CustomerRS>'
EXEC [InsertDestination] @DestinationXML

-- try [5.1] -----------------------------------------------------------------------------------

DECLARE @DestinationXML XML
SET @DestinationXML = '
<CustomerRS>
<Destinations>
<Destination>

<Code>97</Code>
<Description>Paris</Description>
</Destination>
<Destination>
<Type>4</Type>
<Code>98</Code>
<Description>India</Description>
</Destination>
</Destinations>
</CustomerRS>'
EXEC [InsertDestination] @DestinationXML

-- try [5.2] -----------------------------------------------------------------------------------

DECLARE @DestinationXML XML
SET @DestinationXML = '
<CustomerRS>
<Destinations>
<Destination>
<Type>Sample</Type>
<Code>97</Code>
<Description>USA</Description>
</Destination>
<Destination>
<Type>4</Type>
<Code>98</Code>
<Description>SA</Description>
</Destination>
</Destinations>
</CustomerRS>'
EXEC [InsertDestination] @DestinationXML

-- try [5.3] -----------------------------------------------------------------------------------

DECLARE @DestinationXML XML
SET @DestinationXML = '
<CustomerRS>
<Destinations>
<Destination>
<Type>3</Type>
<Description>Madrid</Description>
</Destination>
<Destination>
<Type>4</Type>
<Code>98</Code>
<Description>UK</Description>
</Destination>
</Destinations>
</CustomerRS>'
EXEC [InsertDestination] @DestinationXML