I'm trying to build a worktable/temp schema from a workflow query (targeting dimension = nms:delivery) where I include the subject line of each delivery to the temp schema/work table.
I've reviewed the nms:delivery schema and located the xpath of the subject field as mailParameters/subject (and can see the data type is CDATA).
So in the output columns of my query activity (as well as others), i have added the following expression. [mailParameters/subject]
This produces the following error:
03/15/2019 9:36:46 AM query PGS-220000 PostgreSQL error: ERROR: function unescapexmlvalue(character varying) does not exist LINE 1: ...) SELECT DISTINCT D0.iDeliveryId, D0.tsContact, UnescapeXm... ^ HINT: No function
03/15/2019 9:36:46 AM query matches the given name and argument types. You might need to add explicit type casts.
03/15/2019 9:36:46 AM query WDB-200001 SQL statement 'INSERT INTO wkf41455034_8_1 (iDeliveryId,tsContact,mSubject,iId) SELECT DISTINCT D0.iDeliveryId, D0.tsContact, UnescapeXmlValue((XPath(E'/delivery/mailParameters/subject', D0.mData::XML))::varchar), D0.iDeliveryId FROM NmsD
03/15/2019 9:36:46 AM query elivery D0 WHERE ((D0.iState = 95) AND (D0.tsContact >= TIMESTAMP WITH TIME ZONE '2019-03-01 00:00:00.000Z')) AND ((D0.iDeliveryId > 0 OR D0.iDeliveryId < 0) AND ((D0.iDeleteStatus = 0)))' could not be executed.
Can anyone advise how long text fields can be added to temp schemas/worktables?
That was the stub, just outputs its input. Run it in a sql script to add it to the db- after that your code should work fine, assuming you didn't need to unescape any xml values. NB code is untested, it was written directly in comment box here.