Differences

This shows you the differences between two versions of the page.

--- dba:postgresql:postgresql_basic_querys [2024/03/15 09:16] – [list objects in schema] dodger
+++ dba:postgresql:postgresql_basic_querys [2024/04/19 07:37] (current) – [Section: EMPTY] dodger
@@ Line 414: / Line 414: @@
 ===== show object source =====
 Change ''FUNCTION_NAME'':
 <code sql>
 select prosrc from pg_proc where proname='FUNCTION_NAME'  ;
@@ Line 883: / Line 885: @@
-====== Section: EMPTY ======
+====== Section: XML (tables) ======
+This is an example on how to use XMLTABLE function to extract data from a XML column:
+<code sql>
+    select
+        bdef.build_definition_id as build_definition_id,
+        tasks.task_definition_id as task_definition_id,
+        tasks.user_description as user_description,
+        tasks.is_enabled as is_enabled,
+        tasks.plugin_key as plugin_key,
+        tasks.finalising as finalising,
+        config_items.item_key as item_key,
+        config_items.item_value as item_value
+    FROM
+        build_definition bdef,
+        XMLTABLE('//taskDefinition' PASSING XMLPARSE(DOCUMENT xml_definition_data)
+                  COLUMNS
+                      task_definition_id integer PATH 'id',
+                      user_description text PATH 'userDescription',
+                      is_enabled boolean PATH 'isEnabled',
+                      plugin_key text PATH 'pluginKey',
+                      finalising boolean PATH 'finalising',
+                      config XML PATH 'config') AS tasks
+    left join XMLTABLE('//item' PASSING tasks.config COLUMNS item_key text PATH 'key',item_value text PATH 'value') AS config_items
+    on true
+    where xml_definition_data LIKE '%\/dip\/%'
+    order by 1
+;
+</code>
+====== Section: EMPTY ======
+<code sql>
-<code>
 </code>