question

Sample data in question provides an admittedly invalid, but working (after the **tr** tag is, finally, closed properly) HTML fragment defined as XML data type. The reason that HTML is invalid is due to the usage of the **TD** tags inside of the table header definition. Usually, the **TH** tags are used for table cells of the table header. However, the browsers are known to be very forgiving, and capable of absorbing developers' ignorance. This means that it is OK for someone to pace TD tags where TH tags are expected and still have HTML which the browsers can render correctly. With the sample data, which unfortunately provides the sample of only 2 table rows without clarifying what to do if there are more rows, the script to get the desired results is very simple. It is based on the idea that the number of table rows (2) and table cells in each row (3) is known in advance. Just selecting the td inner text will produce 6 rows, which need to be transposed into 3 rows, 2 columns. Here is the script which will work for the sample data: -- static solution, can be used if the number of rows in HTML table is 2. select [Column1], [Column2] from ( select x.value('.[1]', 'varchar(50)') CellValue, (row_number() over (order by (select null)) - 1) % 3 + 1 RowNumber, 'Column' + cast(ntile(2) over (order by (select null)) as varchar) ColumnName from @XML.nodes('//../td') r(x) ) src pivot (max(CellValue) for ColumnName in ([Column1], [Column2])) pvt Based on the sample data in question, the query above produces the following results: Column1 Column2 ---------- ---------- Name Chetan Date 07/22/2016 Status Current If the number of HTML table rows is greater than 2 then the script above will not work. I never received any clarification about what to do in this case, but will assume that whatever number of HTML table rows needs to be transposed to that number of columns in the results. For example, should the HTML table in question have 10 rows, the results should be 3 rows 10 columns, with column named like Column1 through Column10. The assumption is still that the number of columns in HTML table is 3. To make it work, the script needs to be dynamic (because hard coding 2 into ntile will no longer work). Please note that there is a need to declare and set 3 extra variables (the original xml variable is needed as well in addition to these 3 new ones). Of course it goes without saying that the dynamic script below will work with any number of HTML table rows (I mean it will work with the original sample data too). If the number of HTML table columns is not equal to 3 then the script will have to be subjected to minor touch up to accommodate this scenario. declare @rows int; -- number of rows in the HTML table declare @columns varchar(max); -- final list of transformed columns declare @sql nvarchar(max); -- dynamic script to execute; declare @XML xml; -- original variable select @XML = 'some html goes here'; -- dynamic script is needed if HTML table can have multiple, unknown in advance number of rows -- the solution assumes that the number of columns in HTML table is still 3, thus 3 is still hardcoded. -- figure out how many rows are in the HTML table select @rows = count(1) / 3 from @XML.nodes('//../td') r(x); -- prepare dynamic list of columns, which depends on the number of rows in HTML table select @columns = stuff(( select top (@rows) ', ' + quotename('Column' + cast(row_number() over (order by (select null)) as varchar)) from @XML.nodes('//../td') r(x) -- better to use a suitable tally table here for xml path('')), 1, 2, ''); select @sql = ' select ' + @columns + ' from ( select x.value(''.[1]'', ''varchar(50)'') CellValue, (row_number() over (order by (select null)) - 1) % 3 + 1 RowNumber, ''Column'' + cast(ntile(' + cast(@rows as varchar) + ') over (order by (select null)) as varchar) ColumnName from @InputXml.nodes(''//../td'') r(x) ) src pivot (max(CellValue) for ColumnName in (' + @columns + ')) pvt '; exec sp_executesql @statement = @sql, @params = N'@InputXml xml', @InputXml = @XML; go **Edit to add a script to render the HTML table data as is, without pivoting.** If the number of both columns and rows is dynamic then the script needs to be dynamic as well. Because the number of column in the HTML table is determined by the number of TD child tags of THEAD/TR, this part can be used to figure the column numbers and names and then this information can be used to generate and execute the final select. Here is the script, which includes the sample XML data consisting of 5 columns and 7 data rows (in addition to the header row which determines the columns' numbers and names): declare @xml xml; declare @sql nvarchar(max); -- dynamic script to execute select @XML = ' NameDateStatusOtherAnotherChetan07/22/2016Current1AMedivoxx08/22/2016Past2BName309/22/2016Current3CName410/22/2016Past4DName511/22/2016Current5EName612/22/2016Past6FName701/22/2017CurrentOther7G'; select @sql = ' select ' + stuff(( select ', ' + char(10) + ' x.value(''td' + quotename(row_number() over (order by (select null))) + ''', ''varchar(50)'')' + quotename(x.value('.[1]', 'varchar(50)')) from @XML.nodes('//thead/tr/td') r(x) for xml path('') ), 1, 3, '' ) + ' from @InputXml.nodes(''//tbody/tr'') r(x)' --print @sql; exec sp_executesql @statement = @sql, @params = N'@InputXml xml', @InputXml = @XML; go Based on the sample data in XML variable, the script above produces the following results (5 columns, 7 rows, as expected): Name Date Status Other Another ---------- ---------- ---------- ---------- ---------- Chetan 07/22/2016 Current 1 A Medivoxx 08/22/2016 Past 2 B Name3 09/22/2016 Current 3 C Name4 10/22/2016 Past 4 D Name5 11/22/2016 Current 5 E Name6 12/22/2016 Past 6 F Name7 01/22/2017 Current 7 G Hope this helps. Oleg

Hello guys, i have xml file that have value and value contain double qoat in that xml is given below please help me select data from that.

DECLARE @XMLstr AS NVARCHAR(MAX)
SELECT @XMLstr =
 
DECLARE @xml XML
SELECT @xml = CAST(REPLACE(@XMLstr, '''', '''''') AS XML)
SELECT @xml FOR XML PATH('')

actually i wants to store quote in table "2/2018 Just like we store height foot and inch 5'6 like that "2/2018 please help me.