【SQL Server学习笔记】XML、分层、空间数据

1、XML

xml数据可以组成为多个属性，或者多个项目

CREATE DATABASE TestUSE TESTGOdrop table dbo.bookcreate table dbo.book(BookID int identity(1,1) primary key, BookNM char(100) not null, AuthorID int not null, ChapterDESC XML null)go--1.非类型化XMLdeclare @book xmlset @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 BOOKSTORECOLLECTIONASN'<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.类型化xmlcreate 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 ninner 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 bookInvoiceIDfrom dbo.bookInvoicewhere bookinvoiceXML.exist(                    '/bookinvoice/orderitems/item[@id=11]') = 1--nodes方法、value方法declare @bookxml xmlselect @bookxml = bookinvoiceXMLFrom dbo.bookInvoicewhere bookInvoiceID = 1                          select bookId.value('@id','integer')from @bookxml.nodes(              '/bookinvoice/orderitems/item') as booktable(BookID)              --query方法declare @v xmlselect @v = bookinvoiceXMLfrom dbo.bookInvoicewhere bookInvoiceID = 1select @v.query('/bookinvoice/orderitems')    --value方法,一次只能处理一个值，所以这里加了[1]来限制只取返回结果集的第一行--[2]限制只取返回结果集的第二行select         bookinvoicexml.value(                '(/bookinvoice/orderitems/item/@name)[1]',                'varchar(30)') as titlesfrom dbo.bookInvoiceunionselect         bookinvoicexml.value(                '(/bookinvoice/orderitems/item/@name)[2]',                'varchar(30)')from dbo.bookInvoice          

 修改数据

--通过modify方法插入一geitemupdate dbo.bookset 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_ChapterDescon dbo.book(ChapterDesc)/*=======================================================2.建立辅助xml索引,下面的参数用于辅助索引，和xquery优化相关:A.value辅助索引用于根据模糊路径创建索引。B.path辅助索引根据路径和节点值创建索引。C.property辅助索引根据某个路径查询节点值，来创建索引========================================================*/create xml index idx_xml_value_book_ChapterDescon dbo.book(ChapterDesc)using xml index idx_xml_primary_book_ChapterDescfor value--3.查看xml索引元数据select * from sys.xml_indexesselect *from sys.indexeswhere 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.objectsfor 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 oinner join sys.tables t        on o.object_id = t.object_idinner join sys.columns c        on t.object_id = c.object_idfor 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 oinner join sys.tables t        on o.object_id = t.object_idinner join sys.columns c        on t.object_id = c.object_idfor 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 ofor 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                   --itemfrom sys.objects ofor xml path('wc'),        root('w'),        type--2.把xml转化为关系型declare @book xmlset @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,@bookselect 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 hierarchyidset @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,       pageurlfrom 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.返回节点的深度select 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 Geoas(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 latunion allselect 1,'-16.58963','36.943'       )awhere 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 sinner join (select 1 as id,'-116.26598' as lon,'39.27763' as latunion allselect 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