XSD Validation

  • Hi

    We have created an SP that accepts an XML parameter that is validated using an XSD.

    We create a transaction, delete the old data and then load in the new data then commit. A minor irritant, it doesn't actually do the validation until we come to insert the data if the XML is not valid it then has to rollback the transaction.

    Does anyone know of a way to validate the XML being passed at the start of the proc so we can just abort there if necessary?

    Thanks.

  • S t e f (6/6/2011)


    Hi

    We have created an SP that accepts an XML parameter that is validated using an XSD.

    Are you sure you bound the schema to the proc parameter for validation correctly? The proc will not accept the data at all if it does not pass schema validation similar to how a proc would generate an error if passed the letter a into a parameter of type INT.

    Can you post the proc code, the XML schema definition and the actual XML you're trying?

    Here is a proof of concept to demonstrate:

    USE tempdb

    GO

    IF EXISTS ( SELECT *

    FROM sys.objects

    WHERE object_id = OBJECT_ID(N'dbo.insert_some_things')

    AND type IN (N'P', N'PC') )

    DROP PROCEDURE dbo.insert_some_things ;

    GO

    IF EXISTS ( SELECT *

    FROM sys.xml_schema_collections c,

    sys.schemas s

    WHERE c.schema_id = s.schema_id

    AND (QUOTENAME(s.name) + '.' + QUOTENAME(c.name)) = N'[dbo].[my_schema]' )

    DROP XML SCHEMA COLLECTION [dbo].[my_schema]

    GO

    CREATE XML SCHEMA COLLECTION my_schema AS

    '<xs:schema attributeFormDefault="unqualified" elementFormDefault="qualified" xmlns:xs="http://www.w3.org/2001/XMLSchema">

    <xs:element name="item">

    <xs:complexType>

    <xs:attribute name="name" type="xs:string" use="required" />

    <xs:attribute name="value" type="xs:string" use="required" />

    </xs:complexType>

    </xs:element>

    </xs:schema>'

    GO

    CREATE PROC dbo.insert_some_things

    (

    @things XML(DOCUMENT my_schema)

    )

    AS

    BEGIN

    SELECT @things

    END

    GO

    EXEC dbo.insert_some_things

    @things = NULL ;

    GO

    EXEC dbo.insert_some_things

    @things = N'<item name="some_thing" value="some_value"/>' ;

    GO

    EXEC dbo.insert_some_things

    @things = N'<item wrong_name="some_thing" wrong_value="some_value"/>' ;

    GO

    There are no special teachers of virtue, because virtue is taught by the whole community.
    --Plato

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply