Shell Programming and Scripting

Hi

How do I translate

Let say

Cat inserts.sql

gives

Insert into PM9_TAXATION_ROUNDING
(STATE_GECODE, TAX_TYPE, TAX_AUTHORITY, SYS_CREATION_DATE, SYS_UPDATE_DATE, APPLICATION_ID, DL_SERVICE_CODE, ROUNDING_METHOD)
Values
('xx', 'xx', 'x', TO_DATE('10/26/2012 13:01:20', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('10/26/2012 13:01:20', 'MM/DD/YYYY HH24:MI:SS'), 'FCPM9 ', 'v750 ', 'A');


Need to Translate this to


<Sqd CreDate="2012-10-26 13:02:05" UpdDate="2012-10-26 13:02:05" ModVer="v750" Customizable="true">
<SqdCol ColRef="STATE_GECODE">
<Value>&apos;xx&apos;</Value>
</SqdCol>
<SqdCol ColRef="TAX_TYPE">
<Value>&apos;xx&apos;</Value>
</SqdCol>
<SqdCol ColRef="TAX_AUTHORITY">
<Value>&apos;x&apos;</Value>
</SqdCol>
<SqdCol ColRef="SYS_CREATION_DATE">
<Value>TO_DATE('2012-10-26 13:01:20', 'YYYY-MM-DD HH24:MI:SS')</Value>
</SqdCol>
<SqdCol ColRef="SYS_UPDATE_DATE">
<Value>TO_DATE('2012-10-26 13:01:20', 'YYYY-MM-DD HH24:MI:SS')</Value>
</SqdCol>
<SqdCol ColRef="APPLICATION_ID">
<Value>&apos;FCPM9&apos;</Value>
</SqdCol>
<SqdCol ColRef="DL_SERVICE_CODE">
<Value>&apos;v750&apos;</Value>
</SqdCol>
<SqdCol ColRef="ROUNDING_METHOD">
<Value>&apos;A&apos;</Value>
</SqdCol>
</Sqd>



In Unix.




PS - Table name , / Inserts are subject to change


So what I want is something like this



<Sqd CreDate="$date" UpdDate="$date " ModVer="v750" Customizable="true">
<SqdCol ColRef="$COL1">
<Value$Val1;</Value>
</SqdCol>
<SqdCol ColRef="$COL2">
<Value>$Val2;</Value>
</SqdCol>
.
.
.
<SqdCol ColRef="$COLn">
<Value>$Valn;</Value>
</SqdCol>
</Sqd>


Best Regards

Anuj Krishnakant Puranik

It's good you use code tags. But put the whole section in one tag. You don't need a separate tag for each line. It makes it hard to read...

Here is a gawk code based on some assumptions, feel free to modify it as per your requirement:

Code:

DT=$( date +"%Y-%m-%d %H:%M:%S" )
gawk -F',' -v dt="$DT" '
        BEGIN {
                        IGNORECASE = 1
        }

        NR == 1 {
                        table_name = $3
        }

        NR > 1 {
                        for ( i = 1; i <= NF; i++ )
                        {
                                if ( $i ~ /values/ )
                                        v = 1
                                if ( ($i ~ /\(/ || $i ~ /\)/) && !(v) )
                                        gsub ( /\(|\)/, X, $i )
                                sub(/^[ \t]+/, X, $i)
                                if ( !(v) && $i !~ /^$/ )
                                        C[++m] = $i
                                if ( v && $i !~ /values/ )
                                {
                                        if ( $i ~ /\(/ && $( i + 1 ) ~ /\)/ )
                                        {
                                                j = $i "," $( i + 1 )
                                                V[++n] = j
                                                ++i
                                        }
                                        else if ( $i !~ /^$/ )
                                        {
                                                if ( $i ~ /\(/ || $i ~ /\)/ || $i ~ /\;/ )
                                                        gsub ( /\(|\)|\;/, X, $i )
                                                V[++n] = $i
                                        }
                                }
                        }
        }

        END {
                print "<Sqd CreDate=\"" dt "\" UpdDate=\"" dt "\" ModVer=\"v750\" Customizable=\"true\">"
                for ( k = 1; k <= m; k++ )
                {
                        print "<SqdCol ColRef=\"" C[k] "\">"
                        print "<Value>" V[k] "</Value>"
                        print "</SqdCol>"
                }
                print "</Sqd>"
        }

' sql_file

Heres the output i got

Code:

<Sqd CreDate="2013-05-08 01:10:42" UpdDate="2013-05-08 01:10:42" ModVer="v750" Customizable="true">
<SqdCol ColRef="STATE_GECODE">
<Value>'xx'</Value>
</SqdCol>
<SqdCol ColRef="TAX_TYPE">
<Value>'xx'</Value>
</SqdCol>
<SqdCol ColRef="TAX_AUTHORITY">
<Value>'x'</Value>
</SqdCol>
<SqdCol ColRef="SYS_CREATION_DATE">
<Value>TO_DATE('10/26/2012 13:01:20', 'MM/DD/YYYY HH24:MI:SS')</Value>
</SqdCol>
<SqdCol ColRef="SYS_UPDATE_DATE">
<Value>TO_DATE('10/26/2012 13:01:20', 'MM/DD/YYYY HH24:MI:SS')</Value>
</SqdCol>
<SqdCol ColRef="APPLICATION_ID">
<Value>'FCPM9 '</Value>
</SqdCol>
<SqdCol ColRef="DL_SERVICE_CODE">
<Value>'v750 '</Value>
</SqdCol>
<SqdCol ColRef="ROUNDING_METHOD">
<Value>'A'</Value>
</SqdCol>
</Sqd>