PostgreSQL and soap / xpath

Recently I have been working on integrating soap into our postgresql database. Its not something easily built into postgresql, so I wrote some plpgsql functions to achieve the same purpose.

-- Create a XML request for uploading
CREATE OR REPLACE FUNCTION soap_test() RETURNS xml AS $$
DECLARE
x xml;
d text;
BEGIN
d = $A$

The quick red fox jumps over the lazy brown dog.

$A$;
SELECT xmlroot(xmlparse(document d),
version '1.0', standalone no) into x;
return x;
END;
$$ LANGUAGE plpgsql;

I use this function to generate a HTTP request that looks like this:

Host: example.com
Content-Type: text/xml
Content-Length: 349
SOAPAction: "https://example.com/soapservice/TestFunction"

The quick red fox jumps over the lazy brown dog.

The first gotcha I found was with .net services. They do need to know the exact SOAPACTION specified in your request. This is not the case with apache etc. But can catch you out if you are not normally specifying a soap action.
SOAPAction is the soap url + soap function you are calling.

The next detail is parsing the soap requests. I again addressed this with a plpgsql function to process responses. You can read about the xpath querying language on the w3c website. PostgreSQL only implements version 1.0, not version 2.0.

CREATE OR REPLACE FUNCTION soap_test_response(xmlin text) RETURNS integer AS $PROC$
DECLARE
cid text[];
errmsg text[];
errid text[];
x xml;
BEGIN
x = xmlparse(document xmlin);

select xpath('//soap:Error/text()', x,
ARRAY[ARRAY['soap','http://schemas.xmlsoap.org/soap/envelope/'],
ARRAY['example','https://example.com']]) into errid;
select xpath('//soap:ErrorMessage/text()', x,
ARRAY[ARRAY['soap','http://schemas.xmlsoap.org/soap/envelope/'],
ARRAY['example','https://example.com]]) into errmsg;
if errid[1] is not null then
if errid[1] != '0' then
raise Exception E'Soap service rejected with this error:n%nError ID: %',errmsg[1],errid[1];
end if;
return null;
end if;
select xpath('//example:TestResponse/text()', x,
ARRAY[ARRAY['soap','http://schemas.xmlsoap.org/soap/envelope/'],
ARRAY['example','https://example.com']]) into cid;
raise notice '% % %',cid[1], errid[1], errmsg[1];
return cid[1];
END;
$PROC$ LANGUAGE plpgsql;

Wild carding on namespaces are not available in xpath 1.0. So you have to be very careful with your namespace definitions for xpath to avoid getting a NULL return from xpath.

• Use // for looking for any element in the structure
• You often need to nest your name spaces for requests to work. Ie. //soap:Body/example:TestResponse/text()
• Only define valid namespaces in your xpath call, additional name spaces in the ARRAY list may cause your query to not work
• If you still get NULL results, start from the root and work down. Ie /soap:Envelope/soap:Body/example:TestResponse/*
• An xpath request of /soap:Envelope/* can help in debugging what namespaces are used where in the soap response