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
public static method that will do the work for you.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) =>
{
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 = '<Root><NodeA>Blah</NodeA></Root>'
DECLARE @XmlSchema Xml = '' -- Schema goes here
DECLARE @Validation_Message NVARCHAR(1000)
SET @Validation_Message = dbo.fn_ValidateXsd(@XmlDoc, @XmlSchema)
I would highly recommend starting with the code in the function that you already have for doing this from an Xml Schema Collection, as shown in this Question:
SQL 2012 CLR error: "The context transaction which was active before entering ..."?
You can create a second function that is nearly the same but takes in the XSD itself instead of the Schema Collection name, and then it can just do the validation in the same way but without needing to get it from the database. And, since this function is doing any data access to get the XSD, it can be marked as IsDeterministic=true which will allow it to participate in parallel plans (or, to say it another way, this will prevent the function from forcing a serial plan).
[SqlFunction(IsDeterministic=true)]
public static SqlBoolean ValidateXmlFromXsd(SqlXml Xsd, SqlString ValueString)
{
XmlDocument asset1 = new XmlDocument();
XmlSchema schema1 = new XmlSchema();
System.Text.StringBuilder o = new System.Text.StringBuilder();
try
{
if (Xsd.IsNull)
{
try
{
asset1.LoadXml(ValueString.Value);
asset1 = null;
return new SqlBoolean(true);
}
catch (Exception ex)
{
return new SqlBoolean(false);
}
}
else
{
using (XmlReader xmlReader = Xsd.CreateReader())
{
schema1 = XmlSchema.Read(xmlReader, ValidateSchema);
}
asset1.Schemas.Add(schema1);
XDocument oDoc = new XDocument();
oDoc = XDocument.Parse(ValueString.Value);
oDoc.Descendants().Where(e => string.IsNullOrEmpty(e.Value)).Remove();
asset1.LoadXml(oDoc.FirstNode.ToString());
oDoc = null;
asset1.Validate((o, e) =>
{
if (e.Severity == XmlSeverityType.Error)
{
o.AppendLine("Error: " + e.Message);
}
});
if (asset1.SchemaInfo.Validity == XmlSchemaValidity.Valid)
{
asset1 = null;
return new SqlBoolean(true);
}
else
{
asset1 = null;
return new SqlBoolean(false);
}
}
}
catch (Exception ex)
{
return new SqlBoolean(false);
}
finally
{
schema1 = null;
asset1 = null;
o = null;
}
}
This way you have a consistent approach to the valiation, it is only the source of the XSD that is different. And both this function and the function in your other Question (linked above) can be in the same SAFE Assembly. I can see no reason to use an UNSAFE Assembly to do this.
IsDeterministic=true, DataAccess=DataAccessKind.None and that could definitely help performance since it would allow the scalar function to participate in parallel plans :-)