Xml file, remove table tags according the column id?

[Alan Kilborn]

@Nick-Kolios said in Xml file, remove table tags according the column id?:

Not sure if i am missing something else.

Did you tick the box for Regular expression search mode?

[guy038]

Hi, @nick-kolios,

Ah… OK, I suppose that problems are due to some leading indentation chars !

If you don’t mind and if your data is not too private, you could send me your xml file, by e-mail, to my temporary displayed address :

???@gmail.com

And I’ll probably see what don’t work yet !

guy038

PS :

May be, for the first regex S/R, you could try this version :

(?x-si) ^ [\t\x20]* ( <table \x20 name="products_to_products_extra_fields"> | </table> ) \R

[Nick Kolios]

@guy038 I tried this too, still not working i’m sending you right now the xml.
Even if you can’t find any solution i would like to say a big thank you. I appreciate your help and your time

[Nick Kolios]

@Alan-Kilborn
Hello,
Yes i did, the screenshot shows the settings

[Alan Kilborn]

@Nick-Kolios said in Xml file, remove table tags according the column id?:

the screenshot shows the settings

Ah, didn’t even notice that.
Screenshots should be pasted so they show directly as a picture in the flow of a forum post (certainly wouldn’t have missed it if it were done that way).

[Nick Kolios]

@Alan-Kilborn
Oh sorry about that, newbie here. I will do it the right way next time.
Btw, i was checking the forum and i must say this a great and very helpful community! Amazing!

[PeterJones]

