Using XML Schemas

Published 08 November 10 07:50 PM | MartinBell
A few weeks ago someone asked how to load XML schemas from a file. I thought I’d previously blogged about this, but upon checking the only place I found it was in a presentations I gave when SQL 2005 was released… so I thought it was about time I posted something!

First lets get an example of an XML schema in use! If you look at the sys.columns system catalog you will see the xml_collection_id column, this is populated if the data type of the column is xml and it is associated with an XML schema collection.

Looking at the Adevntureworks2008 database and running the following query gives all the columns that are associated with XML schemas:

USE AdventureWorks2008 ;

SELECT AS [Schema], AS [Table], AS [Column], AS [XML Schema Collection]
FROM   sys.columns cJOIN   sys.tables t ON c.object_id = t.object_idJOIN   sys.schemas s ON s.schema_id = t.schema_idJOIN   sys.xml_schema_collections sc ON c.xml_collection_id =sc.xml_collection_id


There should be 6 rows returned one of which is:

Schema Table Column XML Schema Collection
Production ProductModel CatalogDescription ProductDescriptionSchemaCollection

If you scripted the create statement for the ProductModel table you’d get something like:

CREATE TABLE [Production].[ProductModel](
       [ProductModelID] [int] IDENTITY(1,1) NOT NULL,
       [Name] [dbo].[Name] NOT NULL,
       [CatalogDescription] [xml](CONTENT
.[ProductDescriptionSchemaCollection]) NULL,
       [Instructions] [xml](CONTENT [Production].[ManuInstructionsSchemaCollection]) NULL,
       [rowguid] [uniqueidentifier] ROWGUIDCOL  NOT NULL,
       [ModifiedDate] [datetime] NOT NULL,
       [ProductModelID] ASC

The line:

[CatalogDescription] [xml](CONTENT [Production].[ProductDescriptionSchemaCollection]) NULL,

Shows that ProductDescriptionSchemaCollection xml collection is associated with the column. The CONTENT keyword indicates that multiple XML fragments can be contained in the column. If only a single document can be held in the column use the DOCUMENT keyword. To show this XML schema you can use the function xml_schema_namespace:

SELECT xml_schema_namespace(N'Production',N'ProductDescriptionSchemaCollection') ;

If this is displayed in a grid in SSMS the XML will be displayed in a new window if you click the link in the output. This can subsequently be saved as a file (say C:\XML Demo\ProductDescriptionSchema.xsd )

SQL 2005 introduced the OPENROWSET function with the BULK parameter that allows you to open a file and place the contents into a column or variable. So we can load the XML schema into a variable in the same way and then use the variable in a CREATE XML SCHEMA statement e.g:

DECLARE @XmlSchema xml ;

SELECT @XmlSchema = BulkColumn FROM OPENROWSET ( BULK 'C:\XML Demo\ProductDescriptionSchema.xsd', SINGLE_BLOB ) AS b ;

CREATE XML SCHEMA COLLECTION dbo.MyProductDescriptionSchema AS @XmlSchema ;

The full script for the test tables are here.

You might expect that the Test_Production_Content table could just be populated with a statement like:

INSERT INTO [dbo].[Test_ProductModel_Content](  [CatalogDescription] )
SELECT [CatalogDescription]
FROM AdventureWorks2008.Production.ProductModel
WHERE [CatalogDescription] IS NOT NULL ;

But if you try this you get the error:

Msg 527, Level 16, State 2, Line 1
Implicit conversion between XML types constrained by different XML schema collections is not allowed. Use the CONVERT function to run this query.

If you do convert the column the insert statement will work e.g.

INSERT INTO [dbo].[Test_ProductModel_Content](  [CatalogDescription] )SELECT CONVERT(XML, [CatalogDescription] )FROM AdventureWorks2008.Production.ProductModelWHERE [CatalogDescription] IS NOT NULL ;

As there is only a single fragment in the Adventureworks2008 table I can use the same statement to populate the the Test_Production_Document table e.g.

INSERT INTO [dbo].[Test_ProductModel_Document](  [CatalogDescription] )
SELECT CONVERT(varchar(max), [CatalogDescription])
FROM AdventureWorks2008.Production.ProductModel
WHERE [CatalogDescription] IS NOT NULL ;

As the column in Test_Production_Content allows multiple XML fragments I can therefore add more then one XML fragments into the column with the following statement:

INSERT INTO [dbo].[Test_ProductModel_Content](  [CatalogDescription] )
SELECT CONVERT(varchar(max), [CatalogDescription]) + CONVERT(varchar(max), [CatalogDescription])
FROM AdventureWorks2008.Production.ProductModel
WHERE [CatalogDescription] IS NOT NULL ;

But when I try to do something similar this with the document table:

INSERT INTO [dbo].[Test_ProductModel_Document](  [CatalogDescription] )
SELECT CONVERT(varchar(max), [CatalogDescription]) + CONVERT(varchar(max), [CatalogDescription])
FROM AdventureWorks2008.Production.ProductModel
WHERE [CatalogDescription] IS NOT NULL ;

You get the following error:

Msg 6901, Level 16, State 1, Line 1
XML Validation: XML instance must be a document.

If you try to insert an invalid node you will get the error similar to the following:

Msg 6965, Level 16, State 1, Line 13
XML Validation: Invalid content. Expected element(s): '{}Manufacturer','{}Specifications','{}Picture','{}Features','{}Category'. Found: element 'InvalidNode' instead. Location: /*:ProductDescription[1]/*:InvalidNode[1].

This was generated with the following script:

DECLARE @InvalidNode xml, @xml xml ;
SET @InvalidNode = '<InvalidNode>This is not in the Schema!</InvalidNode>' ;

SET @xml = ( SELECT TOP 1 [CatalogDescription]
FROM AdventureWorks2008.Production.ProductModel
WHERE [CatalogDescription] IS NOT NULL ) ;

SET @xml.modify('
declare namespace p1="" ;
insert sql:variable("@InvalidNode") 
after (/p1:ProductDescription/p1:Summary)[1]'
) ;

SELECT @xml ;

INSERT INTO [dbo].[Test_ProductModel_Content](  [CatalogDescription] )
VALUES ( @xml ) ;

Hopefully I’ve shown some of the things you can do with XML schemas and this blog post will be useful when you need to use them.

Filed under: ,


No Comments

This Blog

SQL Blogs