The use case for XML Schema collection type in SQL Server

XML Schema defines the structure of XML documents for storing XML in the SQL Server.
You can define the structure of the XML document for storing in the database so that format remains the same for the column.
For that, we need to define the schema for the XML and then use it for column value.

We create a schema collection in SQL Server first to define the XML Document structure for the SQL Server table column.

Create a schema collection XML in SQL Server:

CREATE XML SCHEMA COLLECTION Test_XML_Schema
AS  
'<?xml version="1.0" encoding="UTF-8" ?>
<xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema">
		<xs:element name="Employees">
	<xs:complexType>
	<xs:sequence>
		<xs:element name="FirstName" minOccurs="1" type="xs:string"/>
		<xs:element name="LastName" minOccurs="0" type="xs:string"/>
	</xs:sequence>
	</xs:complexType>
       </xs:element> 
</xs:schema> '
  

This command create the schema collection which having two columns firstname and lastname.

2. Create a table that will use the XML Schema “TEST_XML_SCHEMA” to make the structure use of an XML file so that no one can mislead or insert any other value in that XML type column.

Example: Create a table with a structured or unstructured XML-type column

create table test_xml_table ( unstructured xml, sturctured xml(CONTENT [dbo].[Test_XML_Schema]) ); 

Insert data into the column which is unstructured and structured column

  INSERT INTO
dbo.test_xml_table (unstructured , sturctured )
VALUES(
'<Employees>
	<FirstName>RAKESH</FirstName>
	<LastName>KUMAR</LastName>
</Employees>',
'<Employees>
	<FirstName>RAKESH</FirstName>
	<LastName>KUMAR</LastName>
</Employees>');

(1 row affected)

Try to insert the row with add one more column in the XML file give an error due to structured is defined for structured column in table:

Msg 6923, Level 16, State 1, Line 105 XML Validation: Unexpected element(s): Salary. Location: /*:Employees[1]/*:Salary[1]

  INSERT INTO
dbo.test_xml_table (unstructured , sturctured )
VALUES(
'<Employees>
	<FirstName>RAKESH</FirstName>
	<LastName>KUMAR</LastName>
	<Salary>10000</Salary>
</Employees>',
'<Employees>
	<FirstName>RAKESH</FirstName>
	<LastName>KUMAR</LastName>
	<Salary>10000</Salary>
</Employees>');

Msg 6923, Level 16, State 1, Line 105
XML Validation: Unexpected element(s): Salary. Location: /*:Employees[1]/*:Salary[1]