XML Schema Definition Part 7


Now we are going to simplify our example and use SQL Server. We don’t want to look at the complexity of all of the different types. Our goal here is to be able to validate XML inside of SQL Server. For example, we may have an XML document that is stored in a table. We want to validate it against our XSD. We can simply copy the XML document that is in our XML column in our table to another column that is “typed”. When we define the data type for our “typed” column in our table, we specify the type to be XML but we also need to specify the schema. To specify the schema, we must first create a schema.

In SQL Server

This part is really a continuation of XML Schema Definition Part 3 with some changes. We are branching out from our series.

Below is a sql script that you can run in your non-production or test server.

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[xmlTestTable]') 
AND TYPE IN (N'U'))
DROP TABLE [dbo].[xmlTestTable]
GO
-- You need to drop the table before the schema because otherwise you will get this error:
-- Msg 6328, Level 16, State 1, Line 9
-- Specified collection 'test' cannot be dropped because it is used by object 'dbo.xmlTestTable'.
IF EXISTS (SELECT * FROM sys.xml_schema_collections
WHERE [name] = 'test')
DROP XML SCHEMA COLLECTION test
GO
-- 
DECLARE @testSchema XML;
SET @testSchema = N'<xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema" 
  targetNamespace="http://porterwebsites.com/test" 
  xmlns:ns="http://porterwebsites.com/test"
 elementFormDefault="unqualified">
 <xs:element name="testXML" type="ns:testXML"/>
  <xs:complexType name="testXML">
   <xs:sequence>
    <xs:element name="element1" minOccurs="0" maxOccurs="unbounded" type="ns:elem1" />
    <xs:element name="element2" minOccurs="0" maxOccurs="unbounded"/>
   </xs:sequence>
  </xs:complexType>
  <xs:simpleType name="elem1">
		<xs:restriction base="xs:string">
			<xs:maxLength value="11" />
		</xs:restriction>
	</xs:simpleType>
</xs:schema>';
CREATE XML SCHEMA COLLECTION test AS @testSchema
GO
CREATE TABLE [dbo].[xmlTestTable](
 [XMLColumn] [XML](CONTENT [dbo].[test]) NULL
) ON [PRIMARY]
GO
INSERT INTO dbo.xmlTestTable
VALUES ('<ns:testXML  xmlns:ns="http://porterwebsites.com/test" 
 xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" 
 xsi:schemaLocation="http://porterwebsites.com/test test.xsd">
<element1>Is 11 chars</element1>
<element2>Some Text 2</element2>
</ns:testXML>')
GO
SELECT [XMLColumn] FROM [dbo].[xmlTestTable]
GO

If the above worked, meaning that there we no errors, we can soon move on to test the validation. Before doing that, in SSMS click the link provided at the bottom in the results pane after executing the above code. You should see your XML document. It should like the following.

<ns:testXML xmlns:ns="http://porterwebsites.com/test" 
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" 
xsi:schemaLocation="http://porterwebsites.com/test test.xsd">
  <element1>Is 11 chars</element1>
  <element2>Some Text 2</element2>
</ns:testXML>

Testing the Validation

Now we want to force an error by entering invalid data into our XML document. We have specified that the tag element1 can have at most 11 characters. We did this in the schema by creating a simple type for that element and specifying a maximum length of 11 characters.

Now enter in a longer string of characters for element1. Change “Is 11 chars” to something like “Is 11 chars plus more”. Re-run the script by clicking the “! Execute” button in SSMS. You should get the following error. That is what you want. You want to be able to add more to your schema file to validate even more data in you XML document. You want errors to appear when the data is incorrect.

Msg 6926, Level 16, State 1, Line 37
XML Validation: Invalid simple type value: 'Is 11 chars plus more'. Location: /*:testXML[1]/*:element1[1]
Series Navigation<< XML Schema Definition Part 6

Leave a comment

Your email address will not be published. Required fields are marked *