Re: schema validation
- From: Kent Tegels <ktegels@xxxxxxxxxxx>
- Date: Wed, 19 Oct 2005 21:38:12 -0700
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(SchemaCollectionName). 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/
.
- Follow-Ups:
- Re: schema validation
- From: Darren
- Re: schema validation
- References:
- schema validation
- From: Darren
- schema validation
- Prev by Date: schema validation
- Next by Date: Importing Reporting Services RDL files into database
- Previous by thread: schema validation
- Next by thread: Re: schema validation
- Index(es):
Relevant Pages
|
