[SOLVED] generate sql result into xml

Hi all,

How do I generate sql result into xml ?
Tried this way (shown below), but it generates all data into single column.
Tell me kindly what else should I post here to get help.
Please help.
Thanks ahead.

generate_xml()
{
        f1=_tmp1
        f2=_tmp2

        cat $SQL_QUERY_OUT |sed -e 's/^"//g;s/"$//g;s/","/|/g'|tr '|' '\t' > $f1

        [ -e $SQL_QUERY_OUT ] && rm $SQL_QUERY_OUT

        # prepare XML header
        while read LINE;
         do
                print $LINE >> $SQL_QUERY_OUT
         done <<EOF
<?xml version="1.0"?>
<?mso-application progid="Excel.Sheet"?>
<Workbook xmlns="urn:schemas-microsoft-com:office:spreadsheet"
xmlns:o="urn:schemas-microsoft-com:office:office"
xmlns:x="urn:schemas-microsoft-com:office:excel"
xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet"
xmlns:html="http://www.w3.org/TR/REC-html40">
<DocumentProperties xmlns="urn:schemas-microsoft-com:office:office">
<Author>b</Author>
<LastAuthor>b</LastAuthor>
<Created>2013-08-18T18:36:57Z</Created>
<Company>b</Company>
<Version>11.9999</Version>
</DocumentProperties>
<ExcelWorkbook xmlns="urn:schemas-microsoft-com:office:excel">
<WindowHeight>11385</WindowHeight>
<WindowWidth>15180</WindowWidth>
<WindowTopX>360</WindowTopX>
<WindowTopY>90</WindowTopY>
<ProtectStructure>False</ProtectStructure>
<ProtectWindows>False</ProtectWindows>
</ExcelWorkbook>
<Styles>
<Style ss:ID="Default" ss:Name="Normal">
<Alignment ss:Vertical="Bottom"/>
<Borders/>
<Font ss:FontName="Arial Cyr"/>
<Interior/>
<NumberFormat/>
<Protection/>
</Style>
<Style ss:ID="s21">
<Borders>
<Border ss:Position="Bottom" ss:LineStyle="Continuous" ss:Weight="1"/>
<Border ss:Position="Left" ss:LineStyle="Continuous" ss:Weight="1"/>
<Border ss:Position="Right" ss:LineStyle="Continuous" ss:Weight="1"/>
<Border ss:Position="Top" ss:LineStyle="Continuous" ss:Weight="1"/>
</Borders>
<Font ss:FontName="Arial Cyr" ss:Bold="1"/>
</Style>
<Style ss:ID="s22">
<Borders>
<Border ss:Position="Bottom" ss:LineStyle="Continuous" ss:Weight="1"/>
<Border ss:Position="Left" ss:LineStyle="Continuous" ss:Weight="1"/>
<Border ss:Position="Right" ss:LineStyle="Continuous" ss:Weight="1"/>
<Border ss:Position="Top" ss:LineStyle="Continuous" ss:Weight="1"/>
</Borders>
</Style>
</Styles>
<Worksheet ss:Name="Sheet1">
<Table>
<Column ss:Width="50"/>
<Column ss:Width="50"/>
<Column ss:Width="50"/>
EOF
        print "<Row>"   >> $SQL_QUERY_OUT
        for i in `awk 'NR==1' $f1`
         do
                print ' <Cell ss:StyleID="s21"><Data ss:Type="String">'$i'</Data></Cell>' >> $SQL_QUERY_OUT
         done
        print "</Row>"  >> $SQL_QUERY_OUT
        awk 'NR>1' $f1|while read LINE;
         do
                print "<Row>"   >> $SQL_QUERY_OUT
                print "$LINE"|awk -F\t '{for (i=1;i<=NF;i++){print $i}}' > $f2
                while read i;
                 do
                        k=$(print "$i"|sed 's/&/&amp;/g')       # replace ampersand
                        print ' <Cell ss:StyleID="s22"><Data ss:Type="String">'$k'</Data></Cell>' >> $SQL_QUERY_OUT
                 done < $f2
                print "</Row>"  >> $SQL_QUERY_OUT
         done

        # add XML footer
        while read LINE;
         do
                print $LINE >> $SQL_QUERY_OUT
         done <<EOF
  </Table>
  <WorksheetOptions xmlns="urn:schemas-microsoft-com:office:excel">
   <PageSetup>
    <PageMargins x:Bottom="0.984251969" x:Left="0.78740157499999996"
     x:Right="0.78740157499999996" x:Top="0.984251969"/>
   </PageSetup>
   <Print>
    <ValidPrinterInfo/>
    <HorizontalResolution>200</HorizontalResolution>
    <VerticalResolution>200</VerticalResolution>
   </Print>
   <Selected/>
   <Panes>
    <Pane>
     <Number>3</Number>
     <ActiveRow>1</ActiveRow>
     <ActiveCol>2</ActiveCol>
    </Pane>
   </Panes>
   <ProtectObjects>False</ProtectObjects>
   <ProtectScenarios>False</ProtectScenarios>
  </WorksheetOptions>
 </Worksheet>
</Workbook>
EOF
        [ -e $f1 ] && rm $f1
        [ -e $f2 ] && rm $f2
}

