LinuxQuestions.org
Download your favorite Linux distribution at LQ ISO.
Go Back   LinuxQuestions.org > Forums > Linux Forums > Linux - Newbie
User Name
Password
Linux - Newbie This Linux forum is for members that are new to Linux.
Just starting out and have a question? If it is not in the man pages or the how-to's this is the place!

Notices


Reply
  Search this Thread
Old 12-03-2014, 02:12 AM   #1
marchelloUA
LQ Newbie
 
Registered: Nov 2014
Posts: 22

Rep: Reputation: Disabled
Question 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.

Code:
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
}

Last edited by marchelloUA; 12-05-2014 at 07:40 AM.
 
Old 12-03-2014, 02:31 AM   #2
grail
LQ Guru
 
Registered: Sep 2009
Location: Perth
Distribution: Manjaro
Posts: 9,243

Rep: Reputation: 2684Reputation: 2684Reputation: 2684Reputation: 2684Reputation: 2684Reputation: 2684Reputation: 2684Reputation: 2684Reputation: 2684Reputation: 2684Reputation: 2684
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
 
Old 12-05-2014, 07:42 AM   #3
marchelloUA
LQ Newbie
 
Registered: Nov 2014
Posts: 22

Original Poster
Rep: Reputation: Disabled
Question

Quote:
Originally Posted by grail View Post
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.
 
Old 12-05-2014, 09:16 AM   #4
grail
LQ Guru
 
Registered: Sep 2009
Location: Perth
Distribution: Manjaro
Posts: 9,243

Rep: Reputation: 2684Reputation: 2684Reputation: 2684Reputation: 2684Reputation: 2684Reputation: 2684Reputation: 2684Reputation: 2684Reputation: 2684Reputation: 2684Reputation: 2684
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.
 
Old 12-05-2014, 12:27 PM   #5
marchelloUA
LQ Newbie
 
Registered: Nov 2014
Posts: 22

Original Poster
Rep: Reputation: Disabled
Question

Quote:
Originally Posted by grail View Post
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)

Last edited by marchelloUA; 12-05-2014 at 12:35 PM.
 
Old 12-05-2014, 12:53 PM   #6
SAbhi
Member
 
Registered: Aug 2009
Location: Bangaluru, India
Distribution: CentOS 6.5, SuSE SLED/ SLES 10.2 SP2 /11.2, Fedora 11/16
Posts: 664

Rep: Reputation: 80
Quote:
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 !!!
 
Old 12-05-2014, 01:22 PM   #7
marchelloUA
LQ Newbie
 
Registered: Nov 2014
Posts: 22

Original Poster
Rep: Reputation: Disabled
Question

Quote:
Originally Posted by SAbhi View Post
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.

Quote:
So ok, please narrow down to whats not working !!!
Code:
        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

Code:
                        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

Code:
                        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?
 
Old 12-06-2014, 02:05 AM   #8
SAbhi
Member
 
Registered: Aug 2009
Location: Bangaluru, India
Distribution: CentOS 6.5, SuSE SLED/ SLES 10.2 SP2 /11.2, Fedora 11/16
Posts: 664

Rep: Reputation: 80
Quote:
This looks fine, because result file is created successfully.
Thats up to you, my work is to suggest the right use of utilities given.


Quote:
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.
 
Old 12-06-2014, 05:54 AM   #9
marchelloUA
LQ Newbie
 
Registered: Nov 2014
Posts: 22

Original Poster
Rep: Reputation: Disabled
Before:
Quote:
awk -F\t
now:
Quote:
awk '-F\t'
Works great.
Thanks for attention.
 
Old 12-06-2014, 07:15 AM   #10
grail
LQ Guru
 
Registered: Sep 2009
Location: Perth
Distribution: Manjaro
Posts: 9,243

Rep: Reputation: 2684Reputation: 2684Reputation: 2684Reputation: 2684Reputation: 2684Reputation: 2684Reputation: 2684Reputation: 2684Reputation: 2684Reputation: 2684Reputation: 2684
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)
 
Old 12-06-2014, 08:58 AM   #11
marchelloUA
LQ Newbie
 
Registered: Nov 2014
Posts: 22

Original Poster
Rep: Reputation: Disabled
Marked thread solved. Thanks to all.
 
  


Reply

Tags
xml


Thread Tools Search this Thread
Search this Thread:

Advanced Search

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off



Similar Threads
Thread Thread Starter Forum Replies Last Post
AWK. generate limited files from xml frambau Programming 5 02-10-2012 08:11 AM
[SOLVED] CLI commands to generate random result from specified list BinaryMan Linux - General 14 10-13-2011 09:32 AM
redirect sql result to a file ShaqDiesel Programming 1 11-05-2008 03:28 PM
generate xml from nmap output shourya21 Linux - Newbie 1 01-24-2008 09:54 AM
Foomatic: How to generate ppd drivers from xml files? johnny1959 Linux - Newbie 0 02-17-2004 05:27 AM


All times are GMT -5. The time now is 12:17 PM.

Main Menu
Advertisement
My LQ
Write for LQ
LinuxQuestions.org is looking for people interested in writing Editorials, Articles, Reviews, and more. If you'd like to contribute content, let us know.
Main Menu
Syndicate
RSS1  Latest Threads
RSS1  LQ News
Twitter: @linuxquestions
Facebook: linuxquestions Google+: linuxquestions
Open Source Consulting | Domain Registration