Oracle 10g：使用 XMLSequence 和 ExtractValue 从 Clob 读取 XML 值
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/12396792/
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
Oracle 10g: Reading XML values from Clob using XMLSequence and ExtractValue
提问by HCC
I'm starting to feel a bit dim. I've read a lot of web pages and tried a number of methods to do something that seems relatively simple.
我开始觉得有点昏暗了。我已经阅读了很多网页并尝试了许多方法来做一些看起来相对简单的事情。
I've some XML stored a table. The table contains an ID and the XML in a CLOB. Something like:
我有一些 XML 存储了一个表。该表在 CLOB 中包含一个 ID 和 XML。就像是：
ID = 1
<?xml version="1.0" encoding="UTF-8" ?>
<CricketGame xmlns="http://www.somewhere.com/cricket/2002/09" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" Version="2002A" xsi:type="CricketGame" >
<TeamInfo TeamId="WestCountry" />
<SessionInfo SessionId="XhRya5m999988888" RestartSession="false" />
<Errors>
<Error ErrorText="Generic Error" ErrorCode="700" SupplierErrorText="Connection: DECLINED" />
<Error ErrorText="Generic Error" ErrorCode="701" SupplierErrorText="Account Error" />
</Errors>
</CricketGame>
I have been trying to use a combination of extract and XMLSequence to create a Table and then extractvalue to get the specific values from ErrorText, ErrorCode and SupplierErrorText. My SQL:
我一直在尝试使用extract 和XMLSequence 的组合来创建一个表，然后extractvalue 从ErrorText、ErrorCode 和SupplierErrorText 中获取特定值。我的 SQL：
SELECT
extractvalue(value(p), '/Error/@ErrorText') as errText,
extractvalue(value(p), '/Error/@ErrorCode') as errCode,
extractvalue(value(p), '/Error/@SupplierErrorText') as supErrText
FROM gamestable s,
Table(
XMLSequence(
extract(xmltype(s.xml), '/CricketGame/Errors/Error')
)
) p
where
s.gameID = 1
The SQL doesn't return any errors, but it doesn't return the data either.
SQL 不返回任何错误，但也不返回数据。
Can anyone see what I'm doing wrong? Am I going about this totally wrong? Is it perhaps something to do with the xmlns?
谁能看到我做错了什么？我这样做是完全错误的吗？这可能与xmlns有关吗？
Many thanks in advance!
提前谢谢了！
采纳答案by Jon Heller
You're right, the problem is related to xmlns. I don't understand XML well, so I can't tell you if this is a good solution, but it at least appears to work for this example:
你说得对，问题与xmlns. 我不太了解 XML，所以我不能告诉你这是否是一个好的解决方案，但它至少似乎适用于这个例子：
SELECT
extractvalue(value(p), '/Error/@ErrorText', 'xmlns="http://www.somewhere.com/cricket/2002/09"') as errText,
extractvalue(value(p), '/Error/@ErrorCode', 'xmlns="http://www.somewhere.com/cricket/2002/09"') as errCode,
extractvalue(value(p), '/Error/@SupplierErrorText', 'xmlns="http://www.somewhere.com/cricket/2002/09"') as supErrText
FROM gamestable s,
Table(
XMLSequence(
extract(xmltype(s.xml), '/CricketGame/Errors/Error', 'xmlns="http://www.somewhere.com/cricket/2002/09"')
)
) p
where
s.gameID = 1