Weird… based on the URL, the screenshot was pasted into the forum, but it went from being embedded to being a link; maybe @Nick-Kolios accidentally messed up the link syntax (deleted the leading ! from ![notepa.jpg](https://community.notepad-plus-plus.org/assets/uploads/files/1674221390404-notepa.jpg))

So that others can see the image when they read this later:

[guy038]

Hello, @nick-kolios,

I did receive your customfieldsnew3.xml file, with size 19,597,523 bytes and I’m going to study it.

See you later,

BR

guy038

[guy038]

Hello, @nick-kolios and All,

My previous second regex does not work due to the total size of your XML file which triggers a catastrophic break-down symptom of the regex engine :-((

However, I found out a better way to achieve your goal correctly !

---

Let’s start with this summary of your big file, of size = 5,039 bytes, as the INPUT text :

IMPORTANT : The original customfieldsnew3.xml file, of @nick-kolios, is an Unix file, but this summary, below, is a Window file !

<?xml version="1.0" encoding="utf-8"?>
<!--
- phpMyAdmin XML Dump
- version 5.2.0
- https://www.phpmyadmin.net
-
- Εξυπηρετητής: dedi1890.your-server.de
- Χρόνος δημιουργίας: 18 Ιαν 2023 στις 10:24:20
- Έκδοση διακομιστή: 10.5.18-MariaDB-0+deb11u1
- Έκδοση PHP: 8.0.27
-->

<pma_xml_export version="1.0" xmlns:pma="https://www.phpmyadmin.net/some_doc_url/">
    <!--
    - Structure schemas
    -->
    <pma:structure_schemas>
        <pma:database name="dodoni_eshop" collation="utf8mb4_unicode_ci" charset="utf8mb4">
            <pma:table name="products_to_products_extra_fields">
                CREATE TABLE `products_to_products_extra_fields` (
                  `products_id` int(11) NOT NULL DEFAULT 0,
                  `products_extra_fields_id` int(11) NOT NULL DEFAULT 0,
                  `language_id` int(11) NOT NULL,
                  `products_extra_fields_value` text DEFAULT NULL,
                  `products_extra_fields_value_description` varchar(255) NOT NULL DEFAULT '',
                  `products_extra_fields_value_to` int(11) NOT NULL,
                  PRIMARY KEY (`products_id`,`products_extra_fields_id`,`language_id`)
                ) ENGINE=InnoDB DEFAULT CHARSET=greek COLLATE=greek_general_ci;
            </pma:table>
        </pma:database>
    </pma:structure_schemas>

    <!--
    - Βάση δεδομένων: 'dodoni_eshop'
    -->
    <database name="dodoni_eshop">
        <!-- Πίνακας products_to_products_extra_fields -->
        <table name="products_to_products_extra_fields">
            <column name="products_id">78</column>
 <column name="products_extra_fields_id_23">Χρ. Γαλατόπουλου</column>
        </table>
        <table name="products_to_products_extra_fields">
            <column name="products_id">128</column>
 <column name="products_extra_fields_id_23">Δ. Κωστελένος</column>
        </table>
        <table name="products_to_products_extra_fields">
            <column name="products_id">833</column>
 <column name="products_extra_fields_id_23">Α. Δαμιανού</column>
        </table>
        <table name="products_to_products_extra_fields">
            <column name="products_id">1113</column>
 <column name="products_extra_fields_id_1">1993</column>
        </table>
        <table name="products_to_products_extra_fields">
            <column name="products_id">1113</column>
 <column name="products_extra_fields_id_3">960-248-660-0</column>
        </table>
        <table name="products_to_products_extra_fields">
            <column name="products_id">1113</column>
 <column name="products_extra_fields_id_6">Μαλακό εξώφυλλο</column>
        </table>
        <table name="products_to_products_extra_fields">
            <column name="products_id">1113</column>
 <column name="products_extra_fields_id_7">14Χ21</column>
        </table>
        <table name="products_to_products_extra_fields">
            <column name="products_id">1114</column>
 <column name="products_extra_fields_id_1">2000</column>
        </table>
        <table name="products_to_products_extra_fields">
            <column name="products_id">1114</column>
 <column name="products_extra_fields_id_3">978-960-385-034-2</column>
        </table>
        <table name="products_to_products_extra_fields">
            <column name="products_id">1114</column>
 <column name="products_extra_fields_id_6">Μαλακό εξώφυλλο</column>
        </table>
        <table name="products_to_products_extra_fields">
            <column name="products_id">1115</column>
 <column name="products_extra_fields_id_1">2007</column>
        </table>
        <table name="products_to_products_extra_fields">
            <column name="products_id">1115</column>
 <column name="products_extra_fields_id_6">Μαλακό εξώφυλλο</column>
        </table>
        <table name="products_to_products_extra_fields">
            <column name="products_id">1115</column>
 <column name="products_extra_fields_id_7">14x21</column>
        </table>
        <table name="products_to_products_extra_fields">
            <column name="products_id">1115</column>
 <column name="products_extra_fields_id_8">140</column>
        </table>
        <table name="products_to_products_extra_fields">
            <column name="products_id">1115</column>
 <column name="products_extra_fields_id_21">Ιανουάριος 2007</column>
        </table>
        <table name="products_to_products_extra_fields">
            <column name="products_id">1116</column>
 <column name="products_extra_fields_id_1">2000</column>
        </table>
        <table name="products_to_products_extra_fields">
            <column name="products_id">16951</column>
 <column name="products_extra_fields_id_68">Έλληνες συγγραφείς-Ιστορικοί</column>
        </table>
    </database>
</pma_xml_export>

• Move at the very beginning of the first line <table name="products_to_products_extra_fields"> of the file

• Run this first regex S/R :

  • SEARCH (?x-si) ^ [\t\x20]* ( <table \x20 name="products_to_products_extra_fields"> | </table> ) \R

  • REPLACE Leave EMPTY

This regex deletes any complete line containing <table \x20 name="products_to_products_extra_fields"> OR </table>

We get this TEMPORARY text :

<?xml version="1.0" encoding="utf-8"?>
<!--
- phpMyAdmin XML Dump
- version 5.2.0
- https://www.phpmyadmin.net
-
- Εξυπηρετητής: dedi1890.your-server.de
- Χρόνος δημιουργίας: 18 Ιαν 2023 στις 10:24:20
- Έκδοση διακομιστή: 10.5.18-MariaDB-0+deb11u1
- Έκδοση PHP: 8.0.27
-->

<pma_xml_export version="1.0" xmlns:pma="https://www.phpmyadmin.net/some_doc_url/">
    <!--
    - Structure schemas
    -->
    <pma:structure_schemas>
        <pma:database name="dodoni_eshop" collation="utf8mb4_unicode_ci" charset="utf8mb4">
            <pma:table name="products_to_products_extra_fields">
                CREATE TABLE `products_to_products_extra_fields` (
                  `products_id` int(11) NOT NULL DEFAULT 0,
                  `products_extra_fields_id` int(11) NOT NULL DEFAULT 0,
                  `language_id` int(11) NOT NULL,
                  `products_extra_fields_value` text DEFAULT NULL,
                  `products_extra_fields_value_description` varchar(255) NOT NULL DEFAULT '',
                  `products_extra_fields_value_to` int(11) NOT NULL,
                  PRIMARY KEY (`products_id`,`products_extra_fields_id`,`language_id`)
                ) ENGINE=InnoDB DEFAULT CHARSET=greek COLLATE=greek_general_ci;
            </pma:table>
        </pma:database>
    </pma:structure_schemas>

    <!--
    - Βάση δεδομένων: 'dodoni_eshop'
    -->
    <database name="dodoni_eshop">
        <!-- Πίνακας products_to_products_extra_fields -->
            <column name="products_id">78</column>
 <column name="products_extra_fields_id_23">Χρ. Γαλατόπουλου</column>
            <column name="products_id">128</column>
 <column name="products_extra_fields_id_23">Δ. Κωστελένος</column>
            <column name="products_id">833</column>
 <column name="products_extra_fields_id_23">Α. Δαμιανού</column>
            <column name="products_id">1113</column>
 <column name="products_extra_fields_id_1">1993</column>
            <column name="products_id">1113</column>
 <column name="products_extra_fields_id_3">960-248-660-0</column>
            <column name="products_id">1113</column>
 <column name="products_extra_fields_id_6">Μαλακό εξώφυλλο</column>
            <column name="products_id">1113</column>
 <column name="products_extra_fields_id_7">14Χ21</column>
            <column name="products_id">1114</column>
 <column name="products_extra_fields_id_1">2000</column>
            <column name="products_id">1114</column>
 <column name="products_extra_fields_id_3">978-960-385-034-2</column>
            <column name="products_id">1114</column>
 <column name="products_extra_fields_id_6">Μαλακό εξώφυλλο</column>
            <column name="products_id">1115</column>
 <column name="products_extra_fields_id_1">2007</column>
            <column name="products_id">1115</column>
 <column name="products_extra_fields_id_6">Μαλακό εξώφυλλο</column>
            <column name="products_id">1115</column>
 <column name="products_extra_fields_id_7">14x21</column>
            <column name="products_id">1115</column>
 <column name="products_extra_fields_id_8">140</column>
            <column name="products_id">1115</column>
 <column name="products_extra_fields_id_21">Ιανουάριος 2007</column>
            <column name="products_id">1116</column>
 <column name="products_extra_fields_id_1">2000</column>
            <column name="products_id">16951</column>
 <column name="products_extra_fields_id_68">Έλληνες συγγραφείς-Ιστορικοί</column>
    </database>
</pma_xml_export>

• Move at the very beginning of the first line <column name="products_id">78</column> of the file

• Run this second regex S/R :

  • SEARCH (?x-is) ^ .+ _id"> ( \d+ ) .+ \R .+ \R (?! .* \1 )

  • REPLACE $0\r\n

This regex adds an empty new line between two sections with different ID_Number, in lines <column name="products_id">.....</column>

We get this TEMPORARY text :

<?xml version="1.0" encoding="utf-8"?>
<!--
- phpMyAdmin XML Dump
- version 5.2.0
- https://www.phpmyadmin.net
-
- Εξυπηρετητής: dedi1890.your-server.de
- Χρόνος δημιουργίας: 18 Ιαν 2023 στις 10:24:20
- Έκδοση διακομιστή: 10.5.18-MariaDB-0+deb11u1
- Έκδοση PHP: 8.0.27
-->

<pma_xml_export version="1.0" xmlns:pma="https://www.phpmyadmin.net/some_doc_url/">
    <!--
    - Structure schemas
    -->
    <pma:structure_schemas>
        <pma:database name="dodoni_eshop" collation="utf8mb4_unicode_ci" charset="utf8mb4">
            <pma:table name="products_to_products_extra_fields">
                CREATE TABLE `products_to_products_extra_fields` (
                  `products_id` int(11) NOT NULL DEFAULT 0,
                  `products_extra_fields_id` int(11) NOT NULL DEFAULT 0,
                  `language_id` int(11) NOT NULL,
                  `products_extra_fields_value` text DEFAULT NULL,
                  `products_extra_fields_value_description` varchar(255) NOT NULL DEFAULT '',
                  `products_extra_fields_value_to` int(11) NOT NULL,
                  PRIMARY KEY (`products_id`,`products_extra_fields_id`,`language_id`)
                ) ENGINE=InnoDB DEFAULT CHARSET=greek COLLATE=greek_general_ci;
            </pma:table>
        </pma:database>
    </pma:structure_schemas>

    <!--
    - Βάση δεδομένων: 'dodoni_eshop'
    -->
    <database name="dodoni_eshop">
        <!-- Πίνακας products_to_products_extra_fields -->
            <column name="products_id">78</column>
 <column name="products_extra_fields_id_23">Χρ. Γαλατόπουλου</column>

            <column name="products_id">128</column>
 <column name="products_extra_fields_id_23">Δ. Κωστελένος</column>

            <column name="products_id">833</column>
 <column name="products_extra_fields_id_23">Α. Δαμιανού</column>

            <column name="products_id">1113</column>
 <column name="products_extra_fields_id_1">1993</column>
            <column name="products_id">1113</column>
 <column name="products_extra_fields_id_3">960-248-660-0</column>
            <column name="products_id">1113</column>
 <column name="products_extra_fields_id_6">Μαλακό εξώφυλλο</column>
            <column name="products_id">1113</column>
 <column name="products_extra_fields_id_7">14Χ21</column>

            <column name="products_id">1114</column>
 <column name="products_extra_fields_id_1">2000</column>
            <column name="products_id">1114</column>
 <column name="products_extra_fields_id_3">978-960-385-034-2</column>
            <column name="products_id">1114</column>
 <column name="products_extra_fields_id_6">Μαλακό εξώφυλλο</column>

            <column name="products_id">1115</column>
 <column name="products_extra_fields_id_1">2007</column>
            <column name="products_id">1115</column>
 <column name="products_extra_fields_id_6">Μαλακό εξώφυλλο</column>
            <column name="products_id">1115</column>
 <column name="products_extra_fields_id_7">14x21</column>
            <column name="products_id">1115</column>
 <column name="products_extra_fields_id_8">140</column>
            <column name="products_id">1115</column>
 <column name="products_extra_fields_id_21">Ιανουάριος 2007</column>

            <column name="products_id">1116</column>
 <column name="products_extra_fields_id_1">2000</column>

            <column name="products_id">16951</column>
 <column name="products_extra_fields_id_68">Έλληνες συγγραφείς-Ιστορικοί</column>

    </database>
</pma_xml_export>

Continuation on next post !

[guy038]

Hi, @nick-kolios and All,

• Insert an empty line right above the first line <column name="products_id">78</column> of the file

• From that location, run this third regex S/R :

SEARCH    ^\R

REPLACE   (        </table>\r\n        <table name="products_to_products_extra_fields">\r\n)

This regex replaces any true empty line with the two lines :

        </table>
        <table name="products_to_products_extra_fields">

We practically get our OUPTUT text :

<?xml version="1.0" encoding="utf-8"?>
<!--
- phpMyAdmin XML Dump
- version 5.2.0
- https://www.phpmyadmin.net
-
- Εξυπηρετητής: dedi1890.your-server.de
- Χρόνος δημιουργίας: 18 Ιαν 2023 στις 10:24:20
- Έκδοση διακομιστή: 10.5.18-MariaDB-0+deb11u1
- Έκδοση PHP: 8.0.27
-->

<pma_xml_export version="1.0" xmlns:pma="https://www.phpmyadmin.net/some_doc_url/">
    <!--
    - Structure schemas
    -->
    <pma:structure_schemas>
        <pma:database name="dodoni_eshop" collation="utf8mb4_unicode_ci" charset="utf8mb4">
            <pma:table name="products_to_products_extra_fields">
                CREATE TABLE `products_to_products_extra_fields` (
                  `products_id` int(11) NOT NULL DEFAULT 0,
                  `products_extra_fields_id` int(11) NOT NULL DEFAULT 0,
                  `language_id` int(11) NOT NULL,
                  `products_extra_fields_value` text DEFAULT NULL,
                  `products_extra_fields_value_description` varchar(255) NOT NULL DEFAULT '',
                  `products_extra_fields_value_to` int(11) NOT NULL,
                  PRIMARY KEY (`products_id`,`products_extra_fields_id`,`language_id`)
                ) ENGINE=InnoDB DEFAULT CHARSET=greek COLLATE=greek_general_ci;
            </pma:table>
        </pma:database>
    </pma:structure_schemas>

    <!--
    - Βάση δεδομένων: 'dodoni_eshop'
    -->
    <database name="dodoni_eshop">
        <!-- Πίνακας products_to_products_extra_fields -->
        </table>
        <table name="products_to_products_extra_fields">
            <column name="products_id">78</column>
 <column name="products_extra_fields_id_23">Χρ. Γαλατόπουλου</column>
        </table>
        <table name="products_to_products_extra_fields">
            <column name="products_id">128</column>
 <column name="products_extra_fields_id_23">Δ. Κωστελένος</column>
        </table>
        <table name="products_to_products_extra_fields">
            <column name="products_id">833</column>
 <column name="products_extra_fields_id_23">Α. Δαμιανού</column>
        </table>
        <table name="products_to_products_extra_fields">
            <column name="products_id">1113</column>
 <column name="products_extra_fields_id_1">1993</column>
            <column name="products_id">1113</column>
 <column name="products_extra_fields_id_3">960-248-660-0</column>
            <column name="products_id">1113</column>
 <column name="products_extra_fields_id_6">Μαλακό εξώφυλλο</column>
            <column name="products_id">1113</column>
 <column name="products_extra_fields_id_7">14Χ21</column>
        </table>
        <table name="products_to_products_extra_fields">
            <column name="products_id">1114</column>
 <column name="products_extra_fields_id_1">2000</column>
            <column name="products_id">1114</column>
 <column name="products_extra_fields_id_3">978-960-385-034-2</column>
            <column name="products_id">1114</column>
 <column name="products_extra_fields_id_6">Μαλακό εξώφυλλο</column>
        </table>
        <table name="products_to_products_extra_fields">
            <column name="products_id">1115</column>
 <column name="products_extra_fields_id_1">2007</column>
            <column name="products_id">1115</column>
 <column name="products_extra_fields_id_6">Μαλακό εξώφυλλο</column>
            <column name="products_id">1115</column>
 <column name="products_extra_fields_id_7">14x21</column>
            <column name="products_id">1115</column>
 <column name="products_extra_fields_id_8">140</column>
            <column name="products_id">1115</column>
 <column name="products_extra_fields_id_21">Ιανουάριος 2007</column>
        </table>
        <table name="products_to_products_extra_fields">
            <column name="products_id">1116</column>
 <column name="products_extra_fields_id_1">2000</column>
        </table>
        <table name="products_to_products_extra_fields">
            <column name="products_id">16951</column>
 <column name="products_extra_fields_id_68">Έλληνες συγγραφείς-Ιστορικοί</column>
        </table>
        <table name="products_to_products_extra_fields">
    </database>
</pma_xml_export>

Finally :

• Near the beginning of the file, remove the first line </table>, right after the comment <!-- Πίνακας products_to_products_extra_fields -->

• At the end of the file, remove the last line <table name="products_to_products_extra_fields">, right above the </database> line

And here is our expected OUTPUT XML file :

<?xml version="1.0" encoding="utf-8"?>
<!--
- phpMyAdmin XML Dump
- version 5.2.0
- https://www.phpmyadmin.net
-
- Εξυπηρετητής: dedi1890.your-server.de
- Χρόνος δημιουργίας: 18 Ιαν 2023 στις 10:24:20
- Έκδοση διακομιστή: 10.5.18-MariaDB-0+deb11u1
- Έκδοση PHP: 8.0.27
-->

<pma_xml_export version="1.0" xmlns:pma="https://www.phpmyadmin.net/some_doc_url/">
    <!--
    - Structure schemas
    -->
    <pma:structure_schemas>
        <pma:database name="dodoni_eshop" collation="utf8mb4_unicode_ci" charset="utf8mb4">
            <pma:table name="products_to_products_extra_fields">
                CREATE TABLE `products_to_products_extra_fields` (
                  `products_id` int(11) NOT NULL DEFAULT 0,
                  `products_extra_fields_id` int(11) NOT NULL DEFAULT 0,
                  `language_id` int(11) NOT NULL,
                  `products_extra_fields_value` text DEFAULT NULL,
                  `products_extra_fields_value_description` varchar(255) NOT NULL DEFAULT '',
                  `products_extra_fields_value_to` int(11) NOT NULL,
                  PRIMARY KEY (`products_id`,`products_extra_fields_id`,`language_id`)
                ) ENGINE=InnoDB DEFAULT CHARSET=greek COLLATE=greek_general_ci;
            </pma:table>
        </pma:database>
    </pma:structure_schemas>

    <!--
    - Βάση δεδομένων: 'dodoni_eshop'
    -->
    <database name="dodoni_eshop">
        <!-- Πίνακας products_to_products_extra_fields -->
        <table name="products_to_products_extra_fields">
            <column name="products_id">78</column>
 <column name="products_extra_fields_id_23">Χρ. Γαλατόπουλου</column>
        </table>
        <table name="products_to_products_extra_fields">
            <column name="products_id">128</column>
 <column name="products_extra_fields_id_23">Δ. Κωστελένος</column>
        </table>
        <table name="products_to_products_extra_fields">
            <column name="products_id">833</column>
 <column name="products_extra_fields_id_23">Α. Δαμιανού</column>
        </table>
        <table name="products_to_products_extra_fields">
            <column name="products_id">1113</column>
 <column name="products_extra_fields_id_1">1993</column>
            <column name="products_id">1113</column>
 <column name="products_extra_fields_id_3">960-248-660-0</column>
            <column name="products_id">1113</column>
 <column name="products_extra_fields_id_6">Μαλακό εξώφυλλο</column>
            <column name="products_id">1113</column>
 <column name="products_extra_fields_id_7">14Χ21</column>
        </table>
        <table name="products_to_products_extra_fields">
            <column name="products_id">1114</column>
 <column name="products_extra_fields_id_1">2000</column>
            <column name="products_id">1114</column>
 <column name="products_extra_fields_id_3">978-960-385-034-2</column>
            <column name="products_id">1114</column>
 <column name="products_extra_fields_id_6">Μαλακό εξώφυλλο</column>
        </table>
        <table name="products_to_products_extra_fields">
            <column name="products_id">1115</column>
 <column name="products_extra_fields_id_1">2007</column>
            <column name="products_id">1115</column>
 <column name="products_extra_fields_id_6">Μαλακό εξώφυλλο</column>
            <column name="products_id">1115</column>
 <column name="products_extra_fields_id_7">14x21</column>
            <column name="products_id">1115</column>
 <column name="products_extra_fields_id_8">140</column>
            <column name="products_id">1115</column>
 <column name="products_extra_fields_id_21">Ιανουάριος 2007</column>
        </table>
        <table name="products_to_products_extra_fields">
            <column name="products_id">1116</column>
 <column name="products_extra_fields_id_1">2000</column>
        </table>
        <table name="products_to_products_extra_fields">
            <column name="products_id">16951</column>
 <column name="products_extra_fields_id_68">Έλληνες συγγραφείς-Ιστορικοί</column>
        </table>
    </database>
</pma_xml_export>

Voila !

Best Regards

guy038

P.S. : I’ve just sent you, by e-mail, your expected OUTPUT XML file !

[Nick Kolios]

@guy038
You’re a real savior.
Thank you so much for your help, i really appreciate it.
Thank you both