Adding text on certain positions but exclude other positions

[meisn]

Hello,

I have to do some changes in a xml. I have to add column-referneces to certain tables with column-refernces ending with _SK.

The file looks like this (i have marked the columns in bold):

<table name="RA_F_B2_XV" skip-update="false">
<column-ref name="B2_SK"/>
<column-ref name="QRRFLG"/>
<column-ref name="RWBAAS"/>
<column-ref name="RWBFAS"/>
<column-ref name="INST_ID"/>
<column-ref name="UNDISCD"/>
<column-ref name="ORGNOTAMT"/>
<column-ref name="QRRLIMCPY"/>
<column-ref name="RWABAAGRS"/>
<column-ref name="RWABFAGRS"/>
<column-ref name="RWBAAEPES"/>
<column-ref name="SEVAVGTRN"/>
<column-ref name="RATSCEUSED"/>
<column-ref name="OVDUEDYSASV"/>
<column-ref name="PRDCPYWGTP1"/>
<column-ref name="PAY_CTRCTAMT"/>
<column-ref name="RCVYAMTPLYTD"/>
<column-ref name="RCV_CTRCTAMT"/>
<column-ref name="RWABAAGREPES"/>
<column-ref name="DBETY_LEID_SK"/>
<column-ref name="ORGNOTAMT_FLG"/>
<column-ref name="REMLIFETIMEB2_GK_SK"/>
</table>
<table name="RA_D_DBETY_LEID_IS_V" skip-update="false">
<column-ref name="DBETY_LEID_SK"/>
<column-ref name="DBETY_LEID_L1_BK"/>
<column-ref name="DBETY_LEID_L1_SK"/>
<column-ref name="DBETY_LEID_L2_BK"/>
<column-ref name="DBETY_LEID_L2_SK"/>
<column-ref name="DBETY_LEID_L3_BK"/>
<column-ref name="DBETY_LEID_L3_SK"/>
<column-ref name="DBETY_LEID_BK"/>
<column-ref name="DBETY_LEID_L1_DESC"/>
<column-ref name="DBETY_LEID_L2_DESC"/>
<column-ref name="DBETY_LEID_L3_DESC"/>
<column-ref name="DBETY_LEID_DESC"/>
</table>
<table name="RA_D_DBETY_LEID" skip-update="false">
<column-ref name="DBETY_LEID_SK"/>
<column-ref name="DBETY_LEID_L1_BK"/>
<column-ref name="DBETY_LEID_L1_SK"/>
<column-ref name="DBETY_LEID_L2_BK"/>
<column-ref name="DBETY_LEID_L2_SK"/>
<column-ref name="DBETY_LEID_L3_BK"/>
<column-ref name="DBETY_LEID_L3_SK"/>
<column-ref name="DBETY_LEID_BK"/>
<column-ref name="DBETY_LEID_L1_DESC"/>
<column-ref name="DBETY_LEID_L2_DESC"/>
<column-ref name="DBETY_LEID_L3_DESC"/>
<column-ref name="DBETY_LEID_DESC"/>
<column-ref name="X_PKEY"/>
</table>

The result should look like this:

<table name="RA_F_B2_XV" skip-update="false">
<column-ref name="B2_SK"/>
...
<column-ref name="DBETY_LEID_SK"/>
<column-ref name="DBETY_LEID_HSK"/>
...
</table>
...
<table name="RA_D_DBETY_LEID" skip-update="false">
<column-ref name="DBETY_LEID_SK"/>
<column-ref name="DBETY_LEID_HSK"/>
...
<column-ref name="DBETY_LEID_L1_SK"/>
<column-ref name="DBETY_LEID_L1_HSK"/>
...
<column-ref name="DBETY_LEID_L2_SK"/>
<column-ref name="DBETY_LEID_L2_HSK"/>
...
<column-ref name="DBETY_LEID_L3_SK"/>
<column-ref name="DBETY_LEID_L3_HSK"/>
...
</table>

To add these columns I had used WildEdit with the following:

Search for:

(?!<column-ref name="(B2|LCX|COLL|CTRY|DERIV|DISCL|DISCLDM|DLV_SK|RET|DMCTRY|DMLCG|DMP1|DMP1DRILL|ELEC|ELECDM|KWG13|SEC|P1|PRLGVA)_SK"/>)(<column-ref name="[^"]*)_SK"/>

Replace with:


$0
$2_HSK"/>

The alternative is used to prevent updating the table key columns (I had to do this for more files)

This works fine by the way. My problem is, that the tables are heterogenous and that the 2nd table (ending with 'IS_V') must not get these HSK-columns. I have tried to work this out by extending the lookahead expression but it doesn't work as expected. If I'm trying this:

(?!<table name="[^"]*IS_V".*?<column-ref name="(B2|LCX|COLL|CTRY|DERIV|DISCL|DISCLDM|DLV_SK|RET|DMCTRY|DMLCG|DMP1|DMP1DRILL|ELEC|ELECDM|KWG13|SEC|P1|PRLGVA)_SK"/>)(<column-ref name="[^"]*)_SK"/>

and using the same replacement expression it will update both the table key column and the columns of the IS_V-tables.

Did anyone have a solution that works? The alternative is to delete the redundant references maually from the tabledefinitions.

Regards,

Meisn

P.S.: Sorry for so much textinput but I thought this would help to understand this better.

[Bob Hansen]

Maybe I am missing something, but are you trying to do this?

Replace the ending on all lines that end with _SK"/> with _HSK"/> ?

If Yes, then do this in TextPad:
Search for: (^.*_)SK"/>
Replace with: \1_HSK"/>

This:
<column-ref name="DBETY_LEID_L3_SK"/>
Becomes this:
<column-ref name="DBETY_LEID_L3_HSK"/>
===========================
OR
If adding an additional line with the _HSK, do this:
Search for: (^.*_)SK"/>
Replace with: \0\n\1HSK"/>

This:
<column-ref name="DBETY_LEID_L3_SK"/>
Becomes this:
<column-ref name="DBETY_LEID_L3_SK"/>
<column-ref name="DBETY_LEID_L3_HSK"/>

Using Editor with POSIX syntax, Regular Expressions has checkmark.

[ben_josephs]

Meisn wants to add a line after each line like
<column-ref name="prefix_SK"/>
only if prefix is not B2 or LCX or ...
But you can't do "not" with TextPad regular expressions.

Meisn's WildEdit regex
(?!<column-ref name="(B2|LCX|etc)_SK"/>)(<column-ref name="[^"]*)_SK"/>
matches
<column-ref name="[^"]*_SK"/>
only if it doesn't match
<column-ref name="(B2|LCX|etc)_SK"/>
at the same position. This is as required.

The regex
(?!<table name="[^"]*IS_V".*?<column-ref name="(B2|LCX|etc)_SK"/>)(<column-ref name="[^"]*)_SK"/>
matches
<column-ref name="[^"]*_SK"/>
only if it doesn't match
<table name="[^"]*IS_V".*?<column-ref name="(B2|LCX|etc)_SK"/>
at the same position.
But it can't possibly match anything beginning with
<table
at the same position as it matches something that begins with
<column-ref
So the negative look-ahead assertion has no effect, and does not stop the regex matching within the table whose name="RA_D_DBETY_LEID_IS_V".

BTW,
(?!<column-ref name="(B2|LCX|etc)_SK"/>)(<column-ref name="[^"]*)_SK"/>
can be simplified to
(<column-ref name="(?!B2|LCX|etc)[^"]*)_SK"/>

I suspect that you'll have difficulty solving this with regex search and replace alone. Once a regex has matched from the beginning of a table to an element in it, the search position has moved beyond that element; so the regex can't match from the beginning of the table again.

I would do this with a script.

[Bob Hansen]

only if prefix is not B2 or LCX or ...

I knew I was missing something.
I never tried to read the expression he provided.
Usually try to tackle a solution with no bias.
---------------
I should probably remove my wrong solution to eliminate any confusion.

Thanks once again ben_josephs

[meisn]

Hi,

Thank you Ben for the simplification of the expression.

BTW,
(?!<column-ref name="(B2|LCX|etc)_SK"/>)(<column-ref name="[^"]*)_SK"/>
can be simplified to
(<column-ref name="(?!B2|LCX|etc)[^"]*)_SK"/>

My knowledge in scripts is very very basic. I had thought about learning perl (mostly because of Jeffrey Friedel's Regex "bible"), but this will take me some time, I think.

Basically this xml will be created from metadata using oracle's xml-functions in sql plus. This was only one part of a quick fix, because there were some major changes in the definitions.
In the future I will implement this features in the sql-xml-generation code.

Once more I have to thank you both for your suggestions.

Regards,

Meisn

[ben_josephs]

fixcolrefs.pl:

Code: Select all

use warnings ;
use strict   ;

my $exclStr = join '|', qw ( B2 LCX COLL CTRY DERIV DISCL DISCLDM
                             DLV_SK RET DMCTRY DMLCG DMP1 DMP1DRILL
                             ELEC ELECDM KWG13 SEC P1 PRLGVA ) ;
my $excl    = qr|$exclStr| ;

my $tableName = '' ;

for my $line ( <> )
{
  print $line ;

  if    ( my $name = ( $line =~ m|^\s*<table name="([^"]+)"| )[ 0 ] )
  {
    $tableName = $name ;
  }
  elsif ( $line =~ m|^\s*</table>| )
  {
    $tableName = '' ;
  }
  elsif (  $line =~ m|^\s*<column-ref name="(?!$excl)([^"]+_SK)"|
        && $tableName !~ m|IS_V$| )
  {
    $line =~ s|_SK|_HSK| ;

    print $line ;
  }
}

Use as in

Code: Select all

perl fixcolrefs.pl < in.xml > out.xml

[meisn]

Many many many thanks Ben!!!

I will try this as soon as possible.

I hope that I can help you with anything else.

Yor are the best Ben!

Regards,

Meisn