Continuing our discussion from yesterday on SQL Server 2008 XML Enhancements, let's take a look at the XSD enhancement of Union and List types. In SQL Server 2005 you could define a simple type of xsd:list with an enumeration to require items in a list for any element that inherits that type. For example our XSD below will allow FedEx, DHL, and UPS for a shipping.
create XML SCHEMA COLLECTION dbo.order_xsd
AS
'<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema">
<xsd:simpleType name="orderAmt" >
<xsd:restriction base="xsd:int" >
<xsd:maxInclusive value="5000" />
<xsd:minExclusive value="1" />
</xsd:restriction>
</xsd:simpleType>
<xsd:simpleType name="shiptypeList">
<xsd:list>
<xsd:simpleType>
<xsd:restriction base="xsd:string">
<xsd:enumeration value="FexEx"/>
<xsd:enumeration value="DHL"/>
<xsd:enumeration value="UPS"/>
</xsd:restriction>
</xsd:simpleType>
</xsd:list>
</xsd:simpleType>
<xsd:element name="Order">
<xsd:complexType>
<xsd:sequence>
<xsd:element name="CustomerName" type="xsd:string" />
<xsd:element name="OrderDate" type="xsd:dateTime"/>
<xsd:element name="OrderAmt" type="orderAmt"/>
<xsd:element name="ShipType" type="shiptypeList"/>
</xsd:sequence>
</xsd:complexType>
</xsd:element>
</xsd:schema>'
GO
DECLARE @xml XML(dbo.order_xsd)
SET @xml =
'<Order>
<CustomerName>Bill Gates</CustomerName>
<OrderDate>2008-10-10T14:22:27.25-05:00</OrderDate>
<OrderAmt>100</OrderAmt>
<ShipType>DHL</ShipType>
</Order>'
GO
So this is cool, gives you some database lookup functionality, kind of third normal form Clemens Vasters style. With the new support for union of lists in SQL Server 2008 with xsd:union, you can combine multiple lists to one simple type. For example in our XSD below we allow the same strings as FedEx, etc, but also numerical values.
My example below is pretty basic, but this is useful if you have more than one way to describe something and need two lists to do so. One thing that comes to mind is units of measurement, metric and English measurements, so shoe sizes, US and Italian, etc. This is very useful when you need to restrict items and are writing them from a database, etc.
Take a look at the demo for SQL 2008 below, notice that I changed the data to 1 from FedEx:
create XML SCHEMA COLLECTION dbo.order_xsd
AS
'<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema">
<xsd:simpleType name="orderAmt" >
<xsd:restriction base="xsd:int" >
<xsd:maxInclusive value="5000" />
<xsd:minExclusive value="1" />
</xsd:restriction>
</xsd:simpleType>
<xsd:simpleType name="shiptypeList">
<xsd:union>
<xsd:simpleType>
<xsd:list>
<xsd:simpleType>
<xsd:restriction base="xsd:integer">
<xsd:enumeration value="1"/>
<xsd:enumeration value="2"/>
<xsd:enumeration value="3"/>
</xsd:restriction>
</xsd:simpleType>
</xsd:list>
</xsd:simpleType>
<xsd:simpleType>
<xsd:list>
<xsd:simpleType>
<xsd:restriction base="xsd:string">
<xsd:enumeration value="FedEx"/>
<xsd:enumeration value="DHL"/>
<xsd:enumeration value="UPS"/>
</xsd:restriction>
</xsd:simpleType>
</xsd:list>
</xsd:simpleType>
</xsd:union>
</xsd:simpleType>
<xsd:element name="Order">
<xsd:complexType>
<xsd:sequence>
<xsd:element name="CustomerName" type="xsd:string" />
<xsd:element name="OrderDate" type="xsd:dateTime"/>
<xsd:element name="OrderAmt" type="orderAmt"/>
<xsd:element name="ShipType" type="shiptypeList"/>
<xsd:any namespace="##other" processContents="lax"/>
</xsd:sequence>
</xsd:complexType>
</xsd:element>
</xsd:schema>'
GO
DECLARE @xml XML(dbo.order_xsd)
SET @xml =
'<Order>
<CustomerName>Bill Gates</CustomerName>
<OrderDate>2008-10-10T14:22:27.25-05:00</OrderDate>
<OrderAmt>100</OrderAmt>
<ShipType>1</ShipType>
<Notes xmlns="sf">Steve Test 1</Notes>
</Order>'
GO