--drop table dbo.tbl_Orders
--go
--drop xml schema collection xsdPurchaseOrder
--go
create xml schema collection xsdPurchaseOrder as
N' <schema targetNamespace="http://www.example.com/IPO" xmlns="http://www.w3.org/2001/XMLSchema"
xmlns:ipo="http://www.example.com/IPO" elementFormDefault="qualified"><annotation>
<documentation xml:lang="zh-Hans">
为国际订货单据定义的各种地址格式
Copyright 2000 Example.com. All rights reserved.
</documentation>
</annotation><!-- 一般地址 -->
<complexType name="Address">
<sequence>
<element name="name" type="string"/>
<element name="street" type="string"/>
<element name="city" type="string"/>
</sequence>
</complexType><!-- 美国地址附加信息 -->
<complexType name="USAddress">
<complexContent>
<extension base="ipo:Address">
<sequence>
<element name="state" type="ipo:USState"/>
<element name="zip" type="positiveInteger"/>
</sequence>
</extension>
</complexContent>
</complexType><!-- 中国地址附加信息 -->
<complexType name="CNAddress">
<complexContent>
<extension base="ipo:Address">
<sequence>
<element name="省" type="ipo:CNProvince" />
<element name="邮编" type="positiveInteger" />
</sequence>
</extension>
</complexContent>
</complexType><!-- 定义美国州的数据类型 -->
<simpleType name="USState">
<restriction base="string">
<enumeration value="AK"/>
<enumeration value="AL"/>
<enumeration value="AR"/>
<!-- and so on ... -->
<enumeration value="PA"/>
</restriction>
</simpleType><!-- 定义中国省的数据类型 -->
<simpleType name="CNProvince">
<restriction base="string">
<enumeration value="湖南"/>
<enumeration value="湖北"/>
<enumeration value="辽宁"/>
<!-- and so on ... -->
<enumeration value="江苏"/>
</restriction>
</simpleType><!-- 根元素 -->
<element name="purchaseOrder" type="ipo:PurchaseOrderType"/><element name="comment" type="string"/><complexType name="PurchaseOrderType">
<sequence>
<element name="shipTo" type="ipo:CNAddress"/>
<element name="billTo" type="ipo:USAddress"/>
<element ref="ipo:comment" minOccurs="0"/>
<!-- 为了简化我剩去了items的复杂定义 -->
<element name="items" type="string"/>
</sequence>
<attribute name="orderDate" type="string"/>
</complexType>
</schema>'
go create table tbl_Orders( id int, PurchaseOrder xml(xsdPurchaseOrder) )insert into tbl_Orders (id,PurchaseOrder)
values( 1,
N' <purchaseOrder xmlns="http://www.example.com/IPO" orderDate="01-01-01">
<shipTo>
<name>兴业公司 </name>
<street>兴业路十五号 </street>
<city>无锡 </city>
<省>江苏</省>
<邮编>12345 </邮编>
</shipTo>
<billTo>
<name>ABC Corp </name>
<street>1710 Madison Ave. </street>
<city>Jamesville </city>
<state>PA</state>
<zip>98000 </zip>
</billTo>
<comment>ABC Corp 购买一批组件并直接寄送兴业公司组装 </comment>
<items>541045系列组件 </items>
</purchaseOrder>' )
go
--go
--drop xml schema collection xsdPurchaseOrder
--go
create xml schema collection xsdPurchaseOrder as
N' <schema targetNamespace="http://www.example.com/IPO" xmlns="http://www.w3.org/2001/XMLSchema"
xmlns:ipo="http://www.example.com/IPO" elementFormDefault="qualified"><annotation>
<documentation xml:lang="zh-Hans">
为国际订货单据定义的各种地址格式
Copyright 2000 Example.com. All rights reserved.
</documentation>
</annotation><!-- 一般地址 -->
<complexType name="Address">
<sequence>
<element name="name" type="string"/>
<element name="street" type="string"/>
<element name="city" type="string"/>
</sequence>
</complexType><!-- 美国地址附加信息 -->
<complexType name="USAddress">
<complexContent>
<extension base="ipo:Address">
<sequence>
<element name="state" type="ipo:USState"/>
<element name="zip" type="positiveInteger"/>
</sequence>
</extension>
</complexContent>
</complexType><!-- 中国地址附加信息 -->
<complexType name="CNAddress">
<complexContent>
<extension base="ipo:Address">
<sequence>
<element name="省" type="ipo:CNProvince" />
<element name="邮编" type="positiveInteger" />
</sequence>
</extension>
</complexContent>
</complexType><!-- 定义美国州的数据类型 -->
<simpleType name="USState">
<restriction base="string">
<enumeration value="AK"/>
<enumeration value="AL"/>
<enumeration value="AR"/>
<!-- and so on ... -->
<enumeration value="PA"/>
</restriction>
</simpleType><!-- 定义中国省的数据类型 -->
<simpleType name="CNProvince">
<restriction base="string">
<enumeration value="湖南"/>
<enumeration value="湖北"/>
<enumeration value="辽宁"/>
<!-- and so on ... -->
<enumeration value="江苏"/>
</restriction>
</simpleType><!-- 根元素 -->
<element name="purchaseOrder" type="ipo:PurchaseOrderType"/><element name="comment" type="string"/><complexType name="PurchaseOrderType">
<sequence>
<element name="shipTo" type="ipo:CNAddress"/>
<element name="billTo" type="ipo:USAddress"/>
<element ref="ipo:comment" minOccurs="0"/>
<!-- 为了简化我剩去了items的复杂定义 -->
<element name="items" type="string"/>
</sequence>
<attribute name="orderDate" type="string"/>
</complexType>
</schema>'
go create table tbl_Orders( id int, PurchaseOrder xml(xsdPurchaseOrder) )insert into tbl_Orders (id,PurchaseOrder)
values( 1,
N' <purchaseOrder xmlns="http://www.example.com/IPO" orderDate="01-01-01">
<shipTo>
<name>兴业公司 </name>
<street>兴业路十五号 </street>
<city>无锡 </city>
<省>江苏</省>
<邮编>12345 </邮编>
</shipTo>
<billTo>
<name>ABC Corp </name>
<street>1710 Madison Ave. </street>
<city>Jamesville </city>
<state>PA</state>
<zip>98000 </zip>
</billTo>
<comment>ABC Corp 购买一批组件并直接寄送兴业公司组装 </comment>
<items>541045系列组件 </items>
</purchaseOrder>' )
go
解决方案 »
免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货