65.9K
CodeProject 正在变化。 阅读更多。
Home

MySQL XML 用户定义函数

BarrySolomon
emptyStarIconemptyStarIconemptyStarIconemptyStarIconemptyStarIcon

0/5 (0投票)

2010 年 4 月 7 日

CPOL

3分钟阅读

viewsIcon

37357

downloadIcon

697

支持在 MySQL 中读取 XML

引言

当前，MySQL 仅提供非常基础的读取功能，即 ExtractValue()。 它仅返回一个没有分隔符的节点值串联 string，并且没有能力返回目标节点的实际子节点树。 因此，我为 MySQL 创建了几个用于读取 XML 的 UDF。

  • MyXml_InnerXml
    • 从 XML 片段中检索内部 XML
    • 如果 XPath 目标是属性值，这也将返回该属性值
  • MyXml_OuterXml
    • 从 XML 片段中检索完整的外部 XML
    • 如果 XPath 目标是属性值，这也将返回该属性值
  • MyXml_XQuery
    • 目前，这仅支持 XQuery 计数，因为这是我当时需要的

背景

当在 MySQL 中使用存储过程时，只有一种选择用于读取 XML，即 ExtractValue()。

MySQL 的 ExtractValue() 函数充其量是有限的，因为它只读取节点值，而不返回 XML 子节点。 经过大量搜索，我找不到任何 UDF 可以像 InnerXml 和 OuterXml 那样返回 XML 片段。 此外，没有办法计算用于循环目的的 XPath 查询的节点数。

Using the Code

根据您的 MySQL 版本，用于 UDF 的外部 DLL 应该放在 lib/plugins 或 bin 目录中。 将 MyXml_Functions.dll 放在那里，将 libxml2.dll 放在 bin 目录中，然后重新启动 MySQL。

要确定放置 MyXml_Functions.dll 的位置，请在 SQLYog 中运行以下命令。

SHOW VARIABLES LIKE 'plugin_dir'

创建/添加 UDF 到 MySQL

CREATE FUNCTION MyXml_InnerXml RETURNS STRING SONAME 'MyXml_Functions.dll';
CREATE FUNCTION MyXml_OuterXml RETURNS STRING SONAME 'MyXml_Functions.dll';
CREATE FUNCTION MyXml_XQuery   RETURNS STRING SONAME 'MyXml_Functions.dll';

从 MySQL 中删除 UDF

DROP FUNCTION MyXml_InnerXml;
DROP FUNCTION MyXml_OuterXml;
DROP FUNCTION MyXml_XQuery;

验证 UDF 是否已正确加载，请尝试以下任何/所有示例

