检索Oracle PL SQL中xml元素的值 [英] Retrieve value of an xml element in Oracle PL SQL

问题描述

有人知道如何使用PL/SQL检索<ZIPCODE>和<CITY>的值吗?我已经在网上关注了一个教程，但是它可以检索元素名称，但不能检索其值.你们中的任何人都知道问题出在哪里吗?我已经就此征询了Google(互联网的秘密)，但没有运气:(

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 :(

<Zipcodes>
  <mappings Record="4">
    <STATE_ABBREVIATION>CA</STATE_ABBREVIATION>
    <ZIPCODE>94301</ZIPCODE>
    <CITY>Palo Alto</CITY>
  </mappings>
</Zipcodes>

这是示例代码:

-- 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;

推荐答案

您需要更改行

testr := DBMS_XMLDOM.getNodeName(n) || ' ' || DBMS_XMLDOM.getNodeValue(n);

到

testr := DBMS_XMLDOM.getNodeName(n) || ' ' || DBMS_XMLDOM.getNodeValue(DBMS_XMLDOM.getFirstChild(n));

在XML DOM中，元素没有任何价值"可言.元素节点包含Text节点作为子节点，而这些节点包含所需的值.

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.

编辑(响应Tomalak的评论):我不知道DBMS_XMLDOM中的任何函数来获取元素的所有子文本节点的组合值.如果这是您需要的，那么您可能需要使用类似以下功能的东西:

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:

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;
/

这篇关于检索Oracle PL SQL中xml元素的值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！