PostgreSQL/XML

Query

Query
SELECT xml_is_well_formed('<>') AS "<>", -- false xml_is_well_formed('<name >') AS "<name >", -- false xml_is_well_formed('<name />') AS "<name />", -- true xml_is_well_formed('academia') AS "academia", -- true xml_is_well_formed('{academia') AS "{academia", -- true xml_is_well_formed('{academia}') AS "{academia}"; -- true	SELECT xml_is_well_formed_content('<>') AS "<>", -- false xml_is_well_formed_content('<name >') AS "<name >", -- false xml_is_well_formed_content('<name />') AS "<name />", -- true xml_is_well_formed_content('academia') AS "academia", -- true xml_is_well_formed_content('{academia') AS "{academia", -- true xml_is_well_formed_content('{academia}') AS "{academia}"; -- true
SELECT xml_is_well_formed_document('<>') AS "<>", -- false xml_is_well_formed_document('<name >') AS "<name >", -- false xml_is_well_formed_document('<name />') AS "<name />", -- true xml_is_well_formed_document('academia') AS "academia", -- false xml_is_well_formed_document('{academia') AS "{academia", -- false xml_is_well_formed_document('{academia}') AS "{academia}"; -- false
WITH xpath_node AS ( SELECT cast(raw_xpath_node->>0 AS integer) "sn", cast(raw_xpath_node->>1 AS varchar) "node" FROM jsonb_array_elements('[ [0, ""], [1, "bpmn:definitions"], [2, "bpmn:process"], [3, "bpmn:serviceTask"], [4, "bpmn:extensionElements"], [5, "camunda:connector"], [6, "camunda:inputOutput"], [7, "camunda:inputParameter"] ]'::jsonb) AS raw_xpath_node ) SELECT string_agg(node, '/') AS "path" FROM xpath_node;	WITH workflow_namespace AS ( SELECT cast(raw_workflow_namespace->>0 AS varchar) "prefix", cast(raw_workflow_namespace->>1 AS varchar) "namespace" FROM jsonb_array_elements('[ ["bpmn", "http://www.omg.org/spec/BPMN/20100524/MODEL"], ["bpmndi", "http://www.omg.org/spec/BPMN/20100524/DI"], ["dc", "http://www.omg.org/spec/DD/20100524/DC"], ["di", "http://www.omg.org/spec/DD/20100524/DI"], ["modeler", "http://camunda.org/schema/modeler/1.0"], ["camunda", "http://camunda.org/schema/1.0/bpmn"] ]'::jsonb) AS raw_workflow_namespace ) -- SELECT * FROM workflow_namespace; -- SELECT array[prefix, namespace] FROM workflow_namespace; SELECT array_agg(array[prefix, namespace]) FROM workflow_namespace;
WITH xpath_ns AS ( SELECT cast(raw_xpath_ns->>0 AS varchar) "alias", cast(raw_xpath_ns->>1 AS varchar) "namespace" FROM jsonb_array_elements('[ ["bpmn", "http://www.omg.org/spec/BPMN/20100524/MODEL"], ["bpmndi", "http://www.omg.org/spec/BPMN/20100524/DI"], ["dc", "http://www.omg.org/spec/DD/20100524/DC"], ["di", "http://www.omg.org/spec/DD/20100524/DI"], ["modeler", "http://camunda.org/schema/modeler/1.0"], ["camunda", "http://camunda.org/schema/1.0/bpmn"] ]'::jsonb) AS raw_xpath_ns ), xpath_nss AS ( SELECT array_agg(array[ns.alias, ns.namespace]) FROM xpath_ns ns ), stored_dataflow AS ( SELECT id_ AS "id", rev_ AS "revision", generated_ AS "is_generated", tenant_id_ AS "tenant_id", type_ AS "type", create_time_ AS "created_on", root_proc_inst_id_ AS "root_proc_inst_id", removal_time_ AS "deleted_on", name_ AS "name", convert_from(bytes_, 'UTF8') AS "dataflow" FROM act_ge_bytearray ), stored_workflow AS ( SELECT sd.id, sd.name, sd.type, sd.revision, sd.is_generated, sd.created_on, sd.deleted_on, sd.root_proc_inst_id, CAST(sd.dataflow AS xml) AS "workflow", sd.dataflow, (SELECT * FROM xpath_nss) AS "ns" FROM stored_dataflow sd WHERE xml_is_well_formed_document(sd.dataflow) ORDER BY sd.name ASC, sd.created_on DESC ) SELECT sw.name, (xpath('count(/bpmn:definitions/bpmn:process)', sw.workflow, sw.ns))[1]::text::int, (xpath('/bpmn:definitions/bpmn:process', sw.workflow, sw.ns))[1], dataflow FROM stored_workflow sw;