PostgreSQL: How to check if node exists in the XML

Question

How to check if a node exists in the XML. Here is the function which I tried.

    CREATE OR REPLACE FUNCTION dbo.ab001(
         IN par_params TEXT DEFAULT null)
    RETURNS void
    AS
    $BODY$
    DECLARE
        var__query TEXT DEFAULT '';
    BEGIN
        IF NOT (par_Params IS NULL OR par_Params = '') THEN
                if not xmlexist('//IncludeSettle' passing by ref par_Params)
                    var__query := var__query || ' AND (TDlCd.T_Type_ID <> ''V'')'
                if not xmlexist('//IncludeState' passing by ref par_Params)
                    var__query := var__query || ' AND (TDlCd.T_Type_ID <> ''R'')'
        END IF;
    END;
    $BODY$
    LANGUAGE  plpgsql;

Using this I am getting below error saying:

 ERROR:  missing "THEN" at end of SQL expression
 LINE 12:    END IF;

Then I added then and end if and got back below error:

    CREATE OR REPLACE FUNCTION dbo.ab001(
         IN par_params TEXT DEFAULT null)
    RETURNS void
    AS
    $BODY$
    DECLARE
        var__query TEXT DEFAULT '';
    BEGIN
        IF NOT (par_Params IS NULL OR par_Params = '') THEN
                if not xmlexist('//IncludeSettle' passing by ref par_Params) then
                    var__query := var__query || ' AND (TDlCd.T_Type_ID <> ''V'')'
                end if;
                if not xmlexist('//IncludeState' passing by ref par_Params) then
                    var__query := var__query || ' AND (TDlCd.T_Type_ID <> ''R'')'
                end if;
        END IF;
    END;
    $BODY$
    LANGUAGE  plpgsql;

Error:

 ERROR:  syntax error at or near "passing"
 LINE 10:    if not xmlexist('//IncludeSettle' passing by r...

But the following query works as expected.

 SELECT xmlexists('//IncludeSettle' PASSING BY REF '<dummy_root><IncludeSettle/><StartTime/></dummy_root>');

Result is true.

How can I accomplish this. Any help is really appreciated.

Here is the link: https://rextester.com/WSCY48060

score 1 · Accepted Answer · 2019-11-27 16:12:45Z

1

It is xmlexists and not xmlexist!

This one works:

   CREATE OR REPLACE FUNCTION ab001(
         IN par_params TEXT DEFAULT null)
    RETURNS void
    AS
    $BODY$
    DECLARE
        var__query TEXT DEFAULT '';
    BEGIN
        IF NOT (par_Params IS NULL OR par_Params = '') THEN
                if not xmlexists('//IncludeSettle' passing by ref par_Params) then
                    var__query := var__query || ' AND (TDlCd.T_Type_ID <> ''V'')';
                end if;
                if not xmlexists('//IncludeState' passing by ref par_Params) then
                    var__query := var__query || ' AND (TDlCd.T_Type_ID <> ''R'')';
                end if;
        END IF;
    END;
    $BODY$
    LANGUAGE  plpgsql;

edited Nov 27, 2019 at 16:12

answered Nov 27, 2019 at 16:06

user10343866

Sign up to request clarification or add additional context in comments.

1 Comment

User12111111 Over a year ago

Oh my bad... I overlooked. Thank you. I will try again.

Collectives™ on Stack Overflow

PostgreSQL: How to check if node exists in the XML

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related