ora_process_query()
{
        [ $err1 -ne 0 ] && return

        print "Start Oracle ..."|tee -a $DELTA_LOG
        print "Query: "         |tee -a $DELTA_LOG

        print "~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~" |tee -a $DELTA_LOG
        print $SQL_QUERY                        |tee -a $DELTA_LOG
        print "~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~" |tee -a $DELTA_LOG

        print "conn $DBCONN"            > $SQL_SCRIPT
        print "set feed off"            >> $SQL_SCRIPT
        print "set pagesize 0"          >> $SQL_SCRIPT
        print 'set colsep ""","""'      >> $SQL_SCRIPT
        print "set linesize 5000"       >> $SQL_SCRIPT
        print "set pagesize 5000"       >> $SQL_SCRIPT
        print "set trimspool on"        >> $SQL_SCRIPT
        print "spool $SQL_QUERY_OUT"    >> $SQL_SCRIPT
        print "$SQL_QUERY"              >> $SQL_SCRIPT
        print "exit"                    >> $SQL_SCRIPT

        sqlplus -s /nolog @$SQL_SCRIPT >> $SQL_LOG
        err1=$?                                         # save exit code

        err2=`grep -i ora- $SQL_LOG|wc -l|awk '{print $1}'` # if SQL_LOG has any ORA- errors
        if [[ $err1 != 0 || $err2 != 0 ]]; then
                finish "SQL Failure!"
        fi

        sed -e 's/[ ]*["][,]["]/","/g;s/^[ ]*/"/g;s/$/"/g' $SQL_QUERY_OUT|sed -e '3d'|sed -e '1d' > /tmp/_f1
        mv /tmp/_f1 $SQL_QUERY_OUT
        [ "$SQL_QUERY_TYPE" == "XML" ] && generate_xml
}
ora_work()
{
        [ $err1 -ne 0 ] && return
        print "Start Oracle ..." |tee -a $DELTA_LOG
        print "conn $DBCONN" > $SQL_SCRIPT
        # put all commands to Oracle database in ordered way
        for i in `awk '/^SQL_FILE/ {print substr($0,9,256)}' $CFG_FILE|sort -k 1n|awk -F= '{print $1}'`
         do
                eval print "\$SQL_FILE$i" >> $SQL_SCRIPT
         done

        sqlplus /nolog @$SQL_SCRIPT >> $SQL_LOG
        err1=$?                                         # save exit code

        print "SQL LOG:"        |tee -a $DELTA_LOG
        cat $SQL_LOG            |tee -a $DELTA_LOG      # save sql log to the main log

        err2=`grep -i ora- $SQL_LOG|wc -l|awk '{print $1}'` # if SQL_LOG has any ORA- errors
        if [[ $err1 != 0 || $err2 != 0 ]]; then
                finish "SQL Failure!"
        fi
}

