Sql Server 2005 xml datatype Entity-Attribute-Value (EAV) model 实践

 

using System;
using System.IO;
using System.Xml;
using System.Xml.Serialization;

/**//// <summary>
/// XMLSerializer xml对象序列化工具类
/// </summary>
public class XMLSerializer<T>
{

    Serialization support#region Serialization support
    /**//// <summary>
    /// XML serializer for class
    /// </summary>
    private System.Xml.Serialization.XmlSerializer _serializer =
                            new System.Xml.Serialization.XmlSerializer(typeof(T));

    /**//// <summary>
    /// Serialize object into XML string
    /// </summary>
    /// <param name="myobject"></param>
    /// <returns></returns>
    public String Serialize(T myobject)
    {
        try
        {
            String result = null;
            if (myobject != null)
            {
                using (MemoryStream ms = new MemoryStream())
                {
                    using (XmlTextWriter xtw = new XmlTextWriter(ms, System.Text.Encoding.UTF8))
                    {
                        xtw.Formatting = Formatting.Indented;
                        _serializer.Serialize(xtw, myobject);
                        //rewind
                        ms.Seek(0, SeekOrigin.Begin);
                        using (StreamReader reader = new StreamReader(ms, System.Text.Encoding.UTF8))
                        {
                            result = reader.ReadToEnd();
                            xtw.Close();
                            reader.Close();
                        }
                    }
                }
            }
            return result;
        }
        catch
        {
            throw;
        }
    }

    /**//// <summary>
    /// Deserialize object into an instance of T
    /// </summary>
    /// <param name="xml"></param>
    /// <returns></returns>
    public T Deserialize(String xml)
    {
        try
        {
            if (!String.IsNullOrEmpty(xml))
            {
                using (StringReader sr = new StringReader(xml))
                {
                    return (T)_serializer.Deserialize(sr);
                }
            }
            else
                return default(T);
        }
        catch
        {
            throw;
        }
    }

    #endregion

}

 

-- 创建临时表
IF OBJECT_ID('ClientAttributes') IS NOT NULL
    drop table ClientAttributes
go
-- problem with datetime datatype look above URL
-- create a table with desired attributes
create table ClientAttributes
(
    Age int not NULL check( Age > -1) ,
    Weight numeric(10,2) not NULL check( Weight > 0),
    Handed varchar(5) not null check( Handed in ('left', 'right')),
    QuitDate datetime null, 
    HasInsurance bit not null default(1)
)

go

-- 创建 XML schema 并加上 client attributes

DECLARE @mySchema xml 

-- this is the default schema that gets created from the ClientAttributes table
-- however the check constraints aren't converted
SET @mySchema = (SELECT * FROM ClientAttributes FOR XML AUTO, ELEMENTS, XMLSCHEMA('ClientAttributes'))
-- see the schema that gets auto-created
select @mySchema 

<!-- 执行后得到以下xml -->
<xsd:schema xmlns:schema="ClientAttributes" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:sqltypes="http://schemas.microsoft.com/sqlserver/2004/sqltypes" targetNamespace="ClientAttributes" elementFormDefault="qualified">
  <xsd:import namespace="http://schemas.microsoft.com/sqlserver/2004/sqltypes" schemaLocation="http://schemas.microsoft.com/sqlserver/2004/sqltypes/sqltypes.xsd" />
  <xsd:element name="ClientAttributes">
    <xsd:complexType>
      <xsd:sequence>
        <xsd:element name="Age" type="sqltypes:int" />
        <xsd:element name="Weight">
          <xsd:simpleType>
            <xsd:restriction base="sqltypes:numeric">
              <xsd:totalDigits value="10" />
              <xsd:fractionDigits value="2" />
            </xsd:restriction>
          </xsd:simpleType>
        </xsd:element>
        <xsd:element name="Handed">
          <xsd:simpleType>
            <xsd:restriction base="sqltypes:varchar" sqltypes:localeId="2052" sqltypes:sqlCompareOptions="IgnoreCase IgnoreKanaType IgnoreWidth">
              <xsd:maxLength value="5" />
            </xsd:restriction>
          </xsd:simpleType>
        </xsd:element>
        <xsd:element name="QuitDate" type="sqltypes:datetime" minOccurs="0" />
        <xsd:element name="HasInsurance" type="sqltypes:bit" />
      </xsd:sequence>
    </xsd:complexType>
  </xsd:element>
</xsd:schema>

