I have same requirement as the above one please look into it and suggest me the solution.
I have a table with 4 columns. out of which first three columns are nvarchar and last column is XML type.
Table Structure is given below,
FName LName Gender ItemPriceTag
A1 B1 M (Item = "Soap" Price=23), (Item = "Ball" Price=31), (Item = "Book" Price=98)
A2 B2 F (Item = "Soap" Price=45), (Item = "Ball" Price=19), (Item = "Book" Price=45)
A3 B3 M (Item = "Soap" Price=12), (Item = "Ball" Price=11), (Item = "Book" Price=18)
A4 B4 F (Item = "Soap" Price=54), (Item = "Ball" Price=65), (Item = "Book" Price=98)
I am stroring my first 3 columns as nvarchar, I want to store last column as XML type, becuase it may contain the data upto 250 items.
My first question is what is the proper structure to store the data in XML format. i am using the below format, I feel that is wrong, please look into it and suggest me whether it is correct or wrong. If it is wrong, please suggest the proper way to store the data.
for XML column im storing data as, (Since i am concerned on last column, i am mentioning only last column)
<ItemPriceTag><Item= "Soap"><Price>23</Price></ItemPriceTag>
<ItemPriceTag><Item= "Ball"><Price>31</Price></ItemPriceTag>
<ItemPriceTag><Item= "Book"><Price></Price></ItemPriceTag>. this is for first row
<ItemPriceTag><Item= "Soap"><Price>45</Price></ItemPriceTag>
<ItemPriceTag><Item= "Ball"><Price>19</Price></ItemPriceTag>
<ItemPriceTag><Item= "Book"><Price>45</Price></ItemPriceTag> this is for 2nd row .......etc
Second question is, I want to display each item as a column name and price as its value.
For eg:
FName LName Gender Soap Ball Price
A1 B1 M 23 31 98
A2 B2 F 45 19 45
A3 B3 M 12 11 18
A4 B4 F 54 65 98
Please suggest me the table structure and solution for the above requirement.
Thanks in advance,
Nagaraj