Check XML with XSD schema

Question

I was trying to test a code trying to verify if one XML format is aligned with the XSD schema but I cannot. Maybe I'm wrong in some way:

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

    
    
    
', GETDATE() union all
select N'

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


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

In both cases the check with the cursor is giving me failed, should be one OK and the other failed. I would like to do a correct XSD in order to make it work, I don't see any error.

Yitzhak Khabinsky · Accepted Answer

I made lots of changes to your implementation.

(1) Table variables instead of real tables (2) no cursor to loop through the DB table (3) Simplified XSD validation (4) Capturing real XSD validation error message. It allows to understand what is going on.

The XSD validation fails on the very first error and stops after that. That's why if you have multiple errors, you wold need to re-run the SQL multiple times.

The actual error is very simple - you need to use type="xs:string" instead of the type="xs:unsignedShort"for almost all of the attributes. I already fixed the Name and Surname attributes.

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

    
    
    go
', GETDATE()),
(N'

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

Output

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

Check XML with XSD schema

Answers (1)

Related Questions