sqlserver的table转换xml问题

cxs_001 2018-10-12 05:39:11

我语句如下：
--转换xml
DECLARE @ParameterSQL NVARCHAR(MAX)='SELECT A.Account,''null''),isnull(B.Id,''null''),isnullB.EmployeCode,B.Name,B.EmployeType,BirthDate,NativePlace,B.Education,Major,Tel,Email,Address,OrganizeId,DepartmentId,DutyId,PostId,WorkingCondition,Background,CheckinTime,B.EmployeStatus,Picture,Sex,Volk,IDCard,A.Age,DateOfStartWork,DateWorkForceXincai,DateWorkForJiangong,PoliticalStatus,DateToPolity,RegisteredPlace,HouseholdType,B.IsArtisan,Comment,SalaryProationPeriod,BasicSalary,SocialSecurityNumber,SocialSecurityRadices,ProvidentFundAccount,ProvidentFundRadices,IsMarried,BloodType,ForeignLanguage,ForeignLanguageLevel,ComputerLevel,SpecialGroup,MilitaryInfo,ContractID,InternalTitle,OutTitle,IsAgreeToContract,AppraisalStatus,LastDateOfAppraisal,RecruitPrimaryId,WorkReportStatus,RelieveTime,RetireFlag,DoubleDegree,SalaryPosition,B.InPostId,B.Nature,B.PostionNature,B.LivingExpenses,DutyAllowance,B.SkillAllowance,B.OutstandingAllowance,B.Rank,B.Post2,B.Post3,DutyTime,PostTime1,PostTime2,PostTime3,InPostTime,CreateTime,ModifyTime FROM WagesTabulationStaff A LEFT JOIN Staff B ON A.EmployeeID=B.ID WHERE B.ID=''7a96cad9-4264-43cb-99c1-0e39ee33bc9c'' ';
DECLARE @SQL NVARCHAR(MAX)
DECLARE @XMLString VARCHAR(MAX)
DECLARE @XML XML
DECLARE @Paramlist NVARCHAR(1000)
SET @Paramlist = N'@XML XML OUTPUT'
SET @SQL = 'WITH PrepareTable (XMLString)'
SET @SQL = @SQL + 'AS( '
SET @SQL = @SQL + @ParameterSQL+ ' FOR XML RAW,TYPE,ELEMENTS'
SET @SQL = @SQL + ')'
SET @SQL = @SQL + 'SELECT @XML=[XMLString]FROM[PrepareTable]'
EXEC sp_executesql @SQL, @Paramlist, @XML=@XML OUTPUT
SET @XMLString=CAST(@XML AS VARCHAR(MAX))
SELECT @XML;
--转换json
declare @text varchar(max)
exec [SerializeJSON] @XMLString,@text out
select @text

问题：在table转换xml的时候，当值为空的时候，xml里面就没得那个项目。
请问怎么做？
我之前用了isnull的方法，但是由于别人设计的表不怎么好，isnulll无法解决这个问题。
在线等，急。

...全文