在 Oracle PL SQL 中检索 xml 元素的值
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/743031/
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
Retrieve value of an xml element in Oracle PL SQL
提问by Godcode
Does anybody know how to retrieve the values of <ZIPCODE>and <CITY>using PL/SQL? I have followed a tutorial over the net, however, it can retrieve the element names, but not their values. Any of you know what seems to be the problem? I have already consulted Google (the internet's well kept secret) over this but no luck :(
有人知道如何检索<ZIPCODE>和<CITY>使用 PL/SQL的值吗？我在网上学习了一个教程，但是，它可以检索元素名称，但不能检索它们的值。你们中的任何人都知道似乎是什么问题吗？我已经就此咨询过谷歌（互联网保密），但没有运气:(
<Zipcodes>
<mappings Record="4">
<STATE_ABBREVIATION>CA</STATE_ABBREVIATION>
<ZIPCODE>94301</ZIPCODE>
<CITY>Palo Alto</CITY>
</mappings>
</Zipcodes>
here's the sample code:
这是示例代码：
-- prints elements in a document
PROCEDURE printElements(doc DBMS_XMLDOM.DOMDocument) IS
nl DBMS_XMLDOM.DOMNodeList;
n DBMS_XMLDOM.DOMNode;
len number;
BEGIN
-- get all elements
nl := DBMS_XMLDOM.getElementsByTagName(doc, '*');
len := DBMS_XMLDOM.getLength(nl);
-- loop through elements
FOR i IN 0 .. len - 1 LOOP
n := DBMS_XMLDOM.item(nl, i);
testr := DBMS_XMLDOM.getNodeName(n) || ' ' || DBMS_XMLDOM.getNodeValue(n);
DBMS_OUTPUT.PUT_LINE (testr);
END LOOP;
DBMS_OUTPUT.PUT_LINE ('');
END printElements;
回答by Luke Woodward
You need to change the line
你需要改变行
testr := DBMS_XMLDOM.getNodeName(n) || ' ' || DBMS_XMLDOM.getNodeValue(n);
to
到
testr := DBMS_XMLDOM.getNodeName(n) || ' ' || DBMS_XMLDOM.getNodeValue(DBMS_XMLDOM.getFirstChild(n));
In XML DOM, elements don't have any 'value' to speak of. Element nodes contain Text nodes as children, and it is these nodes that contain the values you want.
在 XML DOM 中，元素没有任何“价值”可言。元素节点包含文本节点作为子节点，正是这些节点包含您想要的值。
EDIT (in response to Tomalak's comment): I'm not aware of any functions in DBMS_XMLDOM to obtain the combined value of all the child text nodes of an element. If that's what you need, then you may well need to use something like the following function:
编辑（响应 Tomalak 的评论）：我不知道 DBMS_XMLDOM 中有任何函数可以获取元素的所有子文本节点的组合值。如果这就是您所需要的，那么您很可能需要使用如下函数：
CREATE OR REPLACE FUNCTION f_get_text_content (
p_node DBMS_XMLDOM.DOMNode
) RETURN VARCHAR2
AS
l_children DBMS_XMLDOM.DOMNodeList;
l_child DBMS_XMLDOM.DOMNode;
l_text_content VARCHAR2(32767);
l_length INTEGER;
BEGIN
l_children := DBMS_XMLDOM.GetChildNodes(p_node);
l_length := DBMS_XMLDOM.GetLength(l_children);
FOR i IN 0 .. l_length - 1 LOOP
l_child := DBMS_XMLDOM.Item(l_children, i);
IF DBMS_XMLDOM.GetNodeType(l_child) IN (DBMS_XMLDOM.TEXT_NODE, DBMS_XMLDOM.CDATA_SECTION_NODE) THEN
l_text_content := l_text_content || DBMS_XMLDOM.GetNodeValue(l_child);
END IF;
END LOOP;
RETURN l_text_content;
END f_get_text_content;
/
回答by Pancho
This is a simple illustration of how to retrieve the desired values from the document:
这是如何从文档中检索所需值的简单说明：
declare
vDOM dbms_xmldom.DOMDocument;
vNodes dbms_xmldom.DOMNodeList;
vXML xmltype := xmltype('<Zipcodes>
<mappings Record="4">
<STATE_ABBREVIATION>CA</STATE_ABBREVIATION>
<ZIPCODE>94301</ZIPCODE>
<CITY>Palo Alto</CITY>
</mappings>
</Zipcodes>');
begin
-- create the dom document from our example xmltype
vDOM := dbms_xmldom.newDOMDocument(vXML);
-- find all text nodes in the dom document and return them into a node list
vNodes := dbms_xslprocessor.selectNodes
(n => dbms_xmldom.makeNode(dbms_xmldom.getDocumentElement(vDOM))
,pattern => '//*[self::ZIPCODE or self::CITY]/text()'
,namespace => null
);
-- iterate through the node list
for i in 0 .. dbms_xmldom.getlength(vNodes) - 1 loop
-- output the text value of each text node in the list
dbms_output.put_line(dbms_xmldom.getNodeValue(dbms_xmldom.item(vNodes,i)));
end loop;
-- free up document resources
dbms_xmldom.freeDocument(vDOM);
end;
The above results in the output requested:
上述结果导致请求的输出：
94301
Palo Alto
Replacing the xpath pattern in the above example with pattern => '//text()' results in the output:
用 pattern => '//text()' 替换上面示例中的 xpath 模式会导致输出：
CA
94301
Palo Alto
ie. all the text in the document. Many variations on this theme are of course possible using this technique.
IE。文档中的所有文本。使用这种技术当然可以对这个主题进行许多变体。
