XML Schema collection in SQL Server

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]

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.