管理XML数据(4)

Soft drinks, coffees, teas, beers, and ales

Condiments

Sweet and savory sauces, relishes, spreads, and seasonings

Confections

Desserts, candies, and sweet breads

Dairy Products Cheeses 代码;

SET @tmpXML.modify(

'insert

Confections

Desserts, candies, and sweet breads

after (/Categories/CategoryInfo[2])[1]') 3、编写脚本，插入节点，结果如下

Beverages

Soft drinks, coffees, teas, beers, and ales

Condiments

Sweet and savory sauces, relishes, spreads, and seasonings

Confections

Desserts, candies, and sweet breads

Dairy Products

Cheeses 代码：

SET @tmpXML.modify(

'insert Beverages as first

into (/Categories/CategoryInfo[1])[1]') 4、使用如下脚本创建表T，并输入数据。 DECLARE @tmpXML AS XML SET @tmpXML = '

Soft drinks, coffees, teas, beers, and ales

Condiments

Sweet and savory sauces, relishes, spreads, and seasonings

Dairy Products Cheeses '

CREATE TABLE T (i int, x xml); INSERT INTO T VALUES(1,@tmpXML)

5、修改表T中xml数据，增加节点，结果同2。 代码： UPDATE T

SET x.modify('

insert

Confections

Desserts, candies, and sweet breads

after (/Categories/CategoryInfo[2])[1] ')

6、定义如下变量

DECLARE @tmpXML AS XML SET @tmpXML = '

Beverages

Soft drinks, coffees, teas, beers, and ales

Condiments

Sweet and savory sauces, relishes, spreads, and seasonings

Confections

Desserts, candies, and sweet breads

Dairy Products Cheeses '

7、编写脚本，删除节点，结果如下

Beverages

Soft drinks, coffees, teas, beers, and ales

Condiments

Sweet and savory sauces, relishes, spreads, and seasonings

Dairy Products Cheeses 代码:

SET @tmpXML.modify('

delete /Categories/CategoryInfo[3] ')

8、编写脚本，修改表T中xml数据，删除第5步中增加的节点。 代码： UPDATE T

SET x.modify('

delete /Categories/CategoryInfo[3] ')

9、将第6步中定义的变量CategoryID =\改为CategoryID =\。 代码：

SET @tmpXML.modify('

replace value of(/Categories/CategoryInfo[4]/@CategoryID)[1] with 14 ')

练习5：将XML数据转换为关系型数据

1、 有如下xml文档

Beverages

Soft drinks, coffees, teas, beers, and ales

Condiments

Sweet and savory sauces, relishes, spreads, and seasonings

Confections

Desserts, candies, and sweet breads

Dairy Products Cheeses

2、 使用OPENXML的隐式映射，将其转换为如下关系型数据

代码：

DECLARE @doc VARCHAR(1000) SET @doc='

Beverages

Soft drinks, coffees, teas, beers, and ales

Condiments

Sweet and savory sauces, relishes, spreads, and seasonings

Confections

Desserts, candies, and sweet breads

Dairy Products Cheeses ' DECLARE @idoc int

EXEC sp_xml_preparedocument @idoc OUTPUT,@doc SELECT * FROM

OPENXML(@idoc,'/Categories/CategoryInfo',2) WITH

(CategoryName VARCHAR(100), [Description] VARCHAR(100) )

3、 使用OPENXML的显式映射，将其转换为如下关系型数据。

代码：

DECLARE @doc VARCHAR(1000) SET @doc='

Beverages

Soft drinks, coffees, teas, beers, and ales

Condiments

Sweet and savory sauces, relishes, spreads, and

seasonings

Confections

Desserts, candies, and sweet breads

Dairy Products Cheeses ' DECLARE @idoc int

EXEC sp_xml_preparedocument @idoc OUTPUT,@doc SELECT * FROM

OPENXML(@idoc,'/Categories/CategoryInfo',2) WITH (

CategoryID int '@CategoryID',

CategoryName VARCHAR(100) 'CategoryName', [Description] VARCHAR(100) 'Description' )