SELECT MyXml_InnerXml( "<Children><Child Age='7'>Nicole</Child>_
	<Child Age='4'>Aaron</Child></Children>", "//Children");
SELECT MyXml_OuterXml( "<Children><Child Age='7'>Nicole</Child>_
	<Child Age='4'>Aaron</Child></Children>", "//Children");
SELECT MyXml_XQuery(   "<Children><Child Age='7'>Nicole</Child>_
	<Child Age='4'>Aaron</Child></Children>", "count(//Children/node())");
SELECT MyXml_XQuery(   "<Children><Child Age='7'>Nicole</Child>_
	<Child Age='4'>Aaron</Child></Children>", "count(//Children/Child/node())");

SELECT MyXml_InnerXml( "<Children><Child Age='7'>Nicole</Child>_
	<Child Age='4'>Aaron</Child></Children>", "//Children/Child");
SELECT MyXml_InnerXml( "<Children><Child Age='7'>Nicole</Child>_
	<Child Age='4'>Aaron</Child></Children>", "//Children/Child/@Age");
SELECT MyXml_InnerXml( "<Children><Child Age='7'>Nicole</Child>_
	<Child Age='4'>Aaron</Child></Children>", "//Children/Child[1]");
SELECT MyXml_InnerXml( "<Children><Child Age='7'>Nicole</Child>_
	<Child Age='4'>Aaron</Child></Children>", "//Children/Child[2]");

SELECT MyXml_OuterXml( "<Children><Child Age='7'>Nicole</Child>_
	<Child Age='4'>Aaron</Child></Children>", "//Children/Child");
SELECT MyXml_OuterXml( "<Children><Child Age='7'>Nicole</Child>_
	<Child Age='4'>Aaron</Child></Children>", "//Children/Child[2]/@Age");
SELECT MyXml_OuterXml( "<Children><Child Age='7'>Nicole</Child>_
	<Child Age='4'>Aaron</Child></Children>", "//Children/Child[1]");
SELECT MyXml_OuterXml( "<Children><Child Age='7'>Nicole</Child>_
	<Child Age='4'>Aaron</Child></Children>", "//Children/Child[2]");

我用于测试的一些错误处理示例是

SELECT MyXml_InnerXml( "<Children><Child Age='7'>Nicole</Child>_
	<Child Age='4'>Aaron</Child></Children>", NULL);
SELECT MyXml_InnerXml( NULL, "//Children/Child[2]/@Age");
SELECT MyXml_InnerXml( NULL, NULL);

SELECT MyXml_OuterXml( "<Children><Child Age='7'>Nicole</Child>_
	<Child Age='4'>Aaron</Child></Children>", NULL);
SELECT MyXml_OuterXml( NULL, "//Children/Child[2]/@Age");
SELECT MyXml_OuterXml( NULL, NULL);

SELECT MyXml_InnerXml( 1, "<Children><Child Age='7'>Nicole</Child>_
	<Child Age='4'>Aaron</Child></Children>");
SELECT MyXml_InnerXml( 2, 1);
SELECT MyXml_InnerXml( "//Children/Child[2]/@Age", 1);

SELECT MyXml_OuterXml( 1, "<Children><Child Age='7'>Nicole</Child>_
	<Child Age='4'>Aaron</Child></Children>");
SELECT MyXml_OuterXml( 2, 1);
SELECT MyXml_OuterXml( "//Children/Child[2]/@Age", 1);

遍历 XML 片段

以下代码是我编写的存储过程，它遍历 XML 片段，理论上在线作用于所有或第 nth 个节点。 此示例仅从 XPath "//Children/Child" 返回一个 CSV 字符串。 这个存储过程可以很容易地被修改为接受一个 XPath 参数。 我使用数百个类似于此的函数，用于使用 XML 片段和 ifnull() 构造执行 CRUD 操作。

DELIMITER $$

DROP PROCEDURE IF EXISTS `MyXml_XmlWalker`$$

CREATE PROCEDURE `MyXml_XmlWalker`(IN testXml TEXT, IN verbose BIT, _
	OUT children VARCHAR(1024))
BEGIN
/*
	SET @testXml = CONCAT(
		"<Children><Child Age='7'>Nicole</Child>_
			<Child Age='4'>Aaron</Child></Children>"
	);
		
	CALL MyXml_XmlWalker( @testXml, 1, @children); select @children;
*/
   	DECLARE _child_age 	INT(11);
	DECLARE _child_name	VARCHAR(100);
	
   	DECLARE v_row_count 	INT UNSIGNED;
    	DECLARE _column_id 	INT(11) DEFAULT 1;
	
    	IF (verbose = 1) THEN
		SELECT 'MyXml_XmlWalker', testXml;
     	END IF;
     	
     	SET children = NULL;
     	
     	IF (testXml IS NOT NULL) THEN

		-- Get child count
		-- 
		SET v_row_count := MyXml_XQuery_
			( testXml, 'count(//Children/Child/node())');
		IF (verbose = 1) THEN
			SELECT 'MyXml_XmlWalker', v_row_count;
		END IF;
		
		-- Extract child definitions
		-- 
		WHILE _column_id <= v_row_count DO
	 
			SELECT MyXml_InnerXml_
			( testXml, CONCAT('//Children/Child[', _column_id, ']')) 
			INTO _child_name;
	
			IF (verbose = 1) THEN
				SELECT 'MyXml_XmlWalker', _child_name;
			END IF;
					
			IF (_child_name IS NOT NULL) THEN
				
				IF (children IS NULL) THEN
					SET children = _child_name;
				ELSE
					SET children = CONCAT_
					( children, ', ', _child_name);
				END IF;
				
			END IF;
			
			SET _column_id = _column_id + 1;
				
		END WHILE;	
	
	END IF;	

    END$$

DELIMITER ;

关注点

这些 UDF 依赖于 libXml2，并且已针对 libxml2-2.4.12 构建。 我已经包含了一个精简版本（为了使下载更小，并且仍然允许 VS2008 项目构建）。

虽然我已经尽力了，但还是存在一个我无法解决的多线程问题。 当我在高负载下从两个 SQLYog 会话运行这些函数时，MySQL 有时会崩溃。 我在 libXml2 库中发现一条注释，说它的 XPath 支持应该是线程安全的。“应该”这个词很有意思。 显然，它并不像它应该的那样健壮。 如果有人能弄清楚是怎么回事并告诉我，我将非常感激。

我使用了一些来自 libXml2 源代码的示例代码。 我保留了来自我用来创建此代码的 libXml2 示例的头文件定义等等，因此理论上这些 UDF 的代码应该在 Linux 下编译。 如果有人愿意这样做，我将非常感谢。

历史

  • 2010 年 4 月 7 日：首次提交
© . All rights reserved.