blocks|key|3191915|text|您可能需要重新考虑您的数据库设计。关系数据库系统不支持一个字段中的多个值。ExtractValue是用来从字段中的XML数据中获取一个值的过滤器，而不是用来获取多行数据的。|type|unstyled|depth|inlineStyleRanges|entityRanges|offset|length|data|3191916|您应该阅读有关数据库规范化的知识。所有表格应至少遵循first+normal+form+(1NF)，即每个字段中只有一个值。不满足1NF的表通常很难查询，因为一般的SQL和诸如MySQL之类的实现不会为您提供任何用于此类查询的好工具。|style|BOLD|3191917|如果您仍然想处理您的非1NF表，我认为您可能需要从数据库中提取数据，并在应用程序代码中完成此工作。|3191918|entityMap|0|LINK|mutability|MUTABLE|url|https://dev.mysql.com/doc/refman/5.6/en/xml-functions.html#function_extractvalue^0|11|C|0|0|Q|H|0|0^^$0|@$1|2|3|4|5|6|7|R|8|@]|9|@$A|S|B|T|1|U]]|C|$]]|$1|D|3|E|5|6|7|V|8|@$A|W|B|X|F|G]]|9|@]|C|$]]|$1|H|3|I|5|6|7|Y|8|@]|9|@]|C|$]]|$1|J|3|-4|5|6|7|Z|8|@]|9|@]|C|$]]]|K|$L|$5|M|N|O|C|$P|Q]]]]

You may need to rethink your database design. Relational database systems are not built to support multiple values in one field. <a href="https://dev.mysql.com/doc/refman/5.6/en/xml-functions.html#function_extractvalue" rel="nofollow">ExtractValue</a> is meant as a filter to fetch one value from XML data in a field, not for getting multiple rows of data.

You should read up on database normalization. All tables should at least adhere to the first normal form (1NF), i.e., only one value in each field. Tables which do not fulfill 1NF is usually pretty hard to query because SQL in general and implementations such as MySQL in particular does not give you any good tools for such querying.

If you still want to struggle along with your non-1NF table I think you may need to fetch your data out of the database and do the work in your application code.

blocks|key|1786817|text|问题的解决方案需要使用numbers表:一个由整数组成的表，1,2,3，...直到某个合理的值，比如1024。|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|1786818|然后，您可以使用String+Walking来解决问题。|1786819|下面是numbers表的CREATE+TABLE语句：|1786820|CREATE+TABLE+numbers+(
++`n`+smallint+unsigned+NOT+NULL+AUTO_INCREMENT,
++PRIMARY+KEY+(`n`)
)
;
INSERT+INTO+numbers+VALUES+(NULL);
INSERT+INTO+numbers+SELECT+NULL+FROM+numbers;
INSERT+INTO+numbers+SELECT+NULL+FROM+numbers;
INSERT+INTO+numbers+SELECT+NULL+FROM+numbers;
INSERT+INTO+numbers+SELECT+NULL+FROM+numbers;
INSERT+INTO+numbers+SELECT+NULL+FROM+numbers;
INSERT+INTO+numbers+SELECT+NULL+FROM+numbers;
INSERT+INTO+numbers+SELECT+NULL+FROM+numbers;
INSERT+INTO+numbers+SELECT+NULL+FROM+numbers;
INSERT+INTO+numbers+SELECT+NULL+FROM+numbers;
INSERT+INTO+numbers+SELECT+NULL+FROM+numbers;|code-block|syntax|javascript|1786821|上面的代码用值1..1024填充|1786822|现在是查询：|1786823|SELECT+
++SUBSTRING_INDEX(SUBSTRING_INDEX(ExtractValue(@XML,+'/As/A/B'),+'+',+n),+'+',+-1)+AS+value
FROM
++numbers
WHERE
++n+BETWEEN+1+AND+ExtractValue(@XML,+'count(/As/A/B)')
;


