Validate XML File with XSD Schema in SQL Server

Introduction

If we have XML datatype column in SQL table and we need to validate that XML column data with valid XSD file, then we don't need to write .NET code or create a Windows application. We call database for XML data, then we pass that XML data to XSD and we identify the given XML is valid with XSD or not. But now in this article, we manage all the sections within SQL Server using some easy steps.

Background

If we have XML datatype column in SQL table, then we need to validate that XML coulmn data with valid XSD file. We don't need to write .NET code or create a Windows application, then we call database for XML data. Then, we pass that XML data to XSD, then we identify the given XML is valid with XSD or not. But now in this article, we manage all sections within SQL Server using some easy steps.

Using the Code

Step 1

You need to create database as 'XMLValidateWithXSD' in SQL Server 2008 r2.

Step 2

Then add one table within this database as 'tblXMLDataInformation' and add three columns as 'Id' as int type, 'XMLData' as XML type and 'LoadedDateTime' as datetime.

USE [XMLValidateWithXSD]
GO

/****** Object: Table [dbo].[XMLValidateWithXSD]  Script Date: 03/23/2016 13:43:03 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[tblXMLDataInformation](
   [Id] [int] IDENTITY(1,1) NOT NULL,
   [XMLData] [xml] NULL,
   [LoadedDateTime] [datetime] NULL,
PRIMARY KEY CLUSTERED 
(
   [Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, _
ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

Then add two entries in this table as:

Row 1

Id: 1

XMLData

<Student>
    <Name>Rahul</Name>
    <MName>Suresh</MName>
    <SurName>Chavhan</SurName>
</Student>

LoadedDateTime: todaysdate

Row 2

Id: 2

XMLData

<Student>
    <Name>Madhuri</Name>
    <MName>Patil</MName>
</Student>

LoadedDateTime: todaysdate

Then, you can see in two data entries in 'tblXMLDataInformation' table.

Step 3

Add another table as 'XMLTranformFailedEntry' within this database for maintain failed XSD validation with table XML entries.

USE [XMLValidateWithXSD]
GO

/****** Object: Table [dbo].[XMLTranformFailedEntry]  Script Date: 03/23/2016 13:49:10 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[XMLTranformFailedEntry](
   [Id] [int] NULL,
   [Reason] [nvarchar](50) NULL
) ON [PRIMARY]

GO

After firing the above query, you see 'XMLTranformFailedEntry' table created with in 'XMLValidateWithXSD' database.

Step 4

Now, you need to create XSD schema for validate 'tblXMLDataInformation' table xmldata with the given XSD schema. Fire the below query for this database.

CREATE XML SCHEMA COLLECTION StudentSchema
    AS'<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema">
    <xsd:element >
        <xsd:complexType>
            <xsd:sequence>
                <xsd:element />
                <xsd:element />
                <xsd:element />
            </xsd:sequence>
        </xsd:complexType>
    </xsd:element>
</xsd:schema>'

After the above query successful execution, then you can see 'EmployeeSchema' schema file created under XMLValidateWithXSD -> Programmability -> Types -> XML Schema Collections -> dbo.EmployeeSchema.

Step 5

Now we go to create final result to validate 'tblXMLDataInformation' table xmldata entries with 'EmployeeSchema' XSD schema by writing Cursor.

The below query logic as first it will select 'tblXMLDataInformation' table 'XMLData' column values through selected query, then we pass each XML data record to 'EmployeeSchema' XSD schema file. If we get XML validation failed with XSD file, then we make that unique Id into 'XMLTranformFailedEntry' table.

Suppose you have 1 to 10 entries in 'tblXMLDataInformation' table and 5 no row present invalid XML, then after firing the below query you will get 5 no id entry to 'XMLTranformFailedEntry' means you easily identify which XML is failed for XSD validation.

DECLARE @ID AS int
DECLARE @XML AS XML
DECLARE @string NVARCHAR(MAX);
Declare c Cursor For Select Distinct Id From tblXMLDataInformation
Open c
--Fetch next From c into @Id
While @@Fetch_Status=0 Begin 
  Fetch next From c into @Id
  SELECT @XML=XMLData FROM tblXMLDataInformation where Id=@Id   
   BEGIN TRY
     DECLARE @x XML(StudentSchema)
     SET @x = @XML    
   END TRY
   BEGIN CATCH
     INSERT INTO XMLXMLTranformFailedEntry (Id,Reason)
     VALUES (@Id,'XML validation failed with 'StudentSchema' XSD file ');  
   END CATCH
End
Close c
Deallocate c

After firing the above query, you will get your expected result which means validate multiple XML data entries with XSD file. By using the above steps, you don't need to write complex code in C# or VB.NET, etc. It's easy way to validate XML files with XSD in SQL Server.