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