i worked lot but i coluld not the correct output

Question

My Query as follows select FacultySTUFF( (select ''+ ltrim(rtrim([Course])) +'-S'+ltrim(rtrim([Session])) from Tb_Sch_Time_Table sch where sch.Schdate = sch1.Schdate and sch.Faculty = sch1.Faculty FOR XML PATH('')) 11'')+')' as Schedule from Tb_Sch_Time_Table sch1 group by schdateFaculty I put the above query in view as follows select distinct faculty stuff((select '' + schedule as Msg from Vw_Presea_Sch_Send_SMS vw1 where vw1.faculty = vw2.faculty FOR XML PATH(''))16'') as msg from Vw_Presea_Sch_Send_SMS vw2 Views name is Vw_Presea_Sch_Send_SMS When i execute the above query output as follows Aug 26 (B Tech 1-S3B Tech 1-S4) Aug 28 (ETO-S3ETO-S4) But i want the correct output as follows Aug 26 (B Tech 1-S3B Tech 1-S4) Aug 28 (ETO-S3ETO-S4) for that getting a above output what changes i have to made in above query.

Raghav Mehra · Answer

You can separately import the xml nodes in another table. With whatever node values you wanted from the XML data file. And then can use that table instead of using the XML file directly. DECLARE @XML AS XML @hDoc AS INT @SQL NVARCHAR (MAX) For Example You have an xml file as: Address line 1 2 3 You can Import the data from nodes with below query SELECT @XML = XMLData FROM XMLwithOpenXML EXEC sp_xml_preparedocument @hDoc OUTPUT @XML SELECT CustomerID CustomerName Address FROM OPENXML (@hDoc 'ROOT/Customers/Customer') WITH ( CustomerID [varchar](50) '@CustomerID'  CustomerName [varchar](100) '@CustomerName'  Address [varchar](100) 'Address' ) EXEC sp_xml_removedocument @hDoc GO And so finally you can use the data directly from xml file into the column format of table. Hope this will help.

Sumit Jolly · Answer

i suggest way where you can strip any HTML or XML tags. So write StripHTML function like this and you can use alongwith like this select dbo.StripHTML('This is an html test') create function dbo.StripHTML(@text varchar(max)) returns varchar(max) as begin declare @textXML xml declare @result varchar(max) set @textXML = @text; with doc(contents) as ( select chunks.chunk.query('.') from @textXML.nodes('/') as chunks(chunk) ) select @result = contents.value('.' 'varchar(max)') from doc return @resultend go