xml file validation with xsd RSS

• • Reply
  

  Csharp22

  Member

  296 Points

  410 Posts

  Re: xml file validation with xsd

  Sep 25, 2008 10:33 PM|LINK

  hi,

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

   

• • Reply
  

  mfcorral

  Member

  240 Points

  50 Posts

  Re: xml file validation with xsd

  Sep 25, 2008 10:53 PM|LINK

  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.

• • Reply
  

  Csharp22

  Member

  296 Points

  410 Posts

  Re: xml file validation with xsd

  Sep 25, 2008 10:56 PM|LINK

  build succeeded!!

   

  [:)]

   

   

• • Reply
  

  mfcorral

  Member

  240 Points

  50 Posts

  Re: xml file validation with xsd

  Sep 25, 2008 11:03 PM|LINK

  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.

• • Reply
  

  Csharp22

  Member

  296 Points

  410 Posts

  Re: xml file validation with xsd

  Sep 25, 2008 11:09 PM|LINK

  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

• • Reply
  

  mfcorral

  Member

  240 Points

  50 Posts

  Re: xml file validation with xsd

  Sep 25, 2008 11:21 PM|LINK

  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

• • Reply
  

  mfcorral

  Member

  240 Points

  50 Posts

  Re: xml file validation with xsd

  Sep 25, 2008 11:32 PM|LINK

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

  Bye,

  Miguel.

• • Reply
  

  Csharp22

  Member

  296 Points

  410 Posts

  Re: xml file validation with xsd

  Sep 26, 2008 04:41 PM|LINK

  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

• • Reply
  

  Csharp22

  Member

  296 Points

  410 Posts

  Re: xml file validation with xsd

  Sep 26, 2008 04:58 PM|LINK

  when i have enabled Trustworty property it went through

   

   

• • Reply
  

  mfcorral

  Member

  240 Points

  50 Posts

  Re: xml file validation with xsd

  Sep 26, 2008 08:12 PM|LINK

  Hi, is the solution working?

  Bye, Miguel.