SQL Server Typed XML using XSD Part 2

 in SQL Server  tagged xml / xsd / schema by

Just as a database has a schema, XML has a schema. The XML schema defines the structure of an XML document.

We have the following XML file. Notice that it has a header, detail and trailer section. It is not quite as simple as some of the other XML files we have been working with. 

<?xml version="1.0" encoding="UTF-8"?>
<bookstore>
	<header>
		<store>I Love Books Inc.</store>
	</header>
		<books>
			<book>
				<category>cooking</category>
				<title>Everyday Italian</title>
				<author>Giada De Laurentiis</author>
				<yearpub>2005</yearpub>
				<price>30.00</price>
			</book>
			<book>
				<category>children</category>
				<title>Harry Potter</title>
				<author>J K. Rowling</author>
				<yearpub>2005</yearpub>
				<price>29.99</price>
			</book>
	    </books>
	<trailer>
		<numberofbooks>2</numberofbooks>
	</trailer>
</bookstore>

After going to the website FreeFormatter.com, we were able to generate an XSD from our XML file shown below. Many people are are using websites such as this one to generate their XSD files, however, it is better to generate your own. 

<xs:schema attributeFormDefault="unqualified" elementFormDefault="qualified" xmlns:xs="http://www.w3.org/2001/XMLSchema">
  <xs:element name="bookstore">
    <xs:complexType>
      <xs:sequence>
        <xs:element name="header">
          <xs:complexType>
            <xs:sequence>
              <xs:element type="xs:string" name="store"/>
            </xs:sequence>
          </xs:complexType>
        </xs:element>
        <xs:element name="books">
          <xs:complexType>
            <xs:sequence>
              <xs:element name="book" maxOccurs="unbounded" minOccurs="0">
                <xs:complexType>
                  <xs:sequence>
                    <xs:element type="xs:string" name="category"/>
                    <xs:element type="xs:string" name="title"/>
                    <xs:element type="xs:string" name="author"/>
                    <xs:element type="xs:short" name="yearpub"/>
                    <xs:element type="xs:float" name="price"/>
                  </xs:sequence>
                </xs:complexType>
              </xs:element>
            </xs:sequence>
          </xs:complexType>
        </xs:element>
        <xs:element name="trailer">
          <xs:complexType>
            <xs:sequence>
              <xs:element type="xs:byte" name="numberofbooks"/>
            </xs:sequence>
          </xs:complexType>
        </xs:element>
      </xs:sequence>
    </xs:complexType>
  </xs:element>
</xs:schema>

We need to look through the schema we got and decide if we need to make any changes. We could change the numberofbooks type from byte to integer. Also, the minimum occurrences of book could be 1, not 0. Also the yearpub could be string, even though it is a date. We won’t make these changes now.

Storing the Schema in SQL Server

In SQL Server we can store this schema in the database itself. Once stored, you will find it under Programmability, Types, XML Schema Collections. How do we store this schema in the database? Storing it is easy. All we need to do is run a query that starts with CREATE XML SCHEMA COLLECTION and gives it a name, followed by our schema. For example you would use code simlar to the following. 

CREATE XML SCHEMA COLLECTION MySchemaCollection AS  
'<?xml version="1.0" encoding="UTF-8"?>  
<schema xmlns="http://www.w3.org/2001/XMLSchema" targetNamespace="https://begincodingnow.com/1"
	xmlns:abc="https://begincodingnow.com/1" elementFormDefault="unqualified">
	<element name="Bookstore" type="abc:Bookstore" />
	<![CDATA[  some comments...     ]]>
	<complexType name="    ...
        <complexType name="    ...
              ...
	</complexType>
</schema>'

Let’s create a new table for our XML data. The screenshot is below. In the typed column, the data tye shows the name of our schema. How do you do that? In the Column Properties navigate to XML Type Specification and click the arrow. On the right side there a drop-down allowing you to select the name of the schema that applies to this column.

Below is the sql code that inserts the same XML data into three different types of columns. Normally we would want to use the typed XML. 

INSERT INTO [XMLbookstore].[dbo].[xmlTest]
(untyped, typed, justtext)
VALUES(
'
<bookstore>
	<header>
		<store>I Love Books Inc.</store>
	</header>
		<books>
			<book>
				<category>cooking</category>
				<title>Everyday Italian</title>
				<author>Giada De Laurentiis</author>
				<yearpub>2005</yearpub>
				<price>30.00</price>
			</book>
			<book>
				<category>children</category>
				<title>Harry Potter</title>
				<author>J K. Rowling</author>
				<yearpub>2005</yearpub>
				<price>29.99</price>
			</book>
	    </books>
	<trailer>
		<numberofbooks>2</numberofbooks>
	</trailer>
</bookstore>
',
'<bookstore>
	<header>
		<store>I Love Books Inc.</store>
	</header>
		<books>
			<book>
				<category>cooking</category>
				<title>Everyday Italian</title>
				<author>Giada De Laurentiis</author>
				<yearpub>2005</yearpub>
				<price>30.00</price>
			</book>
			<book>
				<category>children</category>
				<title>Harry Potter</title>
				<author>J K. Rowling</author>
				<yearpub>2005</yearpub>
				<price>29.99</price>
			</book>
	    </books>
	<trailer>
		<numberofbooks>2</numberofbooks>
	</trailer>
</bookstore>
','<bookstore>
	<header>
		<store>I Love Books Inc.</store>
	</header>
		<books>
			<book>
				<category>cooking</category>
				<title>Everyday Italian</title>
				<author>Giada De Laurentiis</author>
				<yearpub>2005</yearpub>
				<price>30.00</price>
			</book>
			<book>
				<category>children</category>
				<title>Harry Potter</title>
				<author>J K. Rowling</author>
				<yearpub>2005</yearpub>
				<price>29.99</price>
			</book>
	    </books>
	<trailer>
		<numberofbooks>2</numberofbooks>
	</trailer>
</bookstore>
'
);

Normally we need to keep tight control of what gets entered into a table. Therefore we will want to use the typed data. Using data that validates against the schema is like having constraints in your tables.