json – Create tables from XML dynamically using Postgres sql script

I’m trying to create postgres sql tables and add columns & its values as a json blob to a Data column of the newly created table.

I can create the table successfully, however, I’m unable to insert the data.

Here’s my postgres sql script:

DO $$ 
DECLARE
    xml_data TEXT;
    container_table_name TEXT;  
    doc XML;
    containers XML[];
    container_element XML;   
    column_elements XML[];
    column_element XML;
    jsonb_data JSONB;
    column_name TEXT;
    column_value TEXT;
BEGIN
    -- Load your XML data into xml_data variable
    xml_data := E'
    <Root>
        <ClientContainer RecordCount="1">
            <Client>
                <Name>Test Client</Name>
                <Address>469 LaTrobe St, Melbourne</Address>
                <Mobile>0411223344</Mobile>
            </Client>
        </ClientContainer>
        <AccountContainer RecordCount="1">
            <Account>
                <Name>Test Account</Name>
                <Type>Super</Type>
            </Account>
        </AccountContainer>
    </Root>';

    -- Convert text to XML type for processing
    doc := XMLPARSE(DOCUMENT xml_data);

    -- Extract container elements
    containers := xpath('/Root/*[contains(local-name(), "Container")]', doc);

    -- Loop through each container to create tables and columns
    FOREACH container_element IN ARRAY containers LOOP
        -- Get the table name from the container element's name, removing 'Container' from it
        container_table_name := split_part((xpath('local-name(./child::*[1])', container_element))[1]::TEXT, 'Container', 1);
        
        -- Create the table if it does not exist with a JSONB column for data
        EXECUTE format('CREATE TABLE IF NOT EXISTS %I (data JSONB);', container_table_name);
    
        -- Extract all child elements of the first child element under the container element
        column_elements := xpath('(./child::*[position()=1]/child::*)', container_element);
        
        -- Initialize jsonb_data as an empty JSON object
        jsonb_data := '{}'::JSONB;

        -- Loop through each child element to build a JSONB object
        FOREACH column_element IN ARRAY column_elements LOOP
            RAISE NOTICE 'Column Element: %', column_element;
            column_name := (xpath('local-name(.)', column_element))[1]::TEXT;
            column_value := (xpath('text()', column_element))[1]::TEXT;
            jsonb_data := jsonb_set(jsonb_data, ARRAY[column_name], to_jsonb(column_value));
        END LOOP;

        -- Insert the JSONB data into the table
        EXECUTE format('INSERT INTO %I (data) VALUES (%L);', container_table_name, jsonb_data);
    END LOOP;
END $$;

*the above script should create Client & Account tables and insert json data as below:

`
“Client”: {
“Name”: “Test Client”,
“Address”: “469 LaTrobe St, Melbourne”,
“Mobile”: “0411223344”
}

"Account": {
    "Name": "Test Account",
    "Type": "Super"
}