%2B-------%2B
%7C+value+%7C
%2B-------%2B
%7C+Chan++%7C
%7C+Shey++%7C
%7C+Bob+++%7C
%2B-------%2B
3+rows+in+set+(0.02+sec)|1786824|我们使用ExtractValue(@XML,+'count(/As/A/B)')来获得XML3XML值--匹配的元素的数量。|BOLD|1786825|通过数字1，2，3，我们从文本CHAN+SHEY+BOB中提取令牌#1，令牌#2，令牌#3，按空格拆分。|1786826|备注：|1786827|1786828|ExtractXML返回空格分隔的值。但是如果返回的文本中有一个空格，那就没用了。它与分隔空格没有区别。|unordered-list-item|1786829|可以避免创建numbers表和generate+the+numbers+on+the+fly。我建议不要这样做--这会产生很多开销。有一个1024行的表格总是很好的。|1786830|1786831|祝好运!|1786832|entityMap|0|LINK|mutability|MUTABLE|url|http://code.openark.org/blog/mysql/unwalking-a-string-with-group_concat|1|http://code.openark.org/blog/mysql/generating-numbers-out-of-seemingly-thin-air^0|B|7|0|8|E|0|0|3|7|0|0|0|0|0|4|10|1K|6|0|0|1G|F|D|0|0|3|0|0|0|1G|0|0|2B|F|V|1|0|0|0|4|0^^$0|@$1|2|3|4|5|6|7|1K|8|@$9|1L|A|1M|B|C]]|D|@]|E|$]]|$1|F|3|G|5|6|7|1N|8|@]|D|@$9|1O|A|1P|1|1Q]]|E|$]]|$1|H|3|I|5|6|7|1R|8|@$9|1S|A|1T|B|C]]|D|@]|E|$]]|$1|J|3|K|5|L|7|1U|8|@]|D|@]|E|$M|N]]|$1|O|3|P|5|6|7|1V|8|@]|D|@]|E|$]]|$1|Q|3|R|5|6|7|1W|8|@]|D|@]|E|$]]|$1|S|3|T|5|L|7|1X|8|@]|D|@]|E|$M|N]]|$1|U|3|V|5|6|7|1Y|8|@$9|1Z|A|20|B|C]|$9|21|A|22|B|W]]|D|@]|E|$]]|$1|X|3|Y|5|6|7|23|8|@$9|24|A|25|B|W]|$9|26|A|27|B|C]]|D|@]|E|$]]|$1|Z|3|10|5|6|7|28|8|@$9|29|A|2A|B|W]]|D|@]|E|$]]|$1|11|3|-4|5|6|7|2B|8|@]|D|@]|E|$]]|$1|12|3|13|5|14|7|2C|8|@$9|2D|A|2E|B|W]]|D|@]|E|$]]|$1|15|3|16|5|14|7|2F|8|@$9|2G|A|2H|B|W]]|D|@$9|2I|A|2J|1|2K]]|E|$]]|$1|17|3|-4|5|6|7|2L|8|@]|D|@]|E|$]]|$1|18|3|19|5|6|7|2M|8|@$9|2N|A|2O|B|W]]|D|@]|E|$]]|$1|1A|3|-4|5|6|7|2P|8|@]|D|@]|E|$]]]|1B|$1C|$5|1D|1E|1F|E|$1G|1H]]|1I|$5|1D|1E|1F|E|$1G|1J]]]]

A solution to your problem would require the usage of a <code>numbers</code> table: a table of integers, 1,2,3,.... up to some reasonable value, say 1024.

You would then use <a href="http://code.openark.org/blog/mysql/unwalking-a-string-with-group_concat" rel="nofollow">String Walking</a> to solve the problem.

Here is the CREATE TABLE statement for the <code>numbers</code> table:

<pre><code>CREATE TABLE numbers (
 `n` smallint unsigned NOT NULL AUTO_INCREMENT,
 PRIMARY KEY (`n`)
)
;
INSERT INTO numbers VALUES (NULL);
INSERT INTO numbers SELECT NULL FROM numbers;
INSERT INTO numbers SELECT NULL FROM numbers;
INSERT INTO numbers SELECT NULL FROM numbers;
INSERT INTO numbers SELECT NULL FROM numbers;
INSERT INTO numbers SELECT NULL FROM numbers;
INSERT INTO numbers SELECT NULL FROM numbers;
INSERT INTO numbers SELECT NULL FROM numbers;
INSERT INTO numbers SELECT NULL FROM numbers;
INSERT INTO numbers SELECT NULL FROM numbers;
INSERT INTO numbers SELECT NULL FROM numbers;
</code></pre>

The above populates with values 1..1024

And now the query:

<pre><code>SELECT 
 SUBSTRING_INDEX(SUBSTRING_INDEX(ExtractValue(@XML, '/As/A/B'), ' ', n), ' ', -1) AS value
FROM
 numbers
WHERE
 n BETWEEN 1 AND ExtractValue(@XML, 'count(/As/A/B)')
;


+-------+
| value |
+-------+
| Chan |
| Shey |
| Bob |
+-------+
3 rows in set (0.02 sec)
</code></pre>

We use <code>ExtractValue(@XML, 'count(/As/A/B)')</code> to get the value 3 -- the number of matching XML elements.

