利用SQL生成xml示例

/*
利用SQL生成xml示例
*/


/*显示空的字段*/
SELECT TOP 1 PatName,PAT.PatID,PID.ImageNo,PAT.RegPatID
FROM Patient AS PAT INNER JOIN(SELECT PATID,PatNumber AS ImageNo FROM  PatientIdentify WHERE NTypeCode='0003') AS PID ON PAT.PatID=PID.PatID
WHERE PatOtherName LIKE 'YIN%' FOR XML RAW('Patient'),ELEMENTS XSINIL,ROOT('ROWDATA')




/*不显示空的字段*/
SELECT TOP 1 PatName,PAT.PatID,PID.ImageNo,PAT.RegPatID
FROM Patient AS PAT INNER JOIN(SELECT PATID,PatNumber AS ImageNo FROM  PatientIdentify WHERE NTypeCode='0003') AS PID ON PAT.PatID=PID.PatID
WHERE PatOtherName LIKE 'YIN%' FOR XML RAW('Patient'),ELEMENTS ,ROOT('ROWDATA')




/*lqy 1.FOR XML RAW('Patient')*/
select top 1
 PAT.PatID, PAT.PatName, PAT.PatOtherName, PAT.Birthday, PAT.IdentityNumber, PAT.SexCode,
 pids.PatNumber,od.DepartmentCode,vst.OutpatientCode,vst.WardCode,vst.BedNumber,vst.InpatientCode,EXM.RegistrationDateTime, 
 RP.ReportID, RP.ImageFinding, RP.Impression,rp.EquipeType,rp.BiopsySiteCode,rp.Proposal,rp.HP,rp.ReportPhysicianCode,
  RP.ReportBarcode, RP.ReportPhysicianCode, RP.ReportDateTime, 
 RP.VerifyDoctorCode1, RP.VerifyDoctorCode2, RP.VerifyDateTime1, RP.VerifyDateTime2,RP.CurrentStatus
  from ReportContent as RP
INNER JOIN Examination AS EXM ON RP.ReportID=EXM.ReportID
INNER JOIN [Order] AS OD ON EXM.OrderID=OD.OrderID
INNER JOIN Visit AS VST ON OD.VisitID=VST.VisitID
INNER JOIN (SELECT PatID,PatNumber FROM PatientIdentify WHERE NTypeCode='0003') AS PIDS ON VST.PatID=PIDS.PatID
INNER JOIN Patient AS PAT ON VST.PatID=PAT.PatID
WHERE 1=1 and CurrentStatus>=140 FOR XML RAW('Patient'),ELEMENTS XSINIL,ROOT('SIMED_REPORT')


select top 1  dbo.GetDictionary('Sex','0001','DisplayText') as Sex from patient
--------------------2.FOR XML auto------------ 
select top 100
 PAT.PatID, PAT.PatName, PAT.PatOtherName,datediff(YY,PAT.Birthday,od.OrderDateTime)AS PatAge, PAT.IdentityNumber,
 dbo.GetDictionary('Sex',pat.SexCode,'DisplayText') as Sex,
 dbo.GetDictionary('DepartmentInfo',od.DepartmentCode,'DisplayText') as DepartmentName,
 pids.PatNumber, dbo.GetDictionary('Ward',vst.WardCode,'DisplayText') as OutpatientName,
 vst.BedNumber,vst.InpatientCode,EXM.RegistrationDateTime, 
 RP.ReportID, RP.ImageFinding, RP.Impression,
rp.BiopsySiteCode,pids.PatNumber, rp.EquipeType,
 rp.Proposal,rp.HP,rp.ReportPhysicianCode,
 dbo.GetDictionary('Staff',rp.ReportPhysicianCode,'DisplayText') as ReportPhysicianName,
  RP.ReportBarcode, RP.ReportPhysicianCode, RP.ReportDateTime, 
 RP.VerifyDoctorCode1, RP.VerifyDoctorCode2, RP.VerifyDateTime1, RP.VerifyDateTime2,RP.CurrentStatus
  from ReportContent as RP
INNER JOIN Examination AS EXM ON RP.ReportID=EXM.ReportID
INNER JOIN [Order] AS OD ON EXM.OrderID=OD.OrderID
INNER JOIN Visit AS VST ON OD.VisitID=VST.VisitID
INNER JOIN (SELECT PatID,PatNumber FROM PatientIdentify WHERE NTypeCode='0003') AS PIDS ON VST.PatID=PIDS.PatID
INNER JOIN Patient AS PAT ON VST.PatID=PAT.PatID
WHERE 1=1 and  rp.BiopsySiteCode<>'' and CurrentStatus>=140 FOR XML auto,ELEMENTS XSINIL,ROOT('SIMED_REPORT')


---------------------
select * from p_Dicts where Name like'%沈%'
select * from p_DictValues where ScopeId='ReportPhrase'
-------------------------