Replace database contents with xml file

[glendango]

Hi,,HAs anyone ever worked on .xml files with many elements in them. 

 

  iam working on an xml project which i will eventually receive a feed from the xml provider ,  but having same issue understanding the process. 

I have a .xml practice file/doc  and its for property on the market..  example below of content ( this is a practice file the feed provider releases to 'help' with set up..)

<?xml version="1.0"?>

-<agency name="Baxters Group" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">

-<branches>

-<branch name=" Baxters Bath">

-<properties>

-<property reference="BB00000285">

<availableFrom/>

<department>Agricultural</department>

<propertyofweek>NO</propertyofweek>

<price_text>OIRO £749,950</price_text>

<numeric_price>749950.0000</numeric_price>

<bedrooms>4</bedrooms>

<receptions>2</receptions>

<bathrooms>0</bathrooms>

 

-----------------------

 

 

 

There are literally  a 100 more elements to make up a complete a property... 

DO i actually have to manually create a field name for every element? 

This will take hours and i dont want to do this if the theory is incorrect . 

ALSO a house may have 10 photos or it may have 30 photos...how do i set up the schema to accomodate (pardon pun) all these different elements..

 

i would ask but nothing online goes back to basics..everything assumes the db schema knowledge is normally about 4 elements...  i wish. 

i could just use a plug-in on wordpress but i want to learn this for more unsupported apps i might build. 

thanks

 

 

 

[requinix]

Don't hijack threads.

Have you seen LOAD XML?

[glendango]

 

 

i read this yes...it tells me how to use it but doesnt tell me what is does...

what does loadxml do?      does it mean it creates the table column names automatically?

 

Edited July 18, 2018 by glendango

[Barand]

51 minutes ago, glendango said:

ALSO a house may have 10 photos or it may have 30 photos...how do i set up the schema to accomodate (pardon pun) all these different elements..

It's a table with two fields (house_id/image_name). It's a matter of a house having 10 of those records or 30.

[glendango]

that makes sense... so you might have many tables for an xml file with this many elements..i can feel a plugin coming on?

in this case a table would be

branches, properties, rooms, pictures, floorplans..

 

And do you have to name all the fields in the table by hand or can you use a 'flat file' ??  

i find all the buzz words i just cant piece them together. 

Edited July 18, 2018 by glendango

[Barand]

Were those photos 30 out of the 100+ elements in the file? There is a good chance some the remaining 70 should also be in separate tables.

Check out "data normalization"

[glendango]

.   iam sure what you said is correct.... all the other elements will only have 1 entry..

example  : 

table 1 Properties 

bullet1

bullet2

bullet3.....   

bullet 10

they are predefined values put in by the user of the crm...

 

Thanks..

DO i still have to manually put the fields in the table in by hand though?   ii could have done it by now tbf but i wanted to see if more speedier way to set up the db..

 

Edited July 18, 2018 by glendango

[Barand]

As far as I know, the only ways to create a table and have the columns created for you are

• CREATE TABLE tableB LIKE tableA;
• CREATE tableB SELECT x,y,x FROM tableA;