數(shù)據(jù)庫基礎(chǔ)與應(yīng)用課件

SQL

Server的XML存儲和查詢

1XML數(shù)據(jù)類型XML數(shù)據(jù)類型SQL增加了數(shù)據(jù)類型:xmlCREATE

TABLE

Student(

Sno

CHAR(7)

PRIMARY

KEY,

Sname

CHAR(8)

NOT

NULL,

Ssex

CHAR(2)

,

Sage

SMALLINT,

Sdept

CHAR(20),

PhoneList

XML);XML數(shù)據(jù)類型INSERT

INTO

Student

VALUES('2000012','王林','男',19,'計(jì)算機(jī)',

'<PhoneList>

<Phone

type="home">12345678</Phone>

<Phone

type="apartment">xxxxxxxx</Phone>

<Phone

type="mobile">133xxxxxxxx</Phone>

<Phone

type="mobile">139xxxxxxxx</Phone>

</PhoneList>');查詢XMLSELECT

PhoneListFROM

StudentWHERE

Sno

=

'2000012'<PhoneList>

<Phone

type="home">12345678</Phone>

<Phone

type="apartment">xxxxxxxx</Phone>

<Phone

type="mobile">133xxxxxxxx</Phone>

<Phone

type="mobile">139xxxxxxxx</Phone></PhoneList>例11.17

查詢學(xué)生2000012的全部電話信息查詢XMLSELECT

PhoneList.query('//Phone[@type="home"]')FROM

StudentWHERE

Sno=

'2000012';<Phone

type="home">12345678</Phone>SELECT

PhoneList.query('//Phone[@type="home"]/text()')

FROM

StudentWHERE

Sno=

'2000012';12345678例11.18

查詢學(xué)號為2000012的學(xué)生的家庭電話查詢XMLSELECT

PhoneList.value('(//Phone[@type="home"]/text())[1]',

'varchar(20)')

FROM

StudentWHERE

Sno=

'2000012';

12345678例11.19

查詢學(xué)號為2000012的學(xué)生的家庭電話例11.20

查詢家庭電話號碼是12345678的學(xué)生的姓名。

SELECT

Sname

FROM

StudentWHERE

PhoneList.value('(//Phone[@type="home"]/text())[1]',

'varchar(20)')='12345678'維護(hù)XML例11.22

刪除學(xué)生王林的所有移動電話號碼UPDATE

Student

SET

PhoneList.modify('delete

//Phone[@type="mobile"]')WHERE

Sname='王林';<PhoneList>

<Phone

type="home">12345678</Phone>

<Phone

type="apartment">xxxxxxxx</Phone></PhoneList><PhoneList>

<Phone

type="home">12345678</Phone>

<Phone

type="apartment">xxxxxxxx</Phone>