PostgreSQL/XML
Query
|
XML 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;
| |