-- 手工加入 <xsd:restriction> 标签
SET @mySchema = 
    '<xsd:schema xmlns:schema="ClientAttributes" xmlns:xsd="http://www.w3.org/2001/XMLSchema" 
             xmlns:sqltypes="http://schemas.microsoft.com/sqlserver/2004/sqltypes" 
             targetNamespace="ClientAttributes" elementFormDefault="qualified">
    <xsd:import namespace="http://schemas.microsoft.com/sqlserver/2004/sqltypes" 
             schemaLocation="http://schemas.microsoft.com/sqlserver/2004/sqltypes/sqltypes.xsd" />
    <xsd:element name="ClientAttributes">
        <xsd:complexType>
            <xsd:sequence>
                <xsd:element name="Age" >
                    <xsd:simpleType>
                        <xsd:restriction base="sqltypes:int">
                            <xsd:minInclusive value="0" />
                        </xsd:restriction>
                    </xsd:simpleType>
                </xsd:element>
                <xsd:element name="Weight">
                    <xsd:simpleType>
                        <xsd:restriction base="sqltypes:numeric">
                            <xsd:totalDigits value="10" />
                            <xsd:fractionDigits value="2" />
                            <xsd:minExclusive value="0" />
                        </xsd:restriction>                        
                    </xsd:simpleType>
                </xsd:element>
                <xsd:element name="Handed">
                    <xsd:simpleType>
                        <xsd:restriction base="sqltypes:varchar" sqltypes:localeId="1060" 
                                   sqltypes:sqlCompareOptions="IgnoreCase IgnoreKanaType IgnoreWidth">
                            <xsd:maxLength value="5" />
                            <xsd:pattern value="left|right"/>
                        </xsd:restriction>
                    </xsd:simpleType>
                </xsd:element>
                <xsd:element name="QuitDate" minOccurs="0">
                    <xsd:simpleType>
                        <xsd:restriction base="sqltypes:varchar" sqltypes:localeId="1060" 
                                        sqltypes:sqlCompareOptions="IgnoreCase IgnoreKanaType IgnoreWidth">
                            <xsd:maxLength value="24" />                            
                            <xsd:pattern value="\d\d\d\d-?\d\d-?\d\d \d\d:\d\d:\d\d(:\d{3})?" />
                        </xsd:restriction>
                    </xsd:simpleType>
                </xsd:element>
                <xsd:element name="HasInsurance" type="sqltypes:bit" />
            </xsd:sequence>
        </xsd:complexType>
    </xsd:element>
</xsd:schema>'

-- 创建SCHEMA COLLECTION 
CREATE XML SCHEMA COLLECTION ClientAttributesSchema AS @mySchema
go

-- 删除临时表
drop table ClientAttributes 
go
IF OBJECT_ID('Client') IS NOT NULL
    drop table Client

-- 创建使用此xml类型的数据表
CREATE TABLE Client
(
    ClientID INT NOT NULL PRIMARY KEY
  , FirstName VARCHAR(50) NOT NULL
  , LastName VARCHAR(50) NOT NULL
  , AttribXML xml(ClientAttributesSchema) -- xml column with schema
) 
go

-- 加入函数到ClientAttributes命名空间以验证输入
IF OBJECT_ID('dbo.ClientAttributeValuesValidation') IS NOT NULL
    drop function dbo.ClientAttributeValuesValidation
go

CREATE FUNCTION dbo.ClientAttributeValuesValidation(@chkcol xml)
 RETURNS nvarchar(4000)
AS
BEGIN
    RETURN @chkcol.value('namespace-uri((/*)[1])','nvarchar(4000)')
END
GO

-- 添加此验证到数据表
ALTER TABLE Client WITH NOCHECK ADD CONSTRAINT chk_ClientAttributeValueValidation
 CHECK (dbo.ClientAttributeValuesValidation(AttribXML) = 'ClientAttributes');
GO

-- 测试数据
INSERT INTO Client (ClientID, FirstName, LastName, AttribXML) 
SELECT 1, 'First Name 1', 'Last Name 1',
            N'<ClientAttributes xmlns="ClientAttributes">
                <Age>22</Age>
                <Weight>10.2</Weight>
                <Handed>left</Handed>
                <QuitDate>2006-10-01 00:00:00</QuitDate>                
                <HasInsurance>1</HasInsurance>
            </ClientAttributes>'
-- 测试数据 日期格式不同
INSERT INTO Client (ClientID, FirstName, LastName, AttribXML) 
SELECT 2, 'First Name 2', 'Last Name 3',
            N'<ClientAttributes xmlns="ClientAttributes">
                <Age>22</Age>
                <Weight>10.2</Weight>
                <Handed>left</Handed>
                <QuitDate>20061001 00:00:00</QuitDate>                
                <HasInsurance>1</HasInsurance>
            </ClientAttributes>'
-- 日期列为null
INSERT INTO Client (ClientID, FirstName, LastName, AttribXML)
SELECT 3, 'First Name 3', 'Last Name 3',
            N'<ClientAttributes xmlns="ClientAttributes">
                <Age>23</Age>
                <Weight>10.2</Weight>
                <Handed>right</Handed>
                <HasInsurance>1</HasInsurance>
            </ClientAttributes>'
-- Handed 字段数值不满足条件,此语句将失败
INSERT INTO Client (ClientID, FirstName, LastName, AttribXML)
SELECT 4, 'First Name 4', 'Last Name 4',
            N'<ClientAttributes xmlns="ClientAttributes">
                <Age>1</Age>
                <Weight>1</Weight>
                <Handed>ffdg</Handed>
                <QuitDate>20061001 00:00:00</QuitDate>
                <HasInsurance>1</HasInsurance>
            </ClientAttributes>'


-- Schema collection info
SELECT    XSC.*
FROM    sys.xml_schema_collections XSC
WHERE    XSC.name = 'ClientAttributesSchema'
-- Schema collection namespaces info
SELECT    *
FROM    sys.xml_schema_collections XSC
        JOIN sys.xml_schema_namespaces XSN
                on XSC.xml_collection_id = XSN.xml_collection_id
WHERE    XSC.name = 'ClientAttributesSchema'
-- Schema collection Components info
SELECT    *
FROM    sys.xml_schema_collections XSC
        JOIN sys.xml_schema_components XSM
                on XSC.xml_collection_id = XSM.xml_collection_id
WHERE    XSC.name = 'ClientAttributesSchema'