Xml file, remove table tags according the column id?

[Alan Kilborn]

Since OP didn’t use proper code blocks for his sample data, I’d guess that the real data doesn’t contain “smart quotes”, but rather normal quotes ("), and thus, that type should be used in @guy038 's proposed solution.

Thus, instead of:

…With this first regex S/R, we’ll delete all the complete lines <table name=“products_to_products_extra_fields”> and </table>

it should be:

…With this first regex S/R, we’ll delete all the complete lines <table name="products_to_products_extra_fields"> and </table>

But…I could be wrong. No way to tell.

[Nick Kolios]

@Alan-Kilborn @guy038 1st of all thank you for your fast replies, but no luck!
I copied the text (“products_to_products_extra_fields”) to the search box from my xml file, just to not mess with the quotes.

I also tried to check and test it with the wrap around, matches new line. Not sure if i am missing something else.
@guy038 Yes you’re right, text is sorted by increasing ID_Number

notepa.jpg

[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