Is there a way to validate XML using variable as schema? Is there a way to declare XSD schema as variable inside a construct: DECLARE @xml XML(Books)?

I need to make quick validation of XML using XSD schema which is declared as variable. I suppose there is no way, but I have to ask.

If someone can propose some workaround it will be very helpful also.
I don't think this is possible using T-SQL, however you can achieve what you need using C# and .NET CLR assembly.

Steps


Create a new C# class library project in Visual Studio.
Write a public static method that will do the work for you.
Add CLR assembly to Microsoft SQL Server.
Create a scalar function that will call your method.
Call your method from T-SQL, just like any other command.


Public static method to validate an XML document against an XSD

using System;
using System.IO;
using System.Xml;
using System.Xml.Xsl;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
using System.Xml.Schema;

namespace SqlHelperClr
{
    public static class Xml
    {
        [SqlFunction]
        public static SqlString XmlValidate(SqlXml InputXml, SqlXml ValidationXsd)
        {
            if (InputXml.IsNull)
                throw new ArgumentNullException("InputXml", "InputXml is NULL.");
            if (ValidationXsd.IsNull)
                throw new ArgumentNullException("ValidationXsd", "ValidationXsd is NULL.");

            using (XmlReader xmlSchemaReader = ValidationXsd.CreateReader())
            {
                XmlReaderSettings settings = new XmlReaderSettings();

                settings.Schemas.Add("http://schemanamespace.example.com/", xmlSchemaReader);
                settings.ValidationType = ValidationType.Schema;

                using (XmlReader xmlReader = XmlReader.Create(InputXml.CreateReader(), settings))
                {
                    XmlDocument doc = new XmlDocument();
                    doc.Load(xmlReader);

                    ValidationEventArgs capturedEventArgs = null;

                    // Declare anonymous validation event handler to save the validation event arguments to a 'captured variable'
                    ValidationEventHandler eventHandler = new ValidationEventHandler(
                        (obj, e) =&gt;
                        {
                            capturedEventArgs = e;
                        });

                    try
                    {
                        doc.Validate(eventHandler);
                    }
                    catch (Exception ex)
                    {
                        return String.Format("ERROR; Severity {0}: {1}", capturedEventArgs.Severity.ToString(), capturedEventArgs.Message);
                    }

                    return "OK";
                }
            }
        }
    }
}


Add CLR assembly to Microsoft SQL Server

Build the assembly, then copy the output DLL file to a location accessible by the SQL Server instance. Then run this command to register a CLR assembly in a database:

USE [Mydatabase]
CREATE ASSEMBLY [SqlHelperClr]
FROM 'C:\SqlAssemblies\SqlHelperClr.dll'
WITH PERMISSION_SET = UNSAFE;


Create a scalar function that will call XmlValidate

USE [Mydatabase]
CREATE FUNCTION dbo.fn_ValidateXsd (@InputXml XML, @ValidationXsd XML)
RETURNS NVARCHAR(1000)
AS EXTERNAL NAME [SqlHelperClr].[SqlHelperClr.Xml].[XmlValidate];
-- [AssemblyName].[NamespaceNames.StaticClassName].[MethodName]


Use fn_ValidateXsd

DECLARE @XmlDoc Xml = '&lt;Root&gt;&lt;NodeA&gt;Blah&lt;/NodeA&gt;&lt;/Root&gt;'
DECLARE @XmlSchema Xml = '' -- Schema goes here

DECLARE @Validation_Message NVARCHAR(1000)
SET @Validation_Message = dbo.fn_ValidateXsd(@XmlDoc, @XmlSchema)