时间戳

首页 > 解决方案 > 使用 XSD 架构检查 XML

sql - 使用 XSD 架构检查 XML

问题描述

我试图测试一个代码，试图验证一种 XML 格式是否与 XSD 模式一致，但我不能。也许我在某些方面错了：

/*
    truncate table [stg].[UvetTns_ISOS_XSDPNRList]
    truncate table [stg].[UvetTns_ISOS_XMLTranformFailedEntry]
    select * from [stg].[UvetTns_ISOS_XSDPNRList]
    select * from [stg].[UvetTns_ISOS_XMLTranformFailedEntry]
*/


if OBJECT_ID('[stg].[UvetTns_ISOS_XSDPNRList]','U') is not null drop table [stg].[UvetTns_ISOS_XSDPNRList]
CREATE TABLE [stg].[UvetTns_ISOS_XSDPNRList]
(
    [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

if OBJECT_ID('[stg].[UvetTns_ISOS_XMLTranformFailedEntry]','U') is not null drop table [stg].[UvetTns_ISOS_XMLTranformFailedEntry]
CREATE TABLE [stg].[UvetTns_ISOS_XMLTranformFailedEntry]
(
    [Id]            int NULL,
    [Reason]        nvarchar(255) NULL
) ON [PRIMARY]

insert into [stg].[UvetTns_ISOS_XSDPNRList]
(
    [XMLData], [LoadedDateTime]
)
select N'<?xml version="1.0" encoding="utf-16"?>
<Student>
    <Anagrafica Name="Jona" Surname="Wild"/>
    <Inidirzzo Via="Via Lacco" NCivico="10" Comune="Cincinella" Provincia="Milano"/>
    <Scuola Name="Vesuvio di Milano"/>
</Student>', GETDATE() union all
select N'<?xml version="1.0" encoding="utf-16"?>
<Student>
    <Anagrafica Name="Jona" Surname="Wild"/>
    <Inidirzzo Via="Via Lacco" NCivico="10" Comune="Cincinella" Provincia="Milano"/>
</Student>', GETDATE()

IF EXISTS (SELECT * FROM sys.xml_schema_collections 
                    WHERE name = N'StudentSchema' 
                    AND schema_id = SCHEMA_ID(N'dbo'))
   DROP XML SCHEMA COLLECTION dbo.StudentSchema;
CREATE XML SCHEMA COLLECTION dbo.StudentSchema
    AS N'<?xml version="1.0" encoding="utf-16"?>
    <xs:schema attributeFormDefault="unqualified" elementFormDefault="qualified" xmlns:xs="http://www.w3.org/2001/XMLSchema">
    <xs:element name="Student">
        <xs:complexType>
            <xs:sequence>
                <xs:element name="Anagrafica" minOccurs="1" maxOccurs="1">
                    <xs:complexType>
                        <xs:attribute name="Name" type="xs:unsignedShort" use="required" />
                        <xs:attribute name="Surname" type="xs:unsignedShort" use="required" />
                    </xs:complexType>
                </xs:element>
                <xs:element name="Inidirzzo" minOccurs="1" maxOccurs="1">
                    <xs:complexType>
                        <xs:attribute name="Via" type="xs:unsignedShort" use="required" />
                        <xs:attribute name="NCivico" type="xs:unsignedShort" use="required" />
                        <xs:attribute name="Comune" type="xs:unsignedShort" use="required" />
                        <xs:attribute name="Provincia" type="xs:unsignedShort" use="required" />
                    </xs:complexType>
                </xs:element>
                <xs:element name="Scuola" minOccurs="1" maxOccurs="1">
                    <xs:complexType>
                        <xs:attribute name="Name" type="xs:unsignedShort" use="required" />
                    </xs:complexType>
                </xs:element>
            </xs:sequence>
        </xs:complexType>
    </xs:element>
</xs:schema>'


DECLARE @ID AS int
DECLARE @XML AS XML
--
if (select cursor_status('global','#c')) >= -1
begin
    if (select cursor_status('global','#c')) > -1
    begin
        close #c
    end
    deallocate #c
end
--
Declare #c cursor fast_forward read_only for
Select Distinct 
    Id 
From 
    [stg].[UvetTns_ISOS_XSDPNRList]
Open #c
Fetch next From #c into @Id
While @@Fetch_Status = 0
Begin 
    SELECT 
        @XML=XMLData 
    FROM 
        [stg].[UvetTns_ISOS_XSDPNRList] 
    where 
        Id=@Id
   BEGIN TRY
    DECLARE @x XML(dbo.StudentSchema)
    select @xml
    select @x
    SET @x = @XML  
    print 'ok'
   END TRY
   BEGIN CATCH
    INSERT INTO [stg].[UvetTns_ISOS_XMLTranformFailedEntry] (Id,Reason)
    VALUES (@Id,'XML validation failed with ''StudentSchema'' XSD file');  
    print 'failed'
   END CATCH
   Fetch next From #c into @Id
End
Close #c
Deallocate #c

在这两种情况下，使用光标的检查都失败了，应该是一个 OK 而另一个失败。我想做一个正确的 XSD 以使其工作，我没有看到任何错误。

标签： sqlsql-serverxml

解决方案

我对您的实施进行了很多更改。

(1) 表变量而不是真实表 (2) 没有游标循环通过 DB 表 (3) 简化 XSD 验证 (4) 捕获真实 XSD 验证错误消息。它允许了解正在发生的事情。

XSD 验证在第一个错误时失败，然后停止。这就是为什么如果您有多个错误，您将需要多次重新运行 SQL。

实际的错误非常简单——几乎所有的属性都需要使用type="xs:string"而不是。type="xs:unsignedShort"我已经修复了姓名和姓氏属性。

SQL

USE tempdb;
GO

-- DDL and sample data population, start
IF EXISTS (SELECT * FROM sys.xml_schema_collections 
                    WHERE name = N'StudentSchema' 
                    AND schema_id = SCHEMA_ID(N'dbo'))
   DROP XML SCHEMA COLLECTION dbo.StudentSchema;

CREATE XML SCHEMA COLLECTION dbo.StudentSchema
    AS N'<?xml version="1.0" encoding="utf-16"?>
    <xs:schema attributeFormDefault="unqualified" elementFormDefault="qualified" xmlns:xs="http://www.w3.org/2001/XMLSchema">
    <xs:element name="Student">
        <xs:complexType>
            <xs:sequence>
                <xs:element name="Anagrafica" minOccurs="1" maxOccurs="1">
                    <xs:complexType>
                        <xs:attribute name="Name" type="xs:string" use="required" />
                        <xs:attribute name="Surname" type="xs:string" use="required" />
                    </xs:complexType>
                </xs:element>
                <xs:element name="Inidirzzo" minOccurs="1" maxOccurs="1">
                    <xs:complexType>
                        <xs:attribute name="Via" type="xs:unsignedShort" use="required" />
                        <xs:attribute name="NCivico" type="xs:unsignedShort" use="required" />
                        <xs:attribute name="Comune" type="xs:unsignedShort" use="required" />
                        <xs:attribute name="Provincia" type="xs:unsignedShort" use="required" />
                    </xs:complexType>
                </xs:element>
                <xs:element name="Scuola" minOccurs="1" maxOccurs="1">
                    <xs:complexType>
                        <xs:attribute name="Name" type="xs:unsignedShort" use="required" />
                    </xs:complexType>
                </xs:element>
            </xs:sequence>
        </xs:complexType>
    </xs:element>
</xs:schema>';
GO

DECLARE @UvetTns_ISOS_XSDPNRList TABLE (ID INT IDENTITY PRIMARY KEY, XMLData XML NULL, LoadedDateTime DATETIME NULL);
DECLARE @UvetTns_ISOS_XMLTranformFailedEntry TABLE (ID INT NOT NULL, Reason NVARCHAR(1024) NULL);

INSERT INTO @UvetTns_ISOS_XSDPNRList (XMLData, LoadedDateTime) VALUES
(N'<?xml version="1.0" encoding="utf-16"?>
<Student>
    <Anagrafica Name="Jona" Surname="Wild"/>
    <Inidirzzo Via="Via Lacco" NCivico="10" Comune="Cincinella" Provincia="Milano"/>
    <Scuola Name="Vesuvio di Milano"/>go
</Student>', GETDATE()),
(N'<?xml version="1.0" encoding="utf-16"?>
<Student>
    <Anagrafica Name="Jona" Surname="Wild"/>
    <Inidirzzo Via="Via Lacco" NCivico="10" Comune="Cincinella" Provincia="Milano"/>
</Student>', GETDATE());
-- DDL and sample data population, end

DECLARE @ID INT, @XML AS XML(dbo.StudentSchema)
   , @RowCount INT = (SELECT COUNT(*) FROM @UvetTns_ISOS_XSDPNRList);

WHILE @RowCount > 0
BEGIN
    BEGIN TRY
        SELECT @ID = ID, @XML = XMLData
        FROM @UvetTns_ISOS_XSDPNRList
        ORDER BY ID DESC
        OFFSET @RowCount - 1 ROWS FETCH NEXT 1 ROWS ONLY;

       --SELECT @ID AS ID, @XML AS [XML];
    END TRY
    BEGIN CATCH
        INSERT INTO @UvetTns_ISOS_XMLTranformFailedEntry (ID, Reason)
        VALUES (@ID, ERROR_MESSAGE());  
    END CATCH

   SET @RowCount -= 1;
END;

-- test
SELECT * FROM @UvetTns_ISOS_XMLTranformFailedEntry;

输出

+----+-------------------------------------------------------------------------------------------------------+
| ID |                                                Reason                                                 |
+----+-------------------------------------------------------------------------------------------------------+
|  1 | XML Validation: Invalid simple type value: 'Via Lacco'. Location: /*:Student[1]/*:Inidirzzo[1]/@*:Via |
|  2 | XML Validation: Invalid simple type value: 'Via Lacco'. Location: /*:Student[1]/*:Inidirzzo[1]/@*:Via |
+----+-------------------------------------------------------------------------------------------------------+

推荐阅读