Storing and Retrieving XML data in SQL?

Nagarajaswamy

9y950

Hello Everyone,

I‌ have same requirement as the above one pl‌ease 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‌.

T‌able 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.

M‌y 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>23
<‌ItemPriceTag>31
<‌ItemPriceTag>. this is for first row

<‌ItemPriceTag>45‌
<‌ItemPriceTag>19
<‌ItemPriceTag>45 this is for 2nd row ...‌‌‌....etc

‌‌‌‌
S‌econd question is, I want to display each item as a column name and price as its value.
F‌or 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.

T‌hanks in advance,
N‌agaraj‌

6 Replies

Know the answer? Post it — somebody with the same question will find it here.

Bikesh Srivastava9y ago
Take data in dataset and use as well as ,
0
Nagarajaswamy9y ago
Please find the alternative approach for the same requirement.

--Alternative option with increased performance than the provious option
If object_ID(N'tempdb.dbo.#tmp') is not NULL
Drop table #tmp;

SELECT FName,LName,
t.c.value('@name', 'nvarchar(50)') AS [name]
, t.c.value('@value', 'nvarchar(50)') AS [value]
INTO #tmp
FROM XMLTypes
CROSS APPLY Product.nodes('/Product/param') t(c)

--SELECT * FROM #tmp
Declare @sql1 varchar(max);
set @sql1 = STUFF((SELECT ',' + QUOTENAME([name])
FROM #tmp
GROUP BY [name]
ORDER BY [name]
FOR XML PATH(''))
, 1, 1, '');

set @sql1 = 'SELECT FName,LName, ' + @sql1 + '
FROM #tmp t
PIVOT (
MAX([value])
FOR [name] IN (' + @sql1 + ')
) p';

-- print @sql
exec(@sql1);

(Thanks you Sam Zha for helping me to solve this query)
0
Nagarajaswamy9y ago
Hi all,

I have found an option for the above requirement, I have done it using a sample table.

Please find the Table and Queries below:

create table XMLTypes
(
FName nvarchar(10),
LName nvarchar(10),
Product xml
);

delete from XMLTypes

select * from XMLTypes

INSERT INTO XMLTypes3 VALUES('110','234','');

INSERT INTO XMLTypes VALUES('111','124','') ;

select * from XMLTypes
-------------------------------------------------------------
Declare @template As nvarchar(max)

set @template = 'select Fname, LName, {@col_list} from XMLTypes'

declare @col_template as nvarchar(max)

set @col_template = 'Product.value(''/Product[1]/param[@name="{name}"][1]/@value'',''nvarchar(max)'') as {Name}'

declare @col_list as nvarchar(max)

;with names As (select distinct name from XMLTypes cross apply (select x.name.value('@name','nvarchar(max)') from product.nodes('Product[1]/param') as x(name) ) as y(name))

select @col_list = coalesce(@col_list + ',','') + Replace(@col_template, '{name}', name) from names

declare @sql as nvarchar(max) set @sql = replace(@template, '{@col_list}', @col_list)

exec(@sql)

Thanks,
Nagaraj
0
Nagarajaswamy9y ago
Hi all,

There is a small correction in the expected output,

last column should be "Book", not "Price"

here it is,
FName ‌ LName Gender Soap Ball Book
A1 B1 M 23 31 98
A2 B2 F 45 19 45
A3 B3 M 12 11 18
A4 B4 F 54 65 98

Thanks.
0
Nitin Sontakke9y ago
It is my strictly personal opinion that you DO NOT store data in such a mixed way, that is, relational + non-relational.

If there is a middle layer involved, I strongly suggest that you normalize this structure and put additional set of data in a separate table, for example:

Id - pk, identity column for the new table
PersonId - does your table has a PK, if yes, this will be that column. If not, please create one.
ItemId - If possible create separate table for "Soap", "Book", etc. and keep it's id here.
Price.

You can then left outer join to this table and get the data set.

You can then pivot it (with dynamic sql, sorry!) and get the result you want.

Wait for other suggestions as well.
+1
Nitin Sontakke9y ago
In this reply, I am not answering your question. But I wanted to mention (for others visiting this question) that this is the proper way of asking question.

Many people fail to even ask question properly.
0