Avatar

Level 5

Hi all,

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]

1712214_pastedImage_1.png

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))[1]::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?

Thanks

David