คือผมต้องการดึงข้อมูลจาก Sql Server 2008 ซึ่งมีฟิวล์นึงเก็บเป็น XML ผมลองเขียนคิวรี่ก็ปรากฏว่าดึงออกมาใช้งานได้ ครับ
select a.AppId, a.Start, a.Subject
,Telephone=case when p.Telephone = '' then REPLACE(p.Cellular,'-','') else REPLACE(p.Telephone,'-','') end , b.VisitingPurpose.value('(/Resources/Resource/@Id)[1]', 'nvarchar(max)') as [VisitPurpose]
, vpl.VisitPurposeCode, vpl.VisitPurposeName, vpl.VisitPurposeGroupID, vpg.GroupName
, a.Description, a.AppTypeCodeId, a.WritterUserInfoId
, b.PatientAppId, b.PatientId, b.DepartmentId, d.DepartmentName, b.UserInfoId, c.UserName, b.AppNo
from Appointments a
Inner Join PatientApps b on a.AppId=b.AppId
Inner Join UserInfos c on b.UserInfoId = c.UserInfoID
Inner Join Patients p on p.PatientID = b.PatientId
Inner Join Departments d on d.DepartmentID = b.DepartmentId
Inner Join VisitPurposeLibs vpl on vpl.VisitPurposeLibID = b.VisitingPurpose.value('(/Resources/Resource/@Id)[1]', 'nvarchar(max)')
inner Join VisitPurposeGroups vpg on vpl.VisitPurposeGroupID = vpg.VisitPurposeGroupID
where YEAR(a.Start) = '2017' and MONTH(a.Start) = '09' and Day(a.Start) = '27'
and b.DepartmentID in ('25','27') and c.UserName like '%ศรี%'
แต่พอนำมาใส่ใน php แล้ว กลับไม่มีข้อมูลออกมา
$sql = " select a.AppId, a.Start, a.Subject
,Telephone=case when p.Telephone = '' then REPLACE(p.Cellular,'-','') else REPLACE(p.Telephone,'-','') end
, b.VisitingPurpose.value('$Resources', '$nvar') as VisitPurpose
, vpl.VisitPurposeCode, vpl.VisitPurposeName, vpl.VisitPurposeGroupID, vpg.GroupName
, a.Description, a.AppTypeCodeId, a.WritterUserInfoId
, b.PatientAppId, b.PatientId, b.DepartmentId, d.DepartmentName, b.UserInfoId, c.UserName, b.AppNo
from Appointments a
Inner Join PatientApps b on a.AppId=b.AppId
Inner Join UserInfos c on b.UserInfoId = c.UserInfoID
Inner Join Patients p on p.PatientID = b.PatientId
Inner Join Departments d on d.DepartmentID = b.DepartmentId
Inner Join VisitPurposeLibs vpl on vpl.VisitPurposeLibID = b.VisitingPurpose.value('$Resources', '$nvar')
inner Join VisitPurposeGroups vpg on vpl.VisitPurposeGroupID = vpg.VisitPurposeGroupID
where YEAR(a.Start) = '2017' and MONTH(a.Start) = '09' and Day(a.Start) = '27'
and b.DepartmentID in ('25','27') and c.UserName like '%ศรี%' ";
Load balance : Server 02
