i'd like to create from a simple XML text SQL using RegExp

Question

just simple, I have such data, thousands of <MigratedData> elements, normally we load them using XMLLoader application, but this is a special case now, I have to update the database myself using the mapping:

<MigratedData> <migrationNr>12123456</migrationNr> <idOldSystem>33398088801</idOldSystem> </MigratedData>

<MigratedData> <migrationNr>6767</migrationNr> <idOldSystem>21100077878</idOldSystem> </MigratedData>

<MigratedData> <migrationNr>767066</migrationNr> <idOldSystem>4545566767676</idOldSystem> </MigratedData>

I will create such SQL Statements:

update table_1 t1 set t1.accountNr = idOldSystem__Value__from_XML
where t1.id = migrationNr__from__XML ;

Does there exist a simple way to achieve this vis RegExp, preferrably in UltraEdit regular expression engine?

Answer 1

Why not to use simple xslt transformation on your xml and create update script as you specified?

Make your xml like this

<?xml version="1.0" encoding="utf-8" ?>
<root>
    <MigratedData>
     <migrationNr>12123456</migrationNr>
     <idOldSystem>33398088801</idOldSystem>
    </MigratedData>
    <MigratedData>
     <migrationNr>6767</migrationNr>
     <idOldSystem>21100077878</idOldSystem>
    </MigratedData>
    <MigratedData>
     <migrationNr>767066</migrationNr>
     <idOldSystem>4545566767676</idOldSystem>
    </MigratedData>
</root>

And apply XSLT like this:

<?xml version="1.0" encoding="utf-8"?>
<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
    xmlns:msxsl="urn:schemas-microsoft-com:xslt" exclude-result-prefixes="msxsl"
>
    <xsl:output method="text" indent="yes"/>

    <xsl:template match="MigratedData">
update table_1 t1 set t1.accountNr = <xsl:value-of select="./idOldSystem"/>
where t1.id =<xsl:value-of select="./migrationNr"/> ;

    </xsl:template>

</xsl:stylesheet>

your result will be

update table_1 t1 set t1.accountNr = 33398088801
where t1.id =12123456 ;
update table_1 t1 set t1.accountNr = 21100077878
where t1.id =6767 ;
update table_1 t1 set t1.accountNr = 4545566767676
where t1.id =767066 ;

Answer 2

I don't have UltraEdit (if you want answers strictly restricted to that product you may want to add it as a tag), but in other regex contexts I might do (adding line breaks only for legibility):

s/<MigratedData>\s*<migrationNr>(\d+)<\/migrationNr>\s*
  <idOldSystem>(\d+)<\/idOldSystem>\s*<\/MigratedData>/
  update table_1 t1 set t1.accountNr = \2 where t1.id = \1 ;

Details depends on what characters your specific regex system considers "magic", i.e. needing a backslash escape to be taken literally -- here I'm assuming a dialect with magic characters / as a separator, + and * to indicate repetition, and parentheses to form groups, so I'm escaping the slash to make it be literal.

The key idea is to grab as groups the non-empty sequences of digits (matching the rest literally, except for optional whitespace to be matched as \s*) and substitute them into the replacement text with back-references to the two groups, \1 and \2 (syntax of back-references in substitution may vary by dialect, i.e., maybe you need $1 and $2 in your specific context). It is somewhat of a pity that regex dialects vary so much among them, but the general concepts are reasonably reusable (kind of like SQL!-).