Databases
StackOverflow2013
Postsdbo
POIs there a way to impose a limit on the XML-type of a typed XML column in SQL Server?
Body
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POIs there a way to impose a limit on the XML-type of a typed XML column in SQL Server?
    text
    copied!<p>I have two XML Schemas where one depends on the other, and I'd like to create a typed XML column in SQL Server where the type is defined by one of my schemas (actually I have hundreds of schemas but in principle it's the same). When I create the column it accepts all the types defined in my entire schema collection, and I'd really like to limit the type for one column to just one of the types.</p> <pre><code>&lt;schema targetnamespace="b"&gt; &lt;simpleType name="b"&gt; &lt;restriction base="string"&gt;&lt;pattern value="(0[1-9])"/&gt;&lt;/restriction&gt; &lt;/simpleType&gt; &lt;/schema&gt; &lt;schema targetnamespace="a" xmlns:b="b"&gt; &lt;element name="b" type="b:b" /&gt; &lt;complexType name="aType"&gt; &lt;sequence&gt; &lt;element ref="b"/&gt; &lt;/sequence&gt; &lt;/complexType&gt; &lt;element name="a" type="aType"/&gt; &lt;/schema&gt; </code></pre> <p>So first I create the schema collection with </p> <pre><code>CREATE XML SCHEMA COLLECTION Foo AS N'&lt;above schemas&gt;' </code></pre> <p>and then I create the column with </p> <pre><code>ALTER TABLE FooTable ADD fooCol [xml] (Document [Foo]) </code></pre> <p>Now I can insert values into the column of XML types "a" as well as "b", but I'd really like to limit the column type to just "a".</p> <p>Is there any way of achieving that, short of hand-writing customized schema collections for each different type?</p> <p><strong>Updated:</strong></p> <p>Regarding the check constraint suggested - according to <a href="http://blogs.msdn.com/b/denisruc/archive/2006/08/22/713342.aspx" rel="nofollow">http://blogs.msdn.com/b/denisruc/archive/2006/08/22/713342.aspx</a> the check constraint would then be something along the lines of</p> <pre><code>CREATE FUNCTION dbo.checkTopElmntIsTypeA(@x XML(MySchemaCollection)) RETURNS bit AS BEGIN RETURN ~(@x.exist('/a:a')) END go </code></pre> <p>-and the table definition to limit content of a column to XML type a</p> <pre><code>CREATE TABLE T(xmlCol XML(MySchemaCollection) CHECK (1 = dbo.checkTopElmntIsTypeA(xmlCol))) go </code></pre> <p>I guess that could do, even if it's not quite as nice. :-)</p>
 

Querying!

 
Guidance
An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload