From sql Insert Query to XML format

Shell Programming and Scripting
1

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>'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\('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>'FCPM9'</Value>
                                                            </SqdCol>
                                                            <SqdCol ColRef="DL\_SERVICE_CODE">
                                                                            <Value>'v750'</Value>
                                                            </SqdCol>
                                                            <SqdCol ColRef="ROUNDING_METHOD">
                                                                            <Value>'A'</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

2

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...

3

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

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
4

Heres the output i got

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