I'm know I can use an empty string, but I don't want to lose the null functionality in the database. In some cases null is appropriate and I can't use empty strings.

Other than a empty string, how can I cause xmlforest() to create the empty elements?

tmpint = 0;

    xmlout = '<?xml version="1.0" encoding="UTF-8"?>';
    xmlout = xmlout || '<ListOfFoo FooId="' || input_foo_id  ||'">' || chr(13) || chr(10);

    FOR tmptxt IN
        select xmlforest("foo-orange","foo-apple","foo-banana") from "list-of-foo-meta" where "foo-id" = input_foo_id
        xmlout = xmlout || '<Foo FooNum="' || tmpint || '">' || tmptxt || '</Foo>' || chr(13) || chr(10);
    tmpint = tmpint + 1;
    xmlout = xmlout || '</ListOfFoo>' || chr(13) || chr(10);

    return xmlout;

P.S - I am just beginning to experiment with the XML functions available in PostgreSQL. This procedure creates valid XML I can use straight from the query (nice), and there might be better ways. Any suggestions or insight would be appreciated.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.