小猫的博客

博客园 首页 新随笔 联系 订阅 管理

 

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

/// <summary>
/// XMLSerializer xml对象序列化工具类
/// </summary>

public class XMLSerializer<T>
{

    
Serialization support

}

 

-- 创建临时表
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,2not NULL check( Weight > 0),
    Handed 
varchar(5not 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(50NOT NULL
  , LastName 
VARCHAR(50NOT 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'


posted on 2007-01-21 19:42  吕枫  阅读(1306)  评论(0编辑  收藏  举报