Question 294705 in PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – Read XML Nodes and create Dynamic table with dynamic columns in Postgres sql Script file

I have sample XML files, where i need to ready Node name & create table name similar to Node name skipping container nodes. Then read these XML Nodes and create Dynamic table with dynamic columns in Postgres sql Script file. This file will be executed by existing framework written to create sample database for microservices.

Sample XML –

<Base>
    <Data>
       <UserContainer>
         <user>
            <ID>C1234</ID>
        <Name>name1</Name>
         </user>    
       </UserContainer>
       <AddressContainer>
         <Address>
            <ID>A123</ID>
        <Address1>TestAddress</Address1>
         </Address> 
       </AddressContainer>
     </Data>
    <Data>
       <UserContainer>
         <user>
            <ID>C1234</ID>
        <Name>name1</Name>
         </user>    
       </UserContainer>
       <AddressContainer>
         <Address>
            <ID>A123</ID>
        <Address1>TestAddress</Address1>
         </Address> 
       </AddressContainer>
     </Data>
</Base>

so output of this xml reading should be as follows –

Dynamic User table with columns ID, Name
Dynamic Address table with columns ID, Address1

After creating these tables, same values should be inserted into User and Address tables from xml nodes.

Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    I tried above solution -

        FOR xml_row IN SELECT unnest(xpath('//Data/*', xml_data)) LOOP
            -- Get the node name
        
            RAISE NOTICE 'xml_row: %', xml_row;
            node_name := xml_row;
            RAISE NOTICE 'node_name: %', node_name;
            node_name := name(xml_row);
           RAISE NOTICE 'node_name: %', node_name;
output of above is not as per expectation  
    xml_row: <UserContainer>
             <User><ID>C1234</ID></User>    
           </UserContainer>
    node_name: <UserContainer>
             <User><ID>C1234</ID></User>    
           </UserContainer>
    node_name: <UserContainer>
             <User><ID>C1234</ID></User>**

    Node name and child nodes are not getting populated. Not sure if something i'm missing.


  2. 0

    you can use PL/pgSQL along with the XML functions to dynamically create tables based on XML structure and insert data into these tables.

    -- Function to create tables and insert data based on XML
CREATE OR REPLACE FUNCTION process_xml(xml_data XML)
RETURNS VOID AS $$
DECLARE
    node_name TEXT;
    table_name TEXT;
    column_names TEXT;
    column_values TEXT;
    xml_row XML;
BEGIN
    -- iterate data nodes
    FOR xml_row IN SELECT unnest(xpath('//Data/*', xml_data)) LOOP
        -- Get the node name
        node_name := name(xml_row);
        
        -- omit container nodes
        IF node_name NOT LIKE '%Container' THEN
            table_name := 'dynamic_' || node_name;
            column_names := '';
            column_values := '';

            -- iterate child nodes
            FOR child_node IN SELECT unnest(xpath('./*', xml_row)) LOOP
                column_names := column_names || name(child_node) || ', ';
                column_values := column_values || quote_literal((child_node).text) || ', ';
            END FOR;

            -- omit trailing commas
            column_names := rtrim(column_names, ', ');
            column_values := rtrim(column_values, ', ');

            -- dynamic table creation
            EXECUTE 'CREATE TABLE IF NOT EXISTS ' || table_name ||
                    ' (' || column_names || ');';

            -- dynamic data insertion
            EXECUTE 'INSERT INTO ' || table_name ||
                    ' (' || column_names || ') VALUES (' || column_values || ');';
        END IF;
    END FOR;
END;
$$ LANGUAGE plpgsql;

    can use this function by passing your xml data as an argument.

    DO $$ 
DECLARE 
    xml_data XML := '<Base>
    <Data>
       <UserContainer>
         <user>
            <ID>C1234</ID>
        <Name>name1</Name>
         </user>    
       </UserContainer>
       <AddressContainer>
         <Address>
            <ID>A123</ID>
        <Address1>TestAddress</Address1>
         </Address> 
       </AddressContainer>
     </Data>
    <Data>
       <UserContainer>
         <user>
            <ID>C1234</ID>
        <Name>name1</Name>
         </user>    
       </UserContainer>
       <AddressContainer>
         <Address>
            <ID>A123</ID>
        <Address1>TestAddress</Address1>
         </Address> 
       </AddressContainer>
     </Data>
</Base>'; 
BEGIN 
    PERFORM process_xml(xml_data);
END $$;
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search