MS SQL XML - schema validation

Author

schema validation

Darren

2005-10-27, 9:26 am

I'd like to be able to validate inserts and updates on an xml column based on a schema in another table.

Any inserts into TableData need to be validated based on a schema in TableSchema

CREATE TABLE [dbo].[TableSchema]
(
[schema_id] [nchar](16) NULL,
[schema] [xml] NULL
)

CREATE TABLE [dbo].[TableData]
(
[data_id] [nchar](16) NOT NULL,
[schema_id] [nchar](16) NULL,
[data] [xml] NULL
)

Can this be done with a TSQL insert trigger or will I need to resort to .Net?

TIA

Kent Tegels

2005-10-27, 9:26 am

Hello Darren,

> I'd like to be able to validate inserts and updates on an xml column
> based on a schema in another table.
> Any inserts into TableData need to be validated based on a schema in
> TableSchema.
> Can this be done with a TSQL insert trigger or will I need to resort
> to .Net?

Short Answer: No. The XML data type does allow for schema binding, but not
to a row-member. It requires the use of an object known as Schema Collection.
You can do this via trigger as noted and there is an article in my blog that
talks about doing just that.

Long answer: If you're using namespaced XML, you really don't need to worry
about this much. Just put all of the appropriate schemas into an XML schema
collection and declare TableData.Data as XML(SchemaCollection
Name). SQL Server
will then validate the XML based on schema elements that apply for the indicated
namespace.

If you're not using namespaced XML, then you still might be able to get by
with this provided there's no collisions in node names in the stored instances.
For example:

use scratch
go
drop xml schema collection dbo.NoNameSpaces
go
create xml schema collection dbo.NoNameSpaces as
'<xs:schema attributeFormDefault
="unqualified" elementFormDefault="qualified"
xmlns:xs="http://www.w3.org/2001/XMLSchema">
<xs:element name="foo" type="xs:string" />
</xs:schema>
<xs:schema attributeFormDefault
="unqualified" elementFormDefault="qualified"
xmlns:xs="http://www.w3.org/2001/XMLSchema">
<xs:element name="bar">
<xs:complexType>
<xs:sequence>
<xs:element name="type" type="xs:string" />
<xs:element name="name" type="xs:string" />
<xs:element name="rating" type="xs:string" />
</xs:sequence>
</xs:complexType>
</xs:element>
</xs:schema>'
go
declare @x xml(dbo.NoNameSpaces)
set @x='<?xml version="1.0"?><foo>I pitty the foo who doesn''t like XML.</foo>'
select @x
set @x='<?xml version="1.0"?>
<bar><type>Candy</type><name>Snackers</name><rating>4 stars</rating></bar>'
select @x
set @x='<?xml version="1.0"?>
<bar><type>Candy</type><name>Snackers</name><rating>4 stars</rating></bar><foo>I
pitty the foo who doesn''t like XML.</foo>'
select @x
go

That last one -- that could be a bugger. Somewhat easily addressed though:

declare @x xml(document dbo.NoNameSpaces)
set @x='<?xml version="1.0"?><foo>I pitty the foo who doesn''t like XML.</foo>'
select @x
set @x='<?xml version="1.0"?>
<bar><type>Candy</type><name>Snackers</name><rating>4 stars</rating></bar>'
select @x
set @x='<?xml version="1.0"?>
<bar><type>Candy</type><name>Snackers</name><rating>4 stars</rating></bar><foo>I
pitty the foo who doesn''t like XML.</foo>'

Note that now the mixed example errors. That's probably what you had in mind.

Namespacing is your friend.

Thanks!

Kent Tegels
DevelopMentor
Blogging @ http://staff.develop.com/ktegels/

Darren

2005-10-27, 9:26 am

Thanks a bunch. BTW I was trying to deals with unique constraints

Kent Tegels wrote:
> Hello Darren,
>
>
>
> Short Answer: No. The XML data type does allow for schema binding, but
> not to a row-member. It requires the use of an object known as Schema
> Collection. You can do this via trigger as noted and there is an article
> in my blog that talks about doing just that.
>
> Long answer: If you're using namespaced XML, you really don't need to
> worry about this much. Just put all of the appropriate schemas into an
> XML schema collection and declare TableData.Data as
> XML(SchemaCollection
Name). SQL Server will then validate the XML based
> on schema elements that apply for the indicated namespace.
>
> If you're not using namespaced XML, then you still might be able to get
> by with this provided there's no collisions in node names in the stored
> instances. For example:
>
> use scratch
> go
> drop xml schema collection dbo.NoNameSpaces
> go
> create xml schema collection dbo.NoNameSpaces as
> '<xs:schema attributeFormDefault
="unqualified"
> elementFormDefault="qualified" xmlns:xs="http://www.w3.org/2001/XMLSchema">
> <xs:element name="foo" type="xs:string" />
> </xs:schema>
> <xs:schema attributeFormDefault
="unqualified"
> elementFormDefault="qualified" xmlns:xs="http://www.w3.org/2001/XMLSchema">
> <xs:element name="bar">
> <xs:complexType>
> <xs:sequence>
> <xs:element name="type" type="xs:string" />
> <xs:element name="name" type="xs:string" />
> <xs:element name="rating" type="xs:string" />
> </xs:sequence>
> </xs:complexType>
> </xs:element>
> </xs:schema>'
> go
> declare @x xml(dbo.NoNameSpaces)
> set @x='<?xml version="1.0"?><foo>I pitty the foo who doesn''t like
> XML.</foo>'
> select @x
> set @x='<?xml version="1.0"?>
> <bar><type>Candy</type><name>Snackers</name><rating>4 stars</rating></bar>'
> select @x
> set @x='<?xml version="1.0"?>
> <bar><type>Candy</type><name>Snackers</name><rating>4
> stars</rating></bar><foo>I pitty the foo who doesn''t like XML.</foo>'
> select @x
> go
>
> That last one -- that could be a bugger. Somewhat easily addressed though:
>
> declare @x xml(document dbo.NoNameSpaces)
> set @x='<?xml version="1.0"?><foo>I pitty the foo who doesn''t like
> XML.</foo>'
> select @x
> set @x='<?xml version="1.0"?>
> <bar><type>Candy</type><name>Snackers</name><rating>4 stars</rating></bar>'
> select @x
> set @x='<?xml version="1.0"?>
> <bar><type>Candy</type><name>Snackers</name><rating>4
> stars</rating></bar><foo>I pitty the foo who doesn''t like XML.</foo>'
>
> Note that now the mixed example errors. That's probably what you had in
> mind.
>
> Namespacing is your friend.
>
> Thanks!
>
> Kent Tegels
> DevelopMentor
> Blogging @ http://staff.develop.com/ktegels/
>
>

Kent Tegels

2005-10-27, 9:26 am

Hello Darren,

> Thanks a bunch. BTW I was trying to deals with unique constraints

Uniques are a bugger. The only way I've found to do that is via a CLR call
in a trigger. That's in the blog.

Thanks!

Kent Tegels
DevelopMentor
Blogging @ http://staff.develop.com/ktegels/

Sponsored Links