SqlServer2k，2005 如何读取xml的方法

好久没更新博客了，7月离职去了北京，后来到了国庆进入了世界500企业。☻

工作中要接触Sqlserver，不像之前一直在做Oracle的东西，有些东西得转变思维方式啦。。。

 

SQL Server 2005为XML提供本地支持;现在可以在OPENXML节点中使用一个带有nodes()函数的XML字段类型把一个XML文件转化为一个行集。让我们来看一个使用OPENXML的简单例子，并显示如何把它转化为在SQL Server 2005中使用XML字段类型和nodes()函数。

　　为了使我们的例子简单化，我们将假设我们需要执行某种基于采购订单列表的过程，而这个采购订单将在一个XML文件中指定。该XML文件类似于这样：

 

　　 < polist> 

　　< po>< ponumber>100< /ponumber>< podate>2008-09-10< /podate>< /po>

 

　　< po>< ponumber>101< /ponumber>< podate>2008-09-11< /podate>< /po>

 

　　< /polist>

 

　　我们可以通过下面的OPENXML把 XML转化为一个行集：

 

　　 DECLARE @DocHandle int 

　　DECLARE @XmlDocument nvarchar(1000)

 

　　SET @XmlDocument = N''< polist>

 

　　< po>< ponumber>100< /ponumber>< podate>2008-09-10< /podate>< /po>

 

　　< po>< ponumber>101< /ponumber>< podate>2008-09-11< /podate>< /po>

 

　　< /polist>''

 

　　EXEC sp_xml_preparedocument @DocHandle OUTPUT, @XmlDocument

 

　　SELECT * FROM OPENXML (@DocHandle, ''/polist/po'',2)

 

　　WITH (ponumber nvarchar(10),

 

　　podate datetime)

 

　　EXEC sp_xml_removedocument @DocHandle

 

　　下面是执行以上代码段得到的行集：

 

 

 

　　图一

 

　　这是从上面代码段得到的要点：

 

　　使用OPENXML的第一步是通过访问该存储过程sp_xml_preparedocument来"准备"XML文件，该存储过程将返回一个确认准备好的XML的整数。

 

　　OPENXML是一个行集提供者，这意味着如果它是一张表，那么你同样可以使用它。本质上它是一个带有参数的函数，这些参数将确定该从XML文件中提取什么。

 

　　使用OPENXML的最后步骤是通过访问存储过程sp_xml_removedocument来"释放"准备好的XML文件。

 

上面的xml存储过程使用共同的MSXML解析组件。

 

　　sp_xml_preparedocument存储过程把准备好的XML文件存储在SQL Server的内部高速缓存中。访问sp_xml_removedocument存储过程是很有必要的，这是为了把准备好的XML文件从高速缓存中消除。根据SQL Server 2005联机帮助，能够用于SQL Server的八分之一总内存可以用于MSXML解析器。为了避免使用所有的内存，你应该在你处理XML文件时尽快调用sp_xml_removedocument存储过程。

 

　　上面代码段的SQL Server 2005版本如下：

 

　　 DECLARE @xml xml 

　　SET @xml = N''< polist>

 

　　< po>< ponumber>100< /ponumber>< podate>2008-09-10< /podate>< /po>

 

　　< po>< ponumber>101< /ponumber>< podate>2008-09-11< /podate>< /po>

 

　　< /polist>''

 

　　SELECT

 

　　doc.col.value(''ponumber[1]'', ''nvarchar(10)'') ponumber

 

　　,doc.col.value(''podate[1]'', ''datetime'') podate

 

　　FROM @xml.nodes(''/polist/po'') doc(col)

 

　　下面是从以上代码段中得到的要点：

 

　　我们通过XML字段类型的nodes()函数把XML转换成一个行集。

 

　　nodes() 函数中的doc(col)与表和字段别名一样工作;doc和col的选择没有什么特殊，比如它们都不是保留字。

 

　　该行集和上面使用OPENXML产生的完全一样。

 

　　SQL Server 2005例子的一个细微差异如下图所示：在这个例子中，XML文件具有属性而不是元素：

 

　　 DECLARE @xml xml 

　　SET @xml = N''< polist>

 

　　< po ponumber="100" podate="2008-09-10" />

 

　　< po ponumber="101" podate="2008-09-11" />

 

　　< /polist>''

 

　　SELECT

 

　　doc.col.value(''@ponumber'', ''nvarchar(10)'') ponumber

 

　　,doc.col.value(''@podate'', ''datetime'') podate

 

　　FROM @xml.nodes(''/polist/po'') doc(col)

 

　　你可以通过使用下表符号来指定XML元素(比如ponumber[1]);你也可以通过''@ponumber''符号来指定XML属性。

 

　　至于OPENXML和带有nodes()函数的XML字段类型的性能差异，这里没有一个清晰的答案。如果你扫描SQL Server XML论坛，你会发现在一些线程中，OPENXML比XML字段的nodes()函数要快，反之亦然。一如往常最好的方法会由于个人的情况而有所不同，因此不要盲目选择XML字段类型和nodes()函数而忽视OPENXML。

 

转自csdn博客

 

http://blog.csdn.net/flysun0311/archive/2010/02/04/5287969.aspx