在 Oracle SQL 中选择 xpath 值作为单独的行
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/16735885/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Select xpath values as separate rows in Oracle SQL
提问by lbalazscs
I need to select some values from an XML stored in a CLOB column in an Oracle database. The best I could come up with is the following:
我需要从存储在 Oracle 数据库的 CLOB 列中的 XML 中选择一些值。我能想到的最好的方法如下：
select extract(xmltype(COLUMN), 'xpath-expression').getStringVal() as XMLVAL from TABLE t;
The problem is that when the XPATH selects multiple nodes, the values are concatenated. I need to have each selected node on a separate row. Obviously the concatenation must occur in getStringVal(), I use that because I need to have strings in my client (not XMLType). What should I use instead of getStringVal()?
问题是当 XPATH 选择多个节点时，这些值会被连接起来。我需要将每个选定的节点放在单独的行上。显然连接必须出现在 getStringVal() 中，我使用它是因为我需要在我的客户端中有字符串（而不是 XMLType）。我应该使用什么来代替 getStringVal()？
EDIT: note that there is a similar question here: Oracle Pl/SQL: Loop through XMLTYPE nodes- but I couldn't apply it to my case. It uses two different XPATH expressions, and the principle of separation is not clear.
编辑：请注意，这里有一个类似的问题：Oracle Pl/SQL: Loop through XMLTYPE nodes- 但我无法将其应用于我的案例。它使用了两种不同的XPATH表达式，分离的原理不清楚。
EDIT2: The XML is very complex, but basically I need to find the "some value" entries in
EDIT2：XML 非常复杂，但基本上我需要在
<string name="SOME_KEY" value="some value"/>
elements that are burried under many other elements. I use the XPATH //*[@name="SOME_KEY"]/@valueand it finds successfully the value attribute of all the XML elements that have a SOME_KEY attribute.
隐藏在许多其他元素之下的元素。我使用 XPATH //*[@name="SOME_KEY"]/@value，它成功地找到了所有具有 SOME_KEY 属性的 XML 元素的 value 属性。
采纳答案by Noel
Try this.
尝试这个。
SELECT EXTRACTVALUE (x.COLUMN_VALUE, 'xpath-expression')
FROM TABLE (
SELECT XMLSEQUENCE (
xmltype (column).EXTRACT ('xpath-expression'))
FROM t) x;
Sample at http://sqlfiddle.com/#!4/87af2/1
回答by Rubenisme
I had close to the same thing, but it didn't quite work with "Eat A Peach"'s answer. I had something like the following in a column as xmltype.
我有接近同样的事情，但它并不完全适用于“吃桃子”的答案。我在 xmltype 一栏中有类似以下内容。
<?xml version="1.0" encoding="UTF-8"?>
<serviceRequestAnswer xmlns:fo="http://www.w3.org/1999/XSL/Format" xmlns:xs="http://www.w3.org/2001/XMLSchema" xmlns:fn="http://www.w3.org/2005/xpath-functions" xmlns:ns2="http://www.something.something/bla/v1">
<Persons>
<Person>
<InternalIdNumber>2935612467</InternalIdNumber>
<PublicIdNumber>9871256327</PublicIdNumber>
<FirstNames>Remy</FirstNames>
<LastName>Smith</LastName>
<BirthName>Smith</BirthName>
<BirthDate>19900101</BirthDate>
<PlaceOfBirth>0209</PlaceOfBirth>
<CountryOfBirth>6030</CountryOfBirth>
<Sex>M</Sex>
<Nationality>0001</Nationality>
</Person>
<Person>
<InternalIdNumber>7163584061</InternalIdNumber>
<PublicIdNumber>123432678</PublicIdNumber>
<FirstNames>Jesse</FirstNames>
<LastName>Smith</LastName>
<BirthName>Smith</BirthName>
<BirthDate>19900101</BirthDate>
<PlaceOfBirth>0012</PlaceOfBirth>
<CountryOfBirth>6030</CountryOfBirth>
<Sex>M</Sex>
<Nationality>0001</Nationality>
</Person>
</Persons>
</serviceRequestAnswer>
Let's call the column xmlcontent, and have this in a table named mytable. Then extracting the 2 public ID numbers as 2 rows can be done like so:
让我们调用 xmlcontent 列，并将其放在名为 mytable 的表中。然后可以将 2 个公共 ID 号提取为 2 行，如下所示：
select
nvl(value (line).extract ('/Person/PublicIdNumber/text()').getstringval (),'') PublicId
from mytable, table ( xmlsequence (extract(xmlcontent,'serviceRequestAnswer/Persons/Person'))) line
where id_mytable = 10092053;
Hope this helps someone :)
希望这对某人有所帮助:)
