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?
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
OP_Zaharin
- 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.
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
ASKER
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.
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.