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?

 

[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. 

[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..

 

[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;