Reputation: 221
I am writing tables in database for a restful service that exposes a resource called Orders.
Orders(id, items, user, total_amount)
I am using XML for the same.My question is how do I store "items" in my Orders table per OrderId and return the list in XML ? How do we handle multiple items here ?
What I mean is,if I want an XML response like this, how do I store the data in database?
<Order>
<Id>1</Id>
<Item>A</Item>
<Item>B</Item>
<Item>C</Item>
<User>Simran</User>
<Total_amount>1100</Total_amount>
</Order>
Database Tables I have so far:
create table Users
(
Id int not null primary Key identity(1,1) constraint fk_UserId_Id references Orders(UserId),
name nvarchar(50),
phone nvarchar(10)
);
create table Items
(
Id int not null primary Key identity(1,1) constraint fk_item_Id references Orders(ItemId),
name nvarchar(50),
Price int
);
create table Orders
(
id int not null primary Key identity(1,1),
ItemId int,
UserId int,
);
Upvotes: 0
Views: 66
Reputation: 93
This all depends on how you would like to store your data.
Denormalizing your data would probably create 3 tables from your example, A table for Orders, Users and Items.
In terms of producing XML from the DB query results you would need to extract the data into your XML Model. I would recommend separating this logic and producing a transformation tier, this way you are not messing with your data service layer when manipulating your objects.
Your multiple items in the XML would also have to be wrapped by an outer element, like so:
<Orders>
<Order>
<Id>1</Id>
<Item>A</Item>
<Item>B</Item>
<Item>C</Item>
<User>Simran</User>
<Total_amount>1100</Total_amount>
</Order>
<Order>
<Id>2</Id>
<Item>A</Item>
<Item>B</Item>
<Item>D</Item>
<User>Simran</User>
<Total_amount>1300</Total_amount>
</Order>
</Orders>
Upvotes: 2