Link to home
Create AccountLog in
Avatar of jecommera
jecommeraFlag for United Kingdom of Great Britain and Northern Ireland

asked on

how to manage XML feed into table using SQL

Hi,

We have an ecommerce and email system. We can get a feed from our ecommerce system into our email system using XML which is like

<email>
justin@email.com
</email>
<purchase>
<item>
<name>
product
</name>
<price>
2.00
</price>
</item>
<purchase>

Obviously, customer can purchase 1 - infinity products.

I am thinking we can bring into a table like:
| email | product | price|
justin@email.com | item 1 | 2.00 |
justin@email.com | item 2 | 2.00 |
justin@email.com | item 3 | 2.00 |
joe@email.com | item 1 | 2.00 |

Is this the best way to manage this or can anyone suggest a better table?
ASKER CERTIFIED SOLUTION
Avatar of skarai
skarai
Flag of United States of America image

Link to home
membership
Create a free account to see this answer
Signing up is free and takes 30 seconds. No credit card required.
See answer
SOLUTION
Link to home
membership
Create a free account to see this answer
Signing up is free and takes 30 seconds. No credit card required.
- i should add that the item table should also have quantity-in-hand, reorder quantity and etc should you want to monitor the item stock details.
Avatar of jecommera

ASKER

@OP_Zaharin where what happens in transaction_table if the customer orders more than one item? (There is only one column which is itemID
Just to let you know there is more data in the XML file. I am just wondering is this the best way to manage the data at a minimum.

If the email addresses are cut up into rows then this would obviously become quite large as per my example, this results in three new rows for justin email since the justin purchases 3 items.
SOLUTION
Link to home
membership
Create a free account to see this answer
Signing up is free and takes 30 seconds. No credit card required.
SOLUTION
Avatar of slightwv (䄆 Netminder)
slightwv (䄆 Netminder)

Link to home
membership
Create a free account to see this answer
Signing up is free and takes 30 seconds. No credit card required.