-1

How i can import a custom XML file into a MySQL?

My XML :

    <ObjectList>
    <Section Index="0" Name="MichaelCat">
        <Item Index="0" Slot="0" FirstNmae="Example1" LastName="Example2" Age="30"  />
        <Item Index="1" Slot="0" FirstNmae="Example1.2" LastName="Example2.1" Age="30"  />
    </Section>

    <Section Index="1" Name="MichaelCat2">
        <Item Index="0" Slot="0" FirstNmae="Example1" LastName="Example2" Age="30"  />
        <Item Index="1" Slot="0" FirstNmae="Example1.2" LastName="Example2.1" Age="30"  />
    </Section>
</ObjectList>

My DB Schema:

SectionName | Section | Index | Slot | FIrstName | LastName | Age

In SectionName need to be inserted Section->Name

In Section need to be inserted Section->Index

In Index need to be inserted Section->Item->Index

In Slot need to be inserted Section->Item->Slot

etc..

In MSSQL i use:

DECLARE @input XML = 'MY XML file'

select
  Name = XCol.value('../@Index','varchar(25)'),
  Cat = XCol.value('../@Name','varchar(25)'),
  [Index] = XCol.value('@Index','varchar(25)'),
  Slot = XCol.value('@Slot','varchar(25)')
from
  @input.nodes('/ItemList/Section/Item') AS test(XCol)

Thank you very much

MeTa
  • 89
  • 1
  • 3
  • 11
  • When I Google `import custom XML to mySQL` I get a lot of results, is none of them useful? Can you elaborate why? – Pekka Oct 11 '16 at 12:48
  • e.g. http://stackoverflow.com/questions/5491056/how-to-import-xml-file-into-mysql-database-table-using-xml-load-function – Pekka Oct 11 '16 at 12:48
  • in my mssql i use `DECLARE @input XML = 'MY XML file' select Name = XCol.value('../@Index','varchar(25)'), Cat = XCol.value('../@Name','varchar(25)'), [Index] = XCol.value('@Index','varchar(25)'), Slot = XCol.value('@Slot','varchar(25)') from @input.nodes('/ItemList/Section/Item') AS test(XCol)` – MeTa Oct 11 '16 at 13:53

1 Answers1

0

Look here. Seems pretty straightforward. In your case, maybe something like:

LOAD XML LOCAL INFILE 'some_file.xml'
INTO TABLE person
ROWS IDENTIFIED BY '<section>';
Just Rudy
  • 700
  • 11
  • 28