how to get boolean value of XML data in oracle procedure

Question

I am calling a SOAP webService from oracle which is returning xml response like:

<?xml version="1.0" encoding="utf-8"?>
  <boolean xmlns="http://SOAPwebservice.com/WebServices/Methods">
     false
  </boolean>

I need to catch the boolean value so I found this how to use xmltable in oracle? but I don't how to do it with my simple boolean response, any hints?

Huh? You want a SQL query to extract the value of the element names ? — OldProgrammer, Sep 21 '16 at 11:28
Yes @OldProgrammer, webservice reponse is a boolean tag. Any idea? — Mlle 116, Sep 21 '16 at 11:42

score 1 · Accepted Answer · answered Sep 21 '16 at 12:01

1

WITH test_data AS
  (SELECT xmltype('<boolean xmlns="http://SOAPwebservice.com/WebServices/Methods">false</boolean>') data
  FROM dual
  )
SELECT extractValue(data,'/boolean','xmlns="http://SOAPwebservice.com/WebServices/Methods"')
FROM test_data

answered Sep 21 '16 at 12:01

OldProgrammer

12,050
4
24
45

score 1 · Answer 2 · answered Sep 21 '16 at 14:12

Rather than trying to modify the XML string to remove the namespace, you can incorporate that into your XML query. OldProgrammer already showed how to do that with the deprecated extractValue() function, but you can do the same thing with the XMLTable you asked about:

FUNCTION XMLTEST (P_XML VARCHAR2) RETURN VARCHAR2 AS
    V_FLAG VARCHAR2(30);
BEGIN
    SELECT P.boolean
    INTO   V_FLAG
    FROM   xmltable(xmlnamespaces(default 'http://SOAPwebservice.com/WebServices/Methods'),
           '/boolean' passing xmltype(P_XML) 
           columns boolean VARCHAR2(30) PATH '.') P;

    RETURN V_FLAG;
END XMLTEST;

Or - as you're expected a single node, and will get an error from the XMLTable version if there are multiples anyway - an XMLQuery:

    SELECT XMLQuery(
        'declare default element namespace "http://SOAPwebservice.com/WebServices/Methods"; (: :)
        /boolean/text()'
        passing xmltype(P_XML)
        returning content).getStringVal()
    INTO   V_FLAG
    FROM   dual;

Read more about XMLTable and XMLQuery, and how they handle namespaces.

It's also possible to wildcard the XPath:

    SELECT XMLQuery('/*:boolean/text()'
        passing xmltype(P_XML)
        returning content).getStringVal()
    INTO   V_FLAG
    FROM   dual;

With your formatted XML string the extracted value is more than just the string 'false' - it has leading and trailing spaces and newline characters, which you could then strip out if necessary. If your real XML has <boolean>false</boolean> with no extra whitespace then that isn't necessary, of course, and your v_flag only needs to be five characters long.

You don't need to do this in PL/SQL, but that seems to be a requirement you are imposing.

score 0 · Answer 3 · answered Sep 21 '16 at 12:22

Solved, the problem was with xmlns ="..." should remove it to get the answer

FUNCTION XMLTEST (P_XML VARCHAR2) RETURN VARCHAR2 AS
                     V_FLAG VARCHAR2(4000);
                     V_XML1 VARCHAR2(4000);
                     V_XML2 VARCHAR2(4000);  
                     V_XML VARCHAR2(4000);               
                BEGIN  
                    V_XML1 := SUBSTR(P_XML,0,INSTR(P_XML,'xmlns')-1)||'>';
                    V_XML2 := SUBSTR(P_XML,INSTR(P_XML,'/Methods">')+10);
                    V_XML  := V_XML1||V_XML2;

                    SELECT P.boolean
                    INTO   V_FLAG
                    FROM   xmltable('/boolean' passing xmltype(V_XML) 
                                columns boolean VARCHAR2(30) PATH 'text()') P;

                    RETURN V_FLAG;           

                END XMLTEST;

how to get boolean value of XML data in oracle procedure

3 Answers3