0

I have some xml files (example below). As you can see in the xml, id 4 has 3 entries for tax. When I load it right now, only the last entry gets inserted into the table.

  1. How can I include all 3 of them when I load it into mysql?
  2. What's the best approach to deal with sub attributes of data in xmls?

Table:

mysql> create table if not exists trial (
    -> id NUMERIC PRIMARY KEY,
    -> tax TEXT,
    -> shipping VARCHAR(4),
    -> cost NUMERIC
    -> );
Query OK, 0 rows affected (0.01 sec)

mysql> LOAD XML INFILE '/var/lib/mysql-files/trial.xml' INTO TABLE trial ROWS IDENTIFIED BY '<entry>';
Query OK, 4 rows affected (0.00 sec)
Records: 4  Deleted: 0  Skipped: 0  Warnings: 0

mysql> select * from trial;
+----+------+----------+------+
| id | tax  | shipping | cost |
+----+------+----------+------+
|  1 | NULL | YES      |   10 |
|  2 | NULL | NO       |   20 |
|  3 | NULL | YES      |   30 |
|  4 | NULL | YES      |   90 |
+----+------+----------+------+
4 rows in set (0.00 sec)

XML data:

<?xml version="1.0" encoding="utf-8" ?>
<feed>
    <entry>
        <id>1</id>
        <tax>
            <shipping>YES</shipping>
            <cost>10.0</cost>
        </tax>
    </entry>
    <entry>
        <id>2</id>
        <tax>
            <shipping>NO</shipping>
            <cost>20.0</cost>
        </tax>
    </entry>
    <entry>
        <id>3</id>
        <tax>
            <shipping>YES</shipping>
            <cost>30.0</cost>
        </tax>
    </entry>
    <entry>
        <id>4</id>
        <tax>
            <shipping>YES</shipping>
            <cost>70.0</cost>
        </tax>
        <tax>
            <shipping>YES</shipping>
            <cost>80.0</cost>
        </tax>
        <tax>
            <shipping>YES</shipping>
            <cost>90.0</cost>
        </tax>
    </entry>
</feed>
11
  • If this question is more suitable in stackoverflow than here, please let me know. Mar 29, 2021 at 18:21
  • You try to treate your XML as multi-level one whereas MySQL can process XML as single-level only.
    – Akina
    Mar 29, 2021 at 18:34
  • @Akina right, is there a way around this? I'm not in control of creating the xml so can't do much there. One way I can think of is to process the file, add attributes or something like that, and rewrite the file before consuming into sql. Other way is maybe to associate it with another table? Not sure thinking out loud here. Please let me know. Mar 29, 2021 at 18:35
  • If you can to process your XML then convert it to something like <entry> <tax> <id>1</id> <shipping>YES</shipping> <cost>10.0</cost> </tax> </entry> ... <entry> <tax> <id>4</id> <shipping>YES</shipping> <cost>70.0</cost> </tax> </entry> <entry> <tax> <id>4</id> <shipping>YES</shipping> <cost>80.0</cost> </tax> </entry> ... - i.e. move id attribute into each tax.
    – Akina
    Mar 29, 2021 at 18:39
  • Alternatively - load the whole XML and parse it in iterative stored procedure.
    – Akina
    Mar 29, 2021 at 18:41

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Browse other questions tagged or ask your own question.