Running through numbers 1, 2, 3, we extract token #1, token #2, token #3 from the text <code>CHAN SHEY BOB</code>, splitting by space.

Notes:

<ul>
<li>ExtractXML returns values space delimited. But if there's a space within the returned text - no go. It would be indistinguishable from the delimiting spaces.</li>
<li>It is possible to avoid creating the numbers table and <a href="http://code.openark.org/blog/mysql/generating-numbers-out-of-seemingly-thin-air" rel="nofollow">generate the numbers on the fly</a>. I advise against -- it would create a lot of overhead. Having a 1024 row numbers table is always nice to have.</li>
</ul>

Good luck!

blocks|key|1467290|text|这个问题的答案如下：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1467291|Parse+an+XML+string+in+MySQL|offset|length|1467292|如果您将子代更改为“B”，则该文章中的解决方案应该有效：|1467293|DECLARE+i+INT+DEFAULT+1;
DECLARE+count+DEFAULT+ExtractValue(xml,+'count(//child)');

WHILE+i+<=+count+DO
SELECT+ExtractValue(xml,+'//child[$i]');
SET+i+=+i%2B1;
END+WHILE|code-block|syntax|javascript|1467294|entityMap|0|LINK|mutability|MUTABLE|url|https://stackoverflow.com/questions/902678/parse-an-xml-string-in-mysql^0|0|0|S|0|0|0|0^^$0|@$1|2|3|4|5|6|7|U|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|V|8|@]|9|@$D|W|E|X|1|Y]]|A|$]]|$1|F|3|G|5|6|7|Z|8|@]|9|@]|A|$]]|$1|H|3|I|5|J|7|10|8|@]|9|@]|A|$K|L]]|$1|M|3|-4|5|6|7|11|8|@]|9|@]|A|$]]]|N|$O|$5|P|Q|R|A|$S|T]]]]

This problem was answered here:

<a href="https://stackoverflow.com/questions/902678/parse-an-xml-string-in-mysql">Parse an XML string in MySQL</a>

The solution from that article should workd if you change child to "B":

<pre><code>DECLARE i INT DEFAULT 1;
DECLARE count DEFAULT ExtractValue(xml, 'count(//child)');

WHILE i &lt;= count DO
SELECT ExtractValue(xml, '//child[$i]');
SET i = i+1;
END WHILE
</code></pre>

I kind of having problems with ExtractValue Function in MySQL.

Here is my sample XML:

<pre><code>&lt;As&gt;
 &lt;A&gt;
 &lt;B&gt;Chan&lt;/B&gt;
 &lt;/A&gt;
 &lt;A&gt;
 &lt;B&gt;Shey&lt;/B&gt;
 &lt;/A&gt;
 &lt;A&gt;
 &lt;B&gt;Bob&lt;/B&gt;
 &lt;/A&gt;
&lt;/As&gt;
</code></pre>

Here is my current query:

<pre><code>SELECT ExtractValue(@XML, '/As/A/B')
</code></pre>

Here is the result:

<pre><code>CHAN SHEY BOB
</code></pre>

Here is what i want:

<pre><code>CHAN
SHEY
BOB
</code></pre>

Can someone please help me achieve this.. thanks.

MySQL XML Querying

翻译质量差，导致语言生硬或混乱。

没有提供实际的解决方法或示例。

解答不清晰，无法理解或解决问题。

页面排版不美观，阅读体验差。

文章

问答

视频

学习中心

腾讯云实验室

直播

竞赛

腾讯云代码分析专区

腾讯iOA零信任安全管理系统专区

腾讯云架构师技术同盟交流圈

腾讯云数据库专区

腾讯云顾问专区

腾讯云原生专区

腾讯混元专区

腾讯云TCE专区

腾讯云Lighthouse专区

腾讯云HAI专区

腾讯云Edgeone专区

腾讯云存储专区

腾讯云智能专区

腾讯轻联专区 

腾讯云开发专区

TAPD专区

腾讯轻量云游戏服专区

腾讯云最具价值专家

腾讯云架构师技术同盟

腾讯云创作之星

腾讯云开发者先锋

腾讯云代码助手

云原生构建

TAPD 敏捷项目管理

Cloud Studio

SDK中心

API中心

命令行工具

涵盖代码开发、场景应用、自动测试全流程，助你从零构建专属AI助手

一站式MCP教程库，解锁AI应用新玩法

我对MySQL中的ExtractValue函数有点问题。下面是我的示例XML：<As> <A> Chan </A> <A> Shey </A> <A> Bob </A></As>以下是我当前的查询：S...

问MySQL XML查询
EN

Stack Overflow用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问MySQL XML查询EN