Storing and Retrieving XML data in SQL?

Question

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) 23 31 . this is for first row 45 ‌ 19 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‌

Nagarajaswamy · Answer

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 FNameLName 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 FNameLName ' + @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)

Nitin Sontakke · Answer

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.

Bikesh Srivastava · Answer

Take data in dataset and use as well as

Nagarajaswamy · Answer

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

Nagarajaswamy · Answer

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.

Nitin Sontakke · Answer

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.