sql语句大全之Sql Server2005 XML体验

当前位置:

首页 > 数据库 > sql数据库 >

sql语句大全之Sql Server2005 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 XmltestschemaAS'

</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的行。

结果

存在方法可用于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>

</Phonenumber>

</CompanyDescription>

</Phonenumber>

</CompanyDescription>

</root>'

select @x

结果

<root>

</Phonenumber>

</CompanyDescription>

</Phonenumber>

</CompanyDescription>

</root>

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

select @x

结果

<root>

</Phonenumber>

</CompanyDescription>

</Phonenumber>

</CompanyDescription>

</root>

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

Example:例

DECLARE @x xml

SET @x = '<Root>

</Phonenumber>

</CompanyDescription>

</Root>'

SELECT @x

结果

<Root>

</CompanyDescription>

</Root>

SET @x.modify('

insert <WorkPlac>141717</WorkPlac>

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

SELECT @x

Result:结果

<Root>

</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>

五 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

)

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

---------------------

作者：happydreamer

来源：CSDN

原文：https://blog.csdn.net/happydreamer/article/details/1899546

栏目列表