6.3 把XML数据插入到sql Server数据库的表中

我们可以通过使用系统存储过程sp_xml_preparedocument的OPENXML函数把XML文档中的数据插入数据库中。其中，系统存储过程sp_xml_preparedocument用来创建一个能被插入数据库的XML文档的内部表示，该存储过程返回一个可以访问XML文档内部表示的句柄，另一方面，系统存储过程sp_xml_removedocument可以用来删除XML文档的内部表示。

系统存储过程sp_xml_preparedocument的语法如下：

   
   

    
    
sp_xml_preparedocumenthandleddocOUTPUT，xmltext
   
   

其中：

●handleddoc代表XML文档句柄的整数值。

●xmltext代表原始的XML文档的文本值。

系统存储过程sp_xml_removedocument的语法如下：

    
    
sp_xml_removedocumenthandleddoc
   
   

其中：

handleddoc代表XML文档句柄的整数值。

上述这两个存储过程都可以使用函数OPENXML，函数OPENXML的语法如下：

    
    
OPENXML(handleddoc，rowpattern，flagvalue) 
    
    
Withtablename
   
   

其中：

●handleddoc代表XML文档句柄的整数值。

●rowpattern代表来识别XML文档的节点XPath模式的可变长字符串的值。

●flagvalue代表XML数据和相关的行集之间映射的整数值。如果值为1表示要对数据库中的字段做基于属性的映射；如果值为2表示要对数据库中的字段做基于元素的映射。

●tablename代表数据库中的表名。

系统存储过程sp_xml_preparedocument读入XML文档内的文本并用MSXML解析器进行处理。处理以后，XML文档以带有元素、属性和文本的树型结构显示。OPENXML函数应用该树型结构并生成包含XML文档所有部分的行集。使用OPENXML和INSERT语句，即可以将行集中的数据插入到表中。下面通过实例进行讲解。

实例6-1 以属性的形式将XML数据插入到sql Server数据库的表中

(1) 在sql Server查询分析器窗口中输入以下代码：

    
    
USEschool 
    
    
LECT*FROMstudent
   
   

运行后，先来查看插入之前student表中的数据，如图6-1所示。

(2) 重新输入以下代码：

    
    
1USEschool 
    
    
2DECLARE@docvarchar(1000) 
    
    
3DECLARE@idocint
    
    
4SET@doc='<ROOT> 
    
    
5<studentid="5"name="cathy"
    
    
6sex="female"age="21"> 
    
    
7</student> 
    
    
8</ROOT>' 
    
    
9execsp_xml_preparedocument@idocoutput，@doc 
    
    
10select*fromopenxml(@idoc，'/ROOT/student'，1) 
    
    
11with(idint，namevarchar(40)，sexvarchar(20)， 
    
    
12ageint) 
    
    
13insertstudent 
    
    
14fromopenxml(@idoc，'/ROOT/student') 
    
    
15withstudent 
    
    
16execsp_xml_removedocument@idoc
   
   

在上面代码的第4～8行创建了一个变量@doc并把XML数据存放在该变量中。然后，第9行执行系统存储过程sp_xml_preparedocument，来创建一个能把数据插入数据库的XML文档内部表示，该系统存储过程返回一个保存在变量@idoc中的句柄，可以用该句柄访问XML文档的内部表示。第10行使用一个select语句访问XML文档的内部表示，并把该文档中的所有数据显示出来。

(3) 运行程序，结果如图6-2所示。

(4) 重新输入以下代码。

    
    
USEschool 
    
    
SELECT*FROMstudent
   
   

运行后，查看插入之后student表中的数据，如图6-3所示。

通过图6-3可以清楚地看到，XML数据已经被插入到了数据库中。

实例6-2 以元素的形式将XML数据插入到sql Server数据库的表中

接着上一个实例，我们继续进行数据的插入，不过这次是以元素的形式进行插入操作的。

首先在查询窗口中输入以下代码：

    
    
1DECLARE@docvarchar(1000) 
    
    
2int
    
    
3SET@doc='<ROOT> 
    
    
4<student> 
    
    
5<id>6</id> 
    
    
6<name>sun</name> 
    
    
7<sex>male</sex> 
    
    
8<age>24</age> 
    
    
9</student> 
    
    
10</ROOT>' 
    
    
11output，@doc 
    
    
12fromopenxml(@idoc，'/ROOT/student',2) 
    
    
13varchar(20)， 
    
    
14ageint) 
    
    
15insertstudent 
    
    
16fromopenxml(@idoc，'/ROOT/student'，2) 
    
    
17withstudent 
    
    
18execsp_xml_removedocument@idoc
   
   

在上面代码的第3～10行创建了一个变量@doc，并把XML数据存放在该变量中。然后，第11行执行系统存储过程sp_xml_preparedocument，来创建一个能把数据插入数据库的XML文档的内部表示，该系统存储过程返回一个保存在变量@idoc中的句柄，可以用该句柄访问XML文档的内部表示。第12行使用一个select语句访问XML文档的内部表示，并把该文档中的所有数据显示出来了。该段代码与实例6-1中代码的唯一区别就在于变量中存放的XML数据是以元素的形式出现的，而不是属性的形式。

运行程序，结果如图6-4所示。

重新输入以下代码：

FROMstudent
   
   

运行后，查看插入之后student表中的数据，如图6-5所示。

可以清楚地看到，XML数据已经被插入到了数据库中。

通过上述实例可以看到，在sql Server 2005 中，OPENXML的功能得到了增强，它可以将XML类型数据传递到sp_xml_preparedocument存储过程中，并且可以在WITH子句中使用新的数据类型。