mysqlがxmlにサポートする2つの関数extractvalue updatexml
5916 ワード
mysql updatexml extractvalue MySQL 5.1.5バージョンでは、ExtractValue()とUpdateXML()のXMLドキュメントをクエリーおよび変更する関数が追加されました.
link:http://www.blogjava.net/chenpengyi/archive/2006/07/11/57578.html
mysql> create table x(
-> doc varchar(150)
-> );
Query OK, 0 rows affected (0.08 sec)
mysql> insert into x values('
'> </span>A guide to the SQL standard<span class="hljs-xmlDocTag"/></span>
<span class="hljs-comment">'> <span class="hljs-xmlDocTag"><author/></span></span>
<span class="hljs-comment">'> <span class="hljs-xmlDocTag"><initial/></span>CJ<span class="hljs-xmlDocTag"/></span>
<span class="hljs-comment">'> <span class="hljs-xmlDocTag"><surname/></span>Date<span class="hljs-xmlDocTag"/></span>
<span class="hljs-comment">'> <span class="hljs-xmlDocTag"/></span>
<span class="hljs-comment">'> <span class="hljs-xmlDocTag"/></span>
<span class="hljs-comment">'> ');</span>
Query OK, <span class="hljs-number">1</span> row affected (<span class="hljs-number">0.10</span> sec)
mysql> insert <span class="hljs-keyword">into</span> x values(<span class="hljs-comment">'</span>
<span class="hljs-comment">'> <span class="hljs-xmlDocTag"><book/></span></span>
<span class="hljs-comment">'> <span class="hljs-xmlDocTag"><title/></span>SQL:1999<span class="hljs-xmlDocTag"/></span>
<span class="hljs-comment">'> <span class="hljs-xmlDocTag"><author/></span></span>
<span class="hljs-comment">'> <span class="hljs-xmlDocTag"><initial/></span>J<span class="hljs-xmlDocTag"/></span>
<span class="hljs-comment">'> <span class="hljs-xmlDocTag"><surname/></span>Melton<span class="hljs-xmlDocTag"/></span>
<span class="hljs-comment">'> <span class="hljs-xmlDocTag"/></span>
<span class="hljs-comment">'> <span class="hljs-xmlDocTag"/></span>
<span class="hljs-comment">'> ');</span>
Query OK, <span class="hljs-number">1</span> row affected (<span class="hljs-number">0.08</span> sec)
</code></pre>
<p>EXTRACTVALUE (XML_document, XPath_string); <br/> ：XML_document String ， XML ， Doc <br/> ：XPath_string (Xpath ). <br/> ： XML </p>
<pre><code><span class="hljs-header">mysql> select extractvalue(doc,'/book/author/initial') from x;
+------------------------------------------+</span>
<span class="hljs-header">| extractvalue(doc,'/book/author/initial') |
+------------------------------------------+</span>
| CJ |
<span class="hljs-header">| J |
+------------------------------------------+</span>
2 rows in set (0.00 sec)
<span class="hljs-header">mysql> select extractvalue(doc,'/*/*/initial') from x;
+----------------------------------+</span>
<span class="hljs-header">| extractvalue(doc,'/*/*/initial') |
+----------------------------------+</span>
| CJ |
<span class="hljs-header">| J |
+----------------------------------+</span>
2 rows in set (0.00 sec)</code></pre>
<p>updatexml()</p>
<p>UPDATEXML (XML_document, XPath_string, new_value); <br/> ：XML_document String ， XML ， Doc <br/> ：XPath_string (Xpath ) ， Xpath ， 。 <br/> ：new_value，String ， <br/> ： </p>
<pre><code>mysql> update x set doc=updatexml(doc,<span class="hljs-emphasis">'/book/author/initial'</span>,<span class="hljs-emphasis">'!!!'</span>);
Query OK, 2 rows affected (0.08 sec)
Rows matched: 2 Changed: 2 Warnings: 0
<span class="hljs-header">mysql> select * from x;
+------------------------------------------------------------------------</span>
-----------------------------+
| doc
<span class="hljs-header"> |
+------------------------------------------------------------------------</span>
-----------------------------+
|
<book>
<title>A guide to the SQL standard
!!!
Date
|
|
SQL:1999
!!!
Melton
|
+------------------------------------------------------------------------
-----------------------------+
2 rows in set (0.00 sec)link:http://www.blogjava.net/chenpengyi/archive/2006/07/11/57578.html