Check values in XML

I have variable

LRowNode DBMS_XMLDOM.DOMNode;

contains (example)

<N_4883636>
  <TID_23787542>12</TID_23787542>
  <TID_23787543></TID_23787543>
  <TID_23787532/>
  <TID_23787533>0</TID_23787533>
  <TID_23787534/>
  <TID_23787535/>
  <TID_23787536>10</TID_23787536>
  <TID_23787537/>
  <TID_23787538/>
  <TID_23787539>0</TID_23787539>
  <TID_23787540>0</TID_23787540>
  <TID_23787541>0</TID_23787541>
</N_4883636>

or

<N_4883636>
  <TID_23787542>0</TID_23787542>
  <TID_23787543></TID_237875开发者_如何转开发43>
  <TID_23787532/>
  <TID_23787533>0</TID_23787533>
  <TID_23787534/>
  <TID_23787535/>
  <TID_23787536>0</TID_23787536>
  <TID_23787537/>
  <TID_23787538/>
  <TID_23787539>0</TID_23787539>
  <TID_23787540>0</TID_23787540>
  <TID_23787541>0</TID_23787541>
</N_4883636>

how to check whether the at least one non-empty and not null

This is just a basic example and might not fit for you as such but should give you an idea how to use DBMS_XMLDOM.

Another alternative is to use DBMS_XMLDOM.GETXMLTYPE to change the rules of the game use all those XMLTYPE goodies.

create or replace function has_data (p_node in dbms_xmldom.domnode)
return boolean as
  subnodes dbms_xmldom.domnodelist;
  node dbms_xmldom.domnode;
  strval varchar2(32767);
  val number;
begin
  subnodes := dbms_xmldom.getchildnodes(p_node);

  dbms_output.put_line('number of subnodes: ' || dbms_xmldom.getlength(subnodes));

  for i in 0 .. dbms_xmldom.getlength(subnodes) - 1 loop
    node := dbms_xmldom.item(subnodes, i);
    strval := dbms_xmldom.getnodevalue(dbms_xmldom.getfirstchild(node));
    dbms_output.put_line(
      '(' || i || ') ' ||
      dbms_xmldom.gettagname(dbms_xmldom.makeelement(node)) ||
      ' = ' || strval
    );

    if strval is not null then
      val := to_number(strval);
      if val > 0 then
        dbms_output.put_line('Node has data.');
        return true;
      end if;
    end if;

  end loop;

  dbms_output.put_line('Node has no data.');
  return false;
end;
/
show errors

declare
  dataset1 constant varchar2(32767) :=
'<N_4883636>
  <TID_23787542>12</TID_23787542>
  <TID_23787543></TID_23787543>
  <TID_23787532/>
  <TID_23787533>0</TID_23787533>
  <TID_23787534/>
  <TID_23787535/>
  <TID_23787536>10</TID_23787536>
  <TID_23787537/>
  <TID_23787538/>
  <TID_23787539>0</TID_23787539>
  <TID_23787540>0</TID_23787540>
  <TID_23787541>0</TID_23787541>
</N_4883636>';

  dataset2 constant varchar2(32767) :=
'<N_4883636>
  <TID_23787542>0</TID_23787542>
  <TID_23787543></TID_23787543>
  <TID_23787532/>
  <TID_23787533>0</TID_23787533>
  <TID_23787534/>
  <TID_23787535/>
  <TID_23787536>0</TID_23787536>
  <TID_23787537/>
  <TID_23787538/>
  <TID_23787539>0</TID_23787539>
  <TID_23787540>0</TID_23787540>
  <TID_23787541>0</TID_23787541>
</N_4883636>';

  dataset3 constant varchar2(32767) :=
'<N_4883636>
  <TID_23787542>0</TID_23787542>
  <TID_23787543>0</TID_23787543>
  <TID_23787533>3</TID_23787533>
</N_4883636>';

  doc dbms_xmldom.domdocument;
  nodes dbms_xmldom.domnodelist;
  x boolean;
begin
  doc := dbms_xmldom.newdomdocument(dataset1);
  nodes := dbms_xmldom.getelementsbytagname(doc, 'N_4883636');
  x := has_data(dbms_xmldom.item(nodes, 0));

  doc := dbms_xmldom.newdomdocument(dataset2);
  nodes := dbms_xmldom.getelementsbytagname(doc, 'N_4883636');
  x := has_data(dbms_xmldom.item(nodes, 0));

  doc := dbms_xmldom.newdomdocument(dataset3);
  nodes := dbms_xmldom.getelementsbytagname(doc, 'N_4883636');
  x := has_data(dbms_xmldom.item(nodes, 0));
end;
/

LNonEmptyIndexes := DBMS_XSLPROCESSOR.SELECTNODES(LRowNode, './*[text()!=''0'']');
nNON_EMPTY_COUNT := DBMS_XMLDOM.getLength(LNonEmptyIndexes);
IF (nNON_EMPTY_COUNT != 0) THEN
...
END IF;