1、XML
xml数据可以组成为多个属性,或者多个项目
CREATE DATABASE Test
USE TEST
GO
drop table dbo.book
create table dbo.book
(BookID int identity(1,1) primary key,
BookNM char(100) not null,
AuthorID int not null,
ChapterDESC XML null
)
go
--1.非类型化XML
declare @book xml
set @book =
CAST('<book name="sql server 2000 fast answers">
<chapters>
<chapter id="1">Installation,Upgrades...</chapter>
<chapter id="2">Configuring SQL Server</chapter>
<chapter id="3">Creating and Configuring Databases</chapter>
<chapter id="">SQL Server Agent and SQL Logs</chapter>
</chapters>
</book>' as XML
)
insert into dbo.book(BOOKNM,AUTHORID,CHAPTERDESC)
values('sql server',
55,
@book)
--2.创建XML架构
CREATE XML SCHEMA COLLECTION BOOKSTORECOLLECTION
AS
N'<xsd:schema targetNamespace="http://ggg/bookstore"
xmlns:xsd="http://www.w3.org/2001/XMLSchema"
xmlns:sqltypes="http://schemas.microsoft.com/sqlserver/2004/sqltypes"
elementFormDefault="qualified">
<xsd:import namespace="http://schemas.microsoft.com/sqlserver/2004/sqltypes" />
<xsd:element name="bookxml">
<xsd:complexType>
<xsd:sequence>
<xsd:element name="bookname" minOccurs="0">
<xsd:simpleType>
<xsd:restriction base="sqltypes:varchar">
<xsd:maxLength value="50" />
</xsd:restriction>
</xsd:simpleType>
</xsd:element>
<xsd:element name="ChapterID" type="sqltypes:int" minOccurs="0" />
<xsd:element name="ChapterNM" minOccurs="0">
<xsd:simpleType>
<xsd:restriction base="sqltypes:varchar">
<xsd:maxLength value="50" />
</xsd:restriction>
</xsd:simpleType>
</xsd:element>
</xsd:sequence>
</xsd:complexType>
</xsd:element>
</xsd:schema>
'
--3.类型化xml
create table dbo.bookXML
(BookID int identity(1,1) primary key,
BookNM char(100) not null,
ChapterID int not null,
ChapterDESC XML (bookStoreCollection) null --类型化xml
)
declare @bookxml xml(bookstorecollection)
set @bookxml ='<bookxml xmlns="http://ggg/bookstore" >
<bookname>sql</bookname>
<ChapterID>123</ChapterID>
<ChapterNM>sqlwc</ChapterNM>
</bookxml>
'
insert into bookXML(BookNM,ChapterID,ChapterDESC)
values('sql server 2008',123,@bookxml)
select * from bookXML
--4.1查看xml架构
select *
from SYS.xml_schema_collections
--4.2查看命名空间
select n.*,
c.*
from sys.xml_schema_namespaces n
inner join sys.xml_schema_collections c
on n.xml_collection_id = c.xml_collection_id
--4.3删除xml架构
drop xml schema collection bookstorecollection
获取数据
create table dbo.bookInvoice
(bookInvoiceID int identity(1,1) primary key,
bookinvoiceXML xml not null)
insert into bookInvoice(bookinvoiceXML)
values('<bookinvoice invoicenumber="1" customerid="22" orderdate="2008-07-01">
<orderitems>
<item id="22" qty="1" name="sql fun in the sun" />
<item id="24" qty="1" name="t-sql crossword puzzles" />
</orderitems>
</bookinvoice>')
insert into bookInvoice(bookinvoiceXML)
values('<bookinvoice invoicenumber="1" customerid="40" orderdate="2008-07-11">
<orderitems>
<item id="11" qty="1" name="MCDBA Cliff Notes" />
</orderitems>
</bookinvoice>')
insert into bookInvoice(bookinvoiceXML)
values('<bookinvoice invoicenumber="1" customerid="9" orderdate="2008-07-22">
<orderitems>
<item id="11" qty="1" name="MCDBA Cliff Notes" />
<item id="24" qty="1" name="t-sql crossword puzzles" />
</orderitems>
</bookinvoice>')
--exist方法
select bookInvoiceID
from dbo.bookInvoice
where bookinvoiceXML.exist(
'/bookinvoice/orderitems/item[@id=11]') = 1
--nodes方法、value方法
declare @bookxml xml
select @bookxml = bookinvoiceXML
From dbo.bookInvoice
where bookInvoiceID = 1
select bookId.value('@id','integer')
from @bookxml.nodes(
'/bookinvoice/orderitems/item') as booktable(BookID)
--query方法
declare @v xml
select @v = bookinvoiceXML
from dbo.bookInvoice
where bookInvoiceID = 1
select @v.query('/bookinvoice/orderitems')
--value方法,一次只能处理一个值,所以这里加了[1]来限制只取返回结果集的第一行
--[2]限制只取返回结果集的第二行
select
bookinvoicexml.value(
'(/bookinvoice/orderitems/item/@name)[1]',
'varchar(30)') as titles
from dbo.bookInvoice
union
select
bookinvoicexml.value(
'(/bookinvoice/orderitems/item/@name)[2]',
'varchar(30)')
from dbo.bookInvoice
修改数据
--通过modify方法插入一geitem
update dbo.book
set ChapterDESC.modify
('insert <chapter id="5">SQL SERVER INTERNALS</chapter>
into (/book/chapters)[1]')
select * from dbo.book
建立XML索引
可以使用xml索引提高xml数据类型列的查询性能,表 必须已经在主键上定义了聚集索引。xml列只能建立一个主xml索引,以及最多3个辅助。
Create XML Index CREATE [ PRIMARY ] XML INDEX index_name ON <object> ( xml_column_name ) [ USING XML INDEX xml_index_name [ FOR { VALUE | PATH | PROPERTY } ] ] [ WITH ( <xml_index_option> [ ,...n ] ) ] [ ; ] <object> ::= { [ database_name. [ schema_name ] . | schema_name. ] table_name } <xml_index_option> ::= { PAD_INDEX = { ON | OFF } | FILLFACTOR = fillfactor | SORT_IN_TEMPDB = { ON | OFF } | IGNORE_DUP_KEY = OFF | DROP_EXISTING = { ON | OFF } | ONLINE = OFF | ALLOW_ROW_LOCKS = { ON | OFF } | ALLOW_PAGE_LOCKS = { ON | OFF } | MAXDOP = max_degree_of_parallelism }
--1.建立主xml索引
create primary xml index idx_xml_primary_book_ChapterDesc
on dbo.book(ChapterDesc)
/*=======================================================
2.建立辅助xml索引,下面的参数用于辅助索引,和xquery优化相关:
A.value辅助索引用于根据模糊路径创建索引。
B.path辅助索引根据路径和节点值创建索引。
C.property辅助索引根据某个路径查询节点值,来创建索引
========================================================*/
create xml index idx_xml_value_book_ChapterDesc
on dbo.book(ChapterDesc)
using xml index idx_xml_primary_book_ChapterDesc
for value
--3.查看xml索引元数据
select * from sys.xml_indexes
select *
from sys.indexes
where name in ('idx_xml_primary_book_ChapterDesc',
'idx_xml_value_book_ChapterDesc')
在xml文档与关系型数据之间进行转换
--1.把关系型数据格式化为xml
/*===============================================
<shifts>
<shift OBJECT_ID="3" name="sysrscols" />
<shift OBJECT_ID="5" name="sysrowsets" />
<shift OBJECT_ID="7" name="sysallocunits" />
<shift OBJECT_ID="8" name="sysfiles1" />
<shift OBJECT_ID="17" name="syspriorities" />
<shift OBJECT_ID="19" name="sysfgfrag" />
<shift OBJECT_ID="23" name="sysphfg" />
<shift OBJECT_ID="24" name="sysprufiles" />
</shifts>
=================================================*/
select top 8
OBJECT_ID, --属性
name --属性
from sys.objects
for xml raw('shift'), --item
root('shifts'), --根
type
/*===============================================
<o object_id="37575172">
<t name="wcObjects">
<c name="name" />
<c name="object_id" />
<c name="principal_id" />
<c name="schema_id" />
<c name="parent_object_id" />
<c name="type" />
<c name="type_desc" />
<c name="create_date" />
<c name="modify_date" />
<c name="is_ms_shipped" />
</t>
</o>
=================================================*/
select top 10 --需要显示的列,所对应的表别名,作为item
--改变列的显示顺序会改变xml的层级
o.object_id, --作为o的item中的object_id属性
t.name, --属性t的item中的name属性
c.name --属性作为c的item中的name属性
from sys.objects o
inner join sys.tables t
on o.object_id = t.object_id
inner join sys.columns c
on t.object_id = c.object_id
for xml auto,
type
/*===============================================
<t object_id="37575172" name="wcObjects">
<c name="name" />
<c name="object_id" />
<c name="principal_id" />
<c name="schema_id" />
<c name="parent_object_id" />
<c name="type" />
<c name="type_desc" />
<c name="create_date" />
<c name="modify_date" />
<c name="is_ms_shipped" />
</t>
=================================================*/
select top 10 --需要显示的列,所对应的表别名,作为item
--改变列的显示顺序会改变xml的层级
t.object_id, --作为t的item中的object_id属性
t.name, --属性t的item中的name属性,也就是说t有2个属性
c.name --属性作为c的item中的name属性
from sys.objects o
inner join sys.tables t
on o.object_id = t.object_id
inner join sys.columns c
on t.object_id = c.object_id
for xml auto,
type
/*===============================================
<wc object_id="3" schema_id="4">
<wc_name>sysrscols</wc_name>
</wc>
<wc object_id="5" schema_id="4">
<wc_name>sysrowsets</wc_name>
</wc>
<wc object_id="7" schema_id="4">
<wc_name>sysallocunits</wc_name>
</wc>
=================================================*/
select top 3
1 as tag,
null as parent,
object_id as [wc!1!object_id],
name as [wc!1!wc_name!element],
schema_id as [wc!1!schema_id]
from sys.objects o
for xml explicit,
type
/*===============================================
<w>
<wc wc_name="sysrscols" object_id="3">
<schema_id>4</schema_id>
</wc>
<wc wc_name="sysrowsets" object_id="5">
<schema_id>4</schema_id>
</wc>
<wc wc_name="sysallocunits" object_id="7">
<schema_id>4</schema_id>
</wc>
</w>
=================================================*/
select top 3
name as '@wc_name', --属性
object_id as '@object_id', --属性
schema_id --item
from sys.objects o
for xml path('wc'),
root('w'),
type
--2.把xml转化为关系型
declare @book xml
set @book =
CAST('<book>
<chapters>
<chapter id="1">Installation,Upgrades</chapter>
</chapters>
<chapters>
<chapter id="2">Configuring SQLServer</chapter>
</chapters>
</book>' as XML
)
--xml文档的句柄
declare @document int
--取得xml文档的句柄
exec sp_xml_preparedocument
@document output,
@book
select chapter_id,
chapter_name
from openxml(@document,
'/book/chapters',
1)
with (chapter_id int 'chapter/@id',
chapter_name nvarchar(100) 'chapter')
--删除文档句柄指定的xml文档的内部表示形式,并使该文档句柄无效
exec sp_xml_removedocument @document
2、分层
在SQL Server 2008中引入了hierarchyid数据类型,可以用来做本地存储,可以表示树层次结构中节点的位置,其中包含了几个可以操作、遍历层次结构的内置方法。
--1.建表,注意方法名称的大小写
create table dbo.webpage
(webpageID hierarchyid not null,
positionDESC as webpageid.GetLevel(), --取得层级
pageurl nvarchar(50) not null
)
--插入数据
insert into dbo.webpage(webpageID,pageurl)
values('/','http://wc.com')
insert into dbo.webpage(webpageID,pageurl)
values('/1/','http://wc.com/abc/')
insert into dbo.webpage(webpageID,pageurl)
values('/2/','http://wc.com/wc.htm')
declare @parent hierarchyid
set @parent = CONVERT(hierarchyid,'/1/')
insert into dbo.webpage(webpageID,pageurl)
values(@parent.GetDescendant(null,null),
'http://wc.com/abc/abc1.html')
insert into dbo.webpage(webpageID,pageurl)
values(@parent.GetDescendant(null,null),
'http://wc.com/abc/abc2.html')
--2.显示层次数据
select webpageID, --二进制表示
webpageID.ToString(), --字符串表示
positionDESC,
pageurl
from dbo.webpage
--3.返回指定层级的向上指定级数的层级
select CONVERT(hierarchyid,'/1/1/').GetAncestor(1).ToString(),
hierarchyid::Parse('/1/1/'), --解析字符串转为hierarchyid
hierarchyid::Parse('/1/1/').GetAncestor(1).ToString()
--4返回指定层级的子节点
select CONVERT(hierarchyid,'/1/').GetDescendant(null,null).ToString()
--5.返回节点的深度
sel
ect CONVERT(hierarchyid,'/1/1/1/1/').GetLevel()
--6.返回根节点
select hierarchyid::GetRoot()
select *
from dbo.webpage
where webpageID = hierarchyid::GetRoot()
--7.验证是否是当前节点的子节点
select hierarchyid::Parse('/1/').IsDescendantOf('/')
--8.修改节点位置
select hierarchyid::Parse('/1/1/'),
hierarchyid::Parse('/1/1/').GetReparentedValue('/1/', --原来的上一级节点
'/2/') --现在的上一级节点
3、空间数据
SQL Server 2008引入了原生的空间数据存储,提供了geography和geometry两种新的数据类型,这些数据类型为位置和制图应用程序、几何形状的表示,提供了内建的功能。
geography数据类型可以存储圆球空间,也可以存储坐标的经度、纬度、点、多边形、曲线、集合。
geometry数据类型表示欧几里得坐标空间数据,也可以存储点、多边形、曲线、集合。SQL Server 2008支持文本(WKT)、二进制(WKB)、地理标记语言(GML)的XML格式,来表示矢量几何映射对象。开放地理空间联盟(OGC)中常用这些格式,这里通过WKT格式使用geography数据类型。
--1.空间数据
with Geo
as
(
select id,
lon,
lat,
geography::Parse('POINT('+lon+space(1)+lat+')') as g
from
(
select 1 as id,'-16.96732' as lon,'36.943' as lat
union all
select 1,'-16.58963','36.943'
)a
where lon is not null and
lat is not null
)
--2.计算坐标之间的距离
select
s.ID,
g.STDistance(geography::Parse('POINT('+r.LON+SPACE(1)+r.LAT+')'))
from Geo s
inner join
(
select 1 as id,'-116.26598' as lon,'39.27763' as lat
union all
select 1,'-16.32683','36.94673'
) r
on s.ID = r.ID
--3.建立有空间数据的表
create table x
(
v int not null identity(1,1) primary key,
geog geography not null,
geogWKT as geog.STAsText()
)
--4.添加空间数据
insert into x(geog)
values(geography::Parse('POLYGON(
(-93.123 36.943,
-93.126 36.953,
-94.129 36.986,
-93.123 36.943)
)'
)
), --多边形,开始坐标和结束坐标必须相同,注意polygon中必须包含2层括号,否则报错
(geography::Parse('POINT(-93.123 36.943)')), --点坐标
(geography::Parse('LINESTRING(-93.123 36.943,
-93.126 36.953)')
) --两坐标之间的线
--5.地理数据的计算
select v,
geogWKT,
geog.STDistance('POINT (-93.123 36.985)'), --距离
geog.STIntersects('POINT (-93.123 36.943)'), --是否有交集
geog.STLength(), --长度
geog.STArea(), --多边形面积
geog.STAsText() --WKT格式的坐标
from x