My Query as follows
select Faculty,STUFF(
(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(''))
,1,1,'')+')' as Schedule from Tb_Sch_Time_Table sch1
group by schdate,Faculty
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('')),1,6,'') 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-S3,B Tech 1-S4),Aug 28 (ETO-S3,ETO-S4)
But i want the correct output as follows
Aug 26 (B Tech 1-S3,B Tech 1-S4) Aug 28 (ETO-S3,ETO-S4)
for that getting a above output what changes i have to made in above query.
select Faculty,STUFF(
(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(''))
,1,1,'')+')' as Schedule from Tb_Sch_Time_Table sch1
group by schdate,Faculty
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('')),1,6,'') 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-S3,B Tech 1-S4)
But i want the correct output as follows
Aug 26 (B Tech 1-S3,B Tech 1-S4) Aug 28 (ETO-S3,ETO-S4)
for that getting a above output what changes i have to made in above query.
Raghav MehraPosted Aug 26, 2014, 8:28 AM
DECLARE @XML AS XML, @hDoc AS INT, @SQL NVARCHAR (MAX)
For Example You have an xml file as:
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.
Guest UserPosted Aug 25, 2014, 11:13 PM
select dbo.StripHTML('This is an html test')