使用 XSD 架构验证 SQL Server 中的 XML 文件

引言

如果我们在 SQL 表中有一个 XML 数据类型列，并且需要使用有效的 XSD 文件验证该 XML 列数据，那么我们不需要编写 .NET 代码或创建 Windows 应用程序。我们可以从数据库调用 XML 数据，然后将 XML 数据传递给 XSD，并确定给定的 XML 是否对 XSD 有效。但现在在本文中，我们使用一些简单的步骤在 SQL Server 中管理所有部分。

背景

如果我们在 SQL 表中有一个 XML 数据类型列，那么我们需要使用有效的 XSD 文件验证该 XML 列数据。我们不需要编写 .NET 代码或创建 Windows 应用程序，然后我们从数据库调用 XML 数据。然后，我们将 XML 数据传递给 XSD，然后我们确定给定的 XML 是否对 XSD 有效。但现在在本文中，我们使用一些简单的步骤在 SQL Server 中管理所有部分。

Using the Code

步骤 1

您需要在 SQL Server 2008 r2 中创建一个名为“XMLValidateWithXSD”的数据库。

第二步

然后在此数据库中添加一个名为“tblXMLDataInformation”的表，并添加三列，分别是 int 类型的“Id”、XML 类型的“XMLData”和 datetime 类型的“LoadedDateTime”。

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

然后在此表中添加两个条目，如下所示：

第 1 行

Id: 1

XMLData

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

LoadedDateTime: todaysdate (今天的日期)

第 2 行

Id: 2

XMLData

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

LoadedDateTime: todaysdate (今天的日期)

然后，您可以在“tblXMLDataInformation”表中看到两个数据条目。

步骤 3

在此数据库中添加另一个名为“XMLTranformFailedEntry”的表，用于维护表 XML 条目中失败的 XSD 验证。

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

执行上述查询后，您会看到在“XMLValidateWithXSD”数据库中创建了“XMLTranformFailedEntry”表。

步骤 4

现在，您需要创建 XSD 模式，以使用给定的 XSD 模式验证“tblXMLDataInformation”表 xmldata。为此数据库执行以下查询。

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>'

成功执行上述查询后，您可以在 XMLValidateWithXSD -> 可编程性 -> 类型 -> XML 架构集合 -> dbo.EmployeeSchema 下看到创建的“EmployeeSchema”架构文件。

步骤 5

现在，我们将创建最终结果，通过编写 Cursor 来验证“tblXMLDataInformation”表 xmldata 条目和“EmployeeSchema”XSD 模式。

以下查询逻辑是：首先，它将通过选定的查询选择“tblXMLDataInformation”表的“XMLData”列值，然后我们将每个 XML 数据记录传递给“EmployeeSchema”XSD 模式文件。如果我们发现 XML 验证与 XSD 文件失败，那么我们将该唯一 Id 放入“XMLTranformFailedEntry”表中。

假设您在“tblXMLDataInformation”表中有 1 到 10 个条目，并且第 5 行存在无效的 XML，那么在执行以下查询后，您将在“XMLTranformFailedEntry”表中获得 5 个 Id 条目，这意味着您可以轻松识别哪个 XML 的 XSD 验证失败。

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

执行上述查询后，您将获得预期的结果，这意味着使用 XSD 文件验证多个 XML 数据条目。通过使用上述步骤，您无需在 C# 或 VB.NET 等中编写复杂的代码。这是在 SQL Server 中使用 XSD 验证 XML 文件的简便方法。