save_local()
{
        [ $err1 -ne 0 ] && return
        [ "$HIST_DIR" == "" ] && return

        print |tee -a $DELTA_LOG
        print -n "Save files to the local dir ..." |tee -a $DELTA_LOG
        for i in `awk '/^SFTP_FILE/ {print substr($0,10,256)}' $CFG_FILE|sort -k 1n|awk -F= '{print $1}'`
         do
                generated_file=$(eval print "put \$SFTP_FILE$i"|awk '{print $2}')       # fullname of the generated file
                fn=$(basename $generated_file)                                          # shortname of the generated file
                history_file=${fn%%.*}_$TS.${fn##*.}            # add timestamp between name and extension
                cp -p $generated_file $HIST_DIR/$history_file   # copying
         done
        print "done." |tee -a $DELTA_LOG
}

I do not follow your snippet?

1. You set 2 variables, but i am guessing 'f2' is used in the part you have not shown

2. Ignoring the useless use of cat, you cat a file that you do not test if it exists until after you have used it and then you remove it??

3. You print the incomplete header into a file you just removed

4. You give no advice as to where you got your example from, so not really able to assist further on why it would generate a single column

Originally Posted by grail I do not follow your snippet? 1. You set 2 variables, but i am guessing 'f2' is used in the part you have not shown 2. Ignoring the useless use of cat, you cat a file that you do not test if it exists until after you have used it and then you remove it?? 3. You print the incomplete header into a file you just removed 4. You give no advice as to where you got your example from, so not really able to assist further on why it would generate a single column

Please find edited code above, I added parts that was not shown before.
I still struggle to complete this task, so thank ahead for any help.

Ok so now we have more of the script. There are several issues with the code, so I will ask specifically which function is not performing as you wish and to please narrow it down
to what you have found?

You must understand you have presented nearly 200 lines of code, which to be perfectly honest looks a mess.
My suggestion would be to start with a single function and work out what it does and what you do not understand ... then come back and ask questions about those specific parts.

It would also possibly help, once you have done some work, to provide both the current and the expected output for the section you look at.

Originally Posted by grail Ok so now we have more of the script. There are several issues with the code, so I will ask specifically which function is not performing as you wish and to please narrow it down to what you have found? You must understand you have presented nearly 200 lines of code, which to be perfectly honest looks a mess. My suggestion would be to start with a single function and work out what it does and what you do not understand ... then come back and ask questions about those specific parts. It would also possibly help, once you have done some work, to provide both the current and the expected output for the section you look at.

Well, it worked just fine before migration and now it generates result file not properly.

We have columns in result file:

RB FB FBH AB ABH FBAD FBHAD FBAC FBHAC ABAA ABHAA

and values of these columns are all inserted into single RB column

941 189 211 2 3 0 0 0 0 3 0

They should be placed each into another column.
How do I perform it?






cat /etc/*-release
LSB_VERSION=base-4.0-amd64:base-4.0-noarch:core-4.0-amd64:core-4.0-noarch:graphics-4.0-amd64:graphics-4.0-noarch:printing-4.0-amd64:printing-4.0-noarch
Red Hat Enterprise Linux Server release 6.6 (Santiago)
Red Hat Enterprise Linux Server release 6.6 (Santiago)

cat $SQL_QUERY_OUT |sed -e 's/^"//g;s/"$//g;s/","/|/g'|tr '|' '\t' > $f1

sed accepts flename as input parameter
..
checked file exists and removed it ?? while it should be checked before the line with cat and sed.

So ok, please narrow down to whats not working !!!

Originally Posted by SAbhi sed accepts flename as input parameter .. checked file exists and removed it ?? while it should be checked before the line with cat and sed.

This looks fine, because result file is created successfully.

So ok, please narrow down to whats not working !!!

        for i in `awk 'NR==1' $f1`
         do
                print ' <Cell ss:StyleID="s21"><Data ss:Type="String">'$i'</Data></Cell>' >> $SQL_QUERY_OUT
         done
        print "</Row>"  >> $SQL_QUERY_OUT
        awk 'NR>1' $f1|while read LINE;
         do
                print "<Row>"   >> $SQL_QUERY_OUT
                print "$LINE"|awk -F\t '{for (i=1;i<=NF;i++){print $i}}' > $f2
                while read i;
                 do
                        k=$(print "$i"|sed 's/&/&amp;/g')       # replace ampersand
                        print ' <Cell ss:StyleID="s22"><Data ss:Type="String">'$k'</Data></Cell>' >> $SQL_QUERY_OUT
                 done < $f2
                print "</Row>"  >> $SQL_QUERY_OUT
         done

        # add XML footer
        while read LINE;
         do
                print $LINE >> $SQL_QUERY_OUT
         done <<EOF

I found that this line generates values

941 189 211 2 3 0 0 0 0 3 0

                        print ' <Cell ss:StyleID="s22"><Data ss:Type="String">'$k'</Data></Cell>' >> $SQL_QUERY_OUT

I tried to play with "$k" variable and added "r" before, so this line is now

                        print ' <Cell ss:StyleID="s22"><Data ss:Type="String">'r$k'</Data></Cell>' >> $SQL_QUERY_OUT

and result values are

r941 189 211 2 3 0 0 0 0 3 0

(all in one single column).

So now my need is to understand how to divide values into different columns.
Any help?

This looks fine, because result file is created successfully.

Thats up to you, my work is to suggest the right use of utilities given.

941 189 211 2 3 0 0 0 0 3 0

Isee the values are separated by an space, there are many ways to store them one of which is arrays..
use array to store all values in different indexes and print them in columns you need.
Since you think you are not doing any other mistake or useless uses this is my suggestion.

Before:

awk -F\t

now:

awk '-F\t'

Works great.
Thanks for attention.

So as you have said it works great I am guessing this is SOLVED? (please mark as such)

I am interested that your previous reply to me suddenly mentioned migration which you never mentioned before.
Little details are important if you want people to be able to help you properly.

As for your new solution, you should virtually always quote unusual characters other wise the shell may make changes you are not prepared for.
I would suggest only quoting the the tab (\t) as -F is a switch to awk and quoting it may make awk not behave as you expect (maybe not now but later on)

Marked thread solved. Thanks to all.