Tell me more ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.
up vote 0 down vote favorite

I will start with what my desired output is:

<?xml version="1.0"?>
<Accounts>
    <Account>
        <variable1>variable1</variable1>
        <variable2>variable2</variable2>
        <variable3>variable3</variable3>
        <variable4>variable4</variable4>
        <variable5>variable5</variable5>
        <variable6>variable6</variable6>
        <Table>
            <Row>
                <Column1>row1col1</Column1>
                <Column2>row1col2</Column2>
                <Column3>row1col3</Column3>
            </Row>
            <Row>
                <Column1>row2col1</Column1>
                <Column2>row2col2</Column2>
                <Column3>row2col3</Column3>
            </Row>
            <Row>
                <Column1>row3col1</Column1>
                <Column2>row3col2</Column2>
                <Column3>row3col3</Column3>
            </Row>
        </Table>
    </Account>
    ...
</Accounts>

The SQL function this needs to come from currently loops through Accounts and for each account ID gathers all the relevant data above for each account, e.g. variable1, 2, table, etc. So per Account ID it returns all the variables and the table with its 3 columns and all rows.

So to simplify, for every Account ID, the XML file needs the above structure appended to the file. Now my dilemma comes when I try to create the XML file with all these varied parts.

The first issue is how to combine the Table information and the variable information into a single XML tree. I know it is possible to create an XML Forest with Elements from a table, but the issue is that the for each account there is a set of variable data and a table. I have considered table_to_xml but this takes directly from a table and I have no idea how I would append the variable elements before the table/xml.

I have considered doing text arrays:

for rec in execute cmd
loop

  cmd:= 'select xmlelement(name "Account",
        xmlelement(name "Variable1", '||var1||'))';

  execute cmd into vtext
  varray[n] := vtext;
  n++
end loop;

And then possibly putting the table nested into the xmlelement as a text array as well. Keep nesting until I have all the 's and then send those to XML after they are all together.

The next issue is combining all these Accounts into one overarching element within the same XML file.

If anyone has any ideas of how to approach solving this I would greatly appreciate the help. I feel like I am over thinking the entire process, or maybe it is too ridiculous to do in SQL and maybe I should try these operations in a scripting language like Python.

share|improve this question
I have considered making a table with the variables, then creating the table for the <Table> section with a relational ID that I could match to the main variables table, but the problem still comes down to having multiple rows trying to be inserted to a single row, making statements like this impossible: – Square Feb 22 at 23:05
select XMLElement( name noi, XMLForest(z.var1, z.var2, z.var3, z.var4), XMLElement(name Table, XMLForest(row1col1, row1col2))) FROM (select a.* from table1 a join table2 b on ((a.something = b.something) and (a.something = b.something)) where b.dt is null and b.dt is null) z – Square Feb 22 at 23:08

1 Answer

up vote 0 down vote accepted

The solution to the problem was pretty straight forward. Place all data into temporary tables and concatenate. Specifically the way to do this for my desired output:

-- FUNCTION AFTER CREATE AND DECLARES
v_cmd := 'select Accounts from <table>';

final_xml := '<?xml version="1.0"?><Accounts>';

for record in execute v_cmd
loop
    <generate Account data into SQL variables>

    -- Check if temp table exists, then create P1
    v_table_name1 := '<temp_table1>';

    IF EXISTS (select 1 from information_schema.tables 
    where table_schema = '<schema>' and table_name = <temp_table1>) THEN
        v_s_cmd := 'DROP TABLE '||<temp_table1>;
        execute v_s_cmd;
    END IF;

    v_s_cmd := 'CREATE TABLE <temp_table1>
    (variable1 varchar, variable2 varchar, ...)';
    execute v_s_cmd;
    v_s_cmd := 'INSERT INTO <temp_table1> 
    (variable1, variable2, ...)
    VALUES ('||coalesce(quote_literal(Account.variable1),'NULL')||',' 
           ''||coalesce(quote_literal(Account.variable2),'NULL')||',' ...)'';
    execute v_s_cmd;

    <generate multiple row data to be elements of the above Account>

    -- Check if temp table exists, then create P1
    v_table_name2 := '<temp_table2>';

    IF EXISTS (select 1 from information_schema.tables 
    where table_schema = '<schema>' and table_name = <temp_table2>) THEN
        v_s_cmd := 'DROP TABLE '||<temp_table2>;
        execute v_s_cmd;
    END IF;

    v_s_cmd := 'CREATE TABLE <temp_table2(Column1, Column2, Column3)';
    execute v_s_cmd;

    column_id = record.row_id;

    for column_record in

    SELECT Column1, Column2, Column3 FROM <column_data_source>
    WHERE data_id = column_id ORDER BY Column1, Column2, Column3

    loop
        v_s_cmd := 'INSERT INTO '||<temp_table2>|| 
        '(Column1, Column2, Column3, column_id) SELECT 
        '||quote_literal(column_record.Column1)||', 
        '||quote_literal(column_record.Column2)||', 
        '||quote_literal(column_record.Column2)||', 
        '''||column_id||''';';
        execute v_s_cmd;
    end loop;

    -- get var data 
    v_s_cmd := '(select regexp_replace((
    XMLElement(name Account, 
        XMLForest(variable1, variable2 ...)))::text,
        ''</Account>'','''',''g'') as var_d
    FROM <temp_table1>)';

    execute v_s_cmd into var_data;

    -- get Table data
    v_s_cmd := 'select XMLElement(name Row, 
                    XMLForest(Column1, Column2, Column3)) as test
                FROM '||<temp_table2>;

    table_rec := '<Table>';

    -- concatenate to variable
    for row_rec in execute v_s_cmd
    loop
        table_rec := table_rec || row_rec.test;
    end loop;
    table_rec := table_rec||'</Table></Account>';

    -- complete XML part and concatenate to main xml variable
    final_xml := final_xml || var_data || s_a_rec;

    v_s_cmd := 'drop table '||temp_table1;
    execute v_s_cmd;
    v_s_cmd := 'drop table '||temp_table2;
    execute v_s_cmd;

END LOOP;

final_xml := final_xml || '</Accounts>';

final_xml := XMLPARSE(DOCUMENT final_xml);

create table schema.temp_final_xml(xmlcode varchar);
insert into schema.temp_final_xml(xmlcode) VALUES (final_xml);

v_s_cmd := 'COPY (select xmlcode from schema.temp_final_xml) 
            TO ''/filepath/file.xml''';
execute v_s_cmd;

drop table schema.temp_final_xml;

If there is a cleaner way to perform this operation I would love to know it. I wracked my brain for a few days to come up with this.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.