typed xml in Sql server

How to ceate typed xml in Sql server.

1. Create xml schema information in the SQL Server.

create xml schema collection DepartmentSchema as
'<xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema">
<xs:element name="department">
 <xs:complexType>
  <xs:all>
   <xs:element name="name" type="xs:string" />
   <xs:element name="office" type="xs:string" />
  </xs:all>
  <xs:attribute name="id" type="xs:int" />
 </xs:complexType>
</xs:element>
</xs:schema>'

2. Create talbe use this Schema "DepartmentSchema".

create table Department(id int primary key, info xml(DepartmentSchema))

3. Insert data to the table  department.

insert into Department values(1,'<department id="1"><name>HR</name><office>HuaShan</office></department>');

insert into department values(2,'<department id="2"><office>SongShan</office><name>IT</name></department>');

 

Create a schema have targetNamespace

They have a small difference. We'll step in to it now.

1. create the schema in the SQL Server.

create xml schema collection StudentSchema as
'<xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema" xmlns:company="http://www.guowei.com/" targetNamespace="http://www.guowei.com/" elementFormDefault="qualified">
<xs:element name="student">
 <xs:complexType>
  <xs:all>
   <xs:element name="name" type="xs:string" />
  </xs:all>
  <xs:attribute name="id" type="xs:int" />
 </xs:complexType>
</xs:element>
</xs:schema>'

2. Create the table use this Schema.

create table student(id int primary key,info xml(studentSchema));

3. insert data in to talbe.

insert into student values
(
1, '<student id="1" xmlns="http://www.guowei.com"><name>Mark</name></student>'
)

We can see that the xmlns="http://www.guowei.com/" , if we don't use this tag, the data can't insert in to the table, if the xml field is not wellformed, the data can't insert into the table too.