Sql Server2005 XML體驗

Sql Server.2005 XML體驗

一 XML列與XML變量

1. 建立一個帶XML列的表

Create table dbo.XmlTest(XmlTestID int,XmlTestName varchar(50),XmlTestMemo XML)

2. 往XML表裡插入資料

Insert into dbo.XmlTest     values(100,'Name1','<ROOT><XmlTest>300,zhuhui  street, Suzhou </XmlTest></ROOT>')

插入一個非法的XML,系統将會報錯:

Insert into dbo.XmlTest values(1,' Name1','<ROOT><XmlTest>300,zhuhui  street,Suzhou ')

消息9400，級别16，狀态1，第1 行

XML 分析: 行1，字元41，意外的輸入結尾

3 建立XML變量

Declare @xml xml

二  無類型與類型化XML

無類型xml

即xml文檔不和任何模式關聯,能以任何形式存儲. 當我們向一個xml列插入資料時,應該有一個檢查去判斷插入的資料是否符合xml規範.

類型化

xml 将xml列與xml模式相關聯,優點是 sql server會自動用模式來驗證xml的有效性, xml占用的存儲空間少,廣域元素和屬性的類型資訊都由模式提供.

Xml模式

 1.  xml的模式須先存在庫裡，然後建立xml資料類型時提供schema用于對照資料格式，這就是類型化的xml

xml模式可以用下面的方法定義

CREATE XML SCHEMA COLLECTION [ <relational_schema>. ]sql_identifier AS Expression

·      Schema名稱，未定義的話，就用預設的schema

·      xml schema集合的sql辨別

·      表達式，是字元串常量或變量，可以是如下類型 varchar, varbinary,       nvarchar, nvarbinary, or xml type.

示例建立xml模式集合

CREATE XML SCHEMA COLLECTION Xmltestschema AS '

<schema xmlns="http://www.w3.org/2001/XMLSchema">

<element name="root">

<complexType>

<sequence>

<element name="StreetName" type="string"/>

<element name="Company" type="string"/>

</sequence>

</complexType>

</element>

</schema>'

xml schema歸檔後，可通過xml_schema_namespace函數取的xml 資料類型執行個體

例

SELECT xml_schema_namespace(N'dbo',N'Xmltestschema')

結果如下

<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"><xsd:element name="root"><xsd:complexType><xsd:complexContent><xsd:restriction base="xsd:anyType"><xsd:sequence><xsd:element name="StreetName" type="xsd:string" /><xsd:element name="Company" type="xsd:string" /></xsd:sequence></xsd:restriction></xsd:complexContent></xsd:complexType></xsd:element></xsd:schema>

  2.xml 模式建立後，可以在建立資料表或聲明xml 變量時使用

例

1) 聲明xml變量

Declare @xml xml(Xmltestschema)

set @xml='<root><StreetName> Zhuhui Road </StreetName><Company>Telecom</Company></root>'

2) 插入資料

Insert into dbo.XmlTest (XmlTestID,XmlTestName,XmlTestMemo)

values ('1','Test01','<root><StreetName> Zhuhui Road </StreetName><Company>Telecom</Company></root>')

三 查詢xml資料

查詢方法如下

1) 序列方法。此方法傳回無類型xml的片段。下例傳回xml列裡的一個值

示例

select XmlTestMemo.query('/root/StreetName') from XmlTest

結果

傳回部分資料集，但是無類型xml的片段

<StreetName>Zhuhui Road</StreetName>

. 

2) 取值方法。類似于查詢方法。唯一的差別是取值方法可以接受額外一個決定傳回資料類型的參數:

例

select XmlTestMemo.value('(/root/StreetName)[1]','varchar(50)') from XmlTest

結果

Zhuhui Road

3)方法三：存在方法。取一個表達式作為輸入，在xml文本中查詢一個特定節點，如存在則傳回1，不存在傳回0。

例

select XmlTestMemo.exist('/root/StreetName') from XmlTest

上述查詢傳回xmltest表中所有含StreetName的行。

結果

1

存在方法可用于where子句

select * from Xmltest

Where XmlTestMemo.exist('/root/StreetName')=1

4)修改方法 表中存儲的xml資料可用如下子方法修改值：

·     INSERT 插入

·     DELETE 删除

·     REPLACE替代

Example:例

① delete方法

declare @x xml

declare @custid int

set @x='<root>

<CompanyDescription CompamyID="001" CompanyName="Telecom">

<Phonenumber>

<WorkPlace>68302239</WorkPlace>

<Mobile>1360613666666</Mobile>

</Phonenumber>

</CompanyDescription>

<CompanyDescription CompamyID="002" CompanyName="Power">

<Phonenumber>

<WorkPlace>69891111</WorkPlace>

<Mobile>13338999909</Mobile>

</Phonenumber>

</CompanyDescription>

</root>'

select @x

結果

<root>

  <CompanyDescription CompamyID="001" CompanyName="Telecom">

    <Phonenumber>

      <WorkPlace>68302239</WorkPlace>

      <Mobile>1360613666666</Mobile>

    </Phonenumber>

  </CompanyDescription>

  <CompanyDescription CompamyID="002" CompanyName="Power">

    <Phonenumber>

      <WorkPlace>69891111</WorkPlace>

      <Mobile>13338999909</Mobile>

    </Phonenumber>

  </CompanyDescription>

</root>

set @x.modify('delete /root/CompanyDescription/@CompamyID')

select @x

結果

<root>

  <CompanyDescription CompanyName="Telecom">

    <Phonenumber>

      <WorkPlace>68302239</WorkPlace>

      <Mobile>1360613666666</Mobile>

    </Phonenumber>

  </CompanyDescription>

  <CompanyDescription CompanyName="Power">

    <Phonenumber>

      <WorkPlace>69891111</WorkPlace>

      <Mobile>13338999909</Mobile>

    </Phonenumber>

  </CompanyDescription>

</root>

②insert 方法  插入xml片段，可使用insert語句

Example:例

DECLARE @x xml      

SET @x = '<Root>      

<CompanyDescription CompanyID="001" CompanyName="Telecom">      

<Phonenumber>       

</Phonenumber>      

</CompanyDescription>      

</Root>'      

SELECT @x   

結果  

<Root>

  <CompanyDescription CompanyID="001" CompanyName="Telecom">

    <Phonenumber />

  </CompanyDescription>

</Root>

SET @x.modify('      

insert <WorkPlac>141717</WorkPlac>

into (/Root/CompanyDescription/Phonenumber)[1]')

SELECT @x

 Result:結果

<Root>

  <CompanyDescription CompanyID="001" CompanyName="Telecom">

    <Phonenumber>

      <WorkPlac>141717</WorkPlac>

    </Phonenumber>

  </CompanyDescription>

</Root>

5)節點方法。節點方法可用來從xml文檔中取得資料生成用于諸如建立新内容或插入建立表格裡的内容的子節點

Example:例

declare @x xml

set @x='<Root><row id="1"><CompanyId>001</CompanyId><CompanyName>Mumbai</CompanyName></row>

<row id="2"><CompanyId>002</CompanyId><CompanyName> Madras </CompanyName></row>

<row id="3"></row></Root>'

Select T.c.query('.') as result

from @x.nodes('/Root/row') T(c)

結果

<row id="1"><CompanyId>001</CompanyId><CompanyName>Mumbai</CompanyName></row>

<row id="2"><CompanyId>002</CompanyId><CompanyName> Madras </CompanyName></row>

<row id="3"></row>

五 xml索引

xml索引可加快xml資料的查詢。Xml索引分為如下類型：

1)主xml索引

2)從xml索引

xml列上的第一個索引必須是主xml索引。有了主xml索引，可使用下列二級索引：path，value和property。取決于查詢的類别，二級索引可能會改善查詢效率。

(一) 主xml索引

 對Xml列的每一個xml二進制對象，索引生成幾行資料。索引中的行數大緻等于xml對象裡的節點數。

 每一行存儲了一下資訊：

1.   元素或屬性名

2.   節點值

3.   節點類型，例如元素節點，屬性節點，或文本節點

4.  由内部節點号代表德文檔順序資訊

5.   從此節點到根節點的路徑。可以用于路徑查詢

6.   基本表的主鍵

示例

Create table dbo.XmlTest(

XmlTestID int,

XmlTestName varchar(50),

XmlTestMemo XML

CONSTRAINT [PK_Testid] PRIMARY KEY CLUSTERED

( XmlTestID ASC

)

)

go

Create Primary XML INDEX Prim_XmlTestMemo ON XmlTest(XmlTestMemo)

(二)從xml索引

為增強查詢效率，可建立二級索引。但必須先建立主索引。以下是從索引的類型：

·     路徑從xml索引。如果查詢是基于路徑，路經二級索引可增加速度。如果你有含exist方法的where子句的查詢，路經二級索引會有幫助。

示例

Create XML INDEX Second_XmlTestMemo ON XmlTest(XmlTestMemo)

USING XML INDEX Prim_XmlTestMemo

FOR PATH

·      值從索引。如果查詢是基于值，且路徑未指定或含通配符，我們可以建基于主索引節點值的二級索引以加快查詢。

Create XML INDEX Value_XmlTestMemo ON XmlTest(XmlTestMemo)

USING XML INDEX Prim_XmlTestMemo

FOR VALUE

·     屬性從索引。從單個xml執行個體傳回一個或多個值的查詢可受益于屬性二級索引。适用于當使用xml類型的value方法獲得對象屬性，且對象的主鍵值已知。

:       

Create XML INDEX Property_XmlTestMemo ON XmlTest(XmlTestMemo)

USING XML INDEX Prim_XmlTestMemo

FOR PROPERTY