XSD in SQL Server

XML validation using XSD in SQL Server:
  
  To acheive the validation process, we will be utilising the option of Regex pattern.

Here is the list of steps to be done in XSD implementation on the XML.

1. Check for the existing XML Schema collection -> Drop it.
2. Create XSD and bind it with XML Schema Collection
3. Create XML of type XML Schema Collection
4. If it binds, it indicates the XML is satisfying the condition specified by the XSD. 


Regex pattern, I've provided is "The username and password can contain any number of special characters and any number of integers but it should not contain the special character - and /  in it.

Let's move on to the querying part.




IF EXISTS(



SELECT * FROM sys.xml_schema_collections


WHERE name = 'StringPattern'


) BEGIN


DROP XML SCHEMA COLLECTION ValidationPattern


END


GO


-------------------------------------------------------------


create XML SCHEMA COLLECTION ValidationPattern AS '


' 
-------------------------------------------------------------





GO


DECLARE @x XML(StringPattern)


SET @x = 'AC78HJGVHJ35AC78HJGVHJ35'


SELECT @x
-- This will give the output
-----------------------------------------------
DECLARE @x XML(StringPattern) 


SET @x = 'AC78HJGVH/J35AC78HJGVHJ35'

SELECT @x 
------This won't give the output 
--------------------------------------------------


Cheers,
Venkatesan Prabu .J
Head, KaaShiv InfoTech