xml file validation with xsd

hi,

i am inserting data from xml into sql 2005 table.

now before reading xml file i want to validate the xml file with the schema.

if validated i want to open and do my operation.

else i want to log the error.

any suggestions...

i am able to insert the xml data into database table(with openxml) but i need the validation part

any suggestion

Re: xml file validation with xsd

Hi Csharp22, you need to do something like this:

/// <summary>
    /// Validates a xml against a xml schema.
    /// </summary>
    public class XmlValidator
    {
        private bool _IsValid = true;


        /// <summary>
        /// Creates a new instance of XmlValidator.
        /// </summary>
        /// <param name="xml">Path to xml file.</param>
        /// <param name="xsd">Path to xml schema file.</param>
        public XmlValidator(string xml, string xsd)
        {
            System.Xml.XmlDocument xmlDoc = new System.Xml.XmlDocument();
            xmlDoc.Load(xml);


            using (System.IO.FileStream fsStr = new System.IO.FileStream(xsd, System.IO.FileMode.Open, System.IO.FileAccess.Read, System.IO.FileShare.Read))
            {
                xmlDoc.Schemas.Add(System.Xml.Schema.XmlSchema.Read(fsStr, this.Validator));
            }


            xmlDoc.Validate(this.Validator);
        }


        /// <summary>
        /// Indicates if the xml file has a valid schema.
        /// </summary>
        public bool IsValid
        {
            get
            {
                return this._IsValid;
            }
        }


        private void Validator(object sender, System.Xml.Schema.ValidationEventArgs e)
        {
            //Check severity (if you need Warnings be treated as erros change this)
            if (e.Severity == System.Xml.Schema.XmlSeverityType.Error)
            {
                this._IsValid = false;
            }
        }
    }

Bye,

Miguel.

Re: xml file validation with xsd

hi miguel,

i wanted to do it in a stored procedure..

Re: xml file validation with xsd

Where do you store the xml schema? In SQL Server? In a file?

What version of Sql Server do you use? You need at least 2005 to use .NET assemblies inside it.

Bye,

Miguel.

Re: xml file validation with xsd

hi,

i am using sql server 2005.

right now i have the schema in a file(.xsd) ....

do i need to register the schema with sql server 2005 and then do validation

any suggestion or sample example would be greatful

tnx

Re: xml file validation with xsd

Oks, the only way is to doing an assembly with visual studio 2005/2008 and embed in Sql Server 2005, then map the assembly procedures to stored procedures in order to use them inside a Sql Server Stored Procedure. Do you have any experience in Visual Studio (C# or Visual Basic.NET)?

Bye,

Miguel.

Re: xml file validation with xsd

i dint do this before....

it would be a great help if you let me know the steps for this.

thx

Re: xml file validation with xsd

Oks, you need to install a Visual Studio if you don't have yet. In http://www.microsoft.com/express/download/ you can download and install it. Once you have installed it you have to create a new Poject (Class Library) in C# language. When you do those steps please post a new message and I guide you step by step to do it.

Bye,

Miguel.

Re: xml file validation with xsd

hi,

i have installed and created a new project...

what next ?

tnx

Re: xml file validation with xsd

Oks, you are quick enough like Fernando Alonso [:P].

The next step is to add a new class to the project (right click in the solution explorer on the project name and add new file, class). Name it XmlValidator.cs.

How dou you named the project?

Re: xml file validation with xsd

hi,

i have given same name(XmlValidator) to both my project and class name

Re: xml file validation with xsd

Oks... overwrite all the code auto generated of your class and paste the following:

using System;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using System.Text;
using System.Xml;
using System.Xml.Schema;
using Microsoft.SqlServer.Server;


namespace XmlValidator
{
    public class XmlValidator
    {
        private bool IsValid = true;


        private XmlValidator(string xml, string xsd)
        {
            System.Xml.XmlDocument xmlDoc = new System.Xml.XmlDocument();
            xmlDoc.LoadXml(xml);


            using (System.IO.FileStream fsStr = new System.IO.FileStream(xsd, System.IO.FileMode.Open, System.IO.FileAccess.Read, System.IO.FileShare.Read))
            {
                xmlDoc.Schemas.Add(System.Xml.Schema.XmlSchema.Read(fsStr, this.Validator));
            }


            xmlDoc.Validate(this.Validator);
        }


        private void Validator(object sender, System.Xml.Schema.ValidationEventArgs e)
        {
            //Check severity (if you need Warnings be treated as erros change this)
            if (e.Severity == System.Xml.Schema.XmlSeverityType.Error)
            {
                this.IsValid = false;
            }
        }


        [SqlFunction]
        public static SqlBoolean Validate(SqlXml xml, SqlString pathToXsd)
        {
            try
            {
                return new SqlBoolean(new XmlValidator(xml.Value, pathToXsd.Value).IsValid);
            }
            catch
            {
            }


            return new SqlBoolean(false);
        }
    }
}

Then build the project.

Re: xml file validation with xsd

build succeeded!!

[:)]

Re: xml file validation with xsd

Well done!!!! [:)] iuuuuuuhu!! Oks the next step is to add the assembly to the Sql Server Database. Inside your database go to Programming->Assemblies and add the built assembly.

Re: xml file validation with xsd

friend i have added the assembly...

please let me know the other steps i will do it tomorror...its too late now time to go

thank you very much fro the information...

will test it tomm and let you know

plz let me know how to validate from stored procedure

Re: xml file validation with xsd

Oks... Run this script in your server:

USE YOUR_DATABASE
GO


EXEC sp_configure 'clr', 1
GO


RECONFIGURE WITH OVERRIDE
GO


ALTER DATABASE [YOUR_DATABASE] SET TRUSTWORTHY ON
GO


CREATE FUNCTION ValidateXml (@Xml Xml,
						     @PathToXsd NVARCHAR(255))
RETURNS BIT
AS EXTERNAL NAME XmlValidator.[XmlValidator.XmlValidator].Validate
GO

And to test it... run a new query in your database:

SELECT [dbo].[ValidateXml]('<xml></xml>', 'PATH_OF_THE_XSD')

If dont work we can test it tomorrow.

Bye,

Miguel

Re: xml file validation with xsd

Hi! I forgot tell you when you add the assembly to the sql server you must enable "External access" in the permissions.

Bye,

Miguel.

Re: xml file validation with xsd

hi,

when i try to enable 'External access' i get the following error

Create Assembly for assembly XmlValidator failed because assembly XmlValidator is not authorized for PERMISSION_SET = EXTERNAL_ACCESS. The assembly is authorized when either of the following is true;

The database owner(DBO) has EXTERNAL ACCESS ASSEMBLY permission and the database has the TRUSTWORTY database property on; or the assembly is signed with as certificate or an asymmetric key that has a corresponding login with EXTERNAL ACCESS ASSEMBLY permission.(Microsoft SQL Server , Error: 10327)

any suggestion

tnx

Re: xml file validation with xsd

when i have enabled Trustworty property it went through

Re: xml file validation with xsd

Hi, is the solution working?

Bye, Miguel.