失败时 SQL 将结果写入 XML

Question

 SELECT   
    (SELECT v_value as "@value", columnname as "@columnname", 
            updatedby as "@updatedby", emailid as "@emailid",
            updatedon as "@updatedon"              
     from nodetable, Progresss PGA, mastertable 
     where columnname ='SSNNO' 
           and PGA.ProjId=9 
           AND OBSID IN (SELECT NodeId FROM projecttable 
                         WHERE ParentId=mastertable.roles AND Title='some value') 
           AND UserId=1 
           AND mastertable.FileID=nodetable.fileid 
           AND mastertable.FileID=821  
     ORDER BY updatedon desc  FOR XML PATH('Forms'), TYPE)
 FOR XML PATH('Form'), TYPE

这是我获取 XML 的查询..别担心它的工作正常..

但是我的问题如果条件失败我想再打印一个标签，它总是告诉查询中发生了什么..

对于这个查询，如果所有条件都成功，我将获得完整的 XML..

否则我会得到

 <Form />

但我想在失败的情况下打印结果..

<Form SUCCESS="0"/>

Answer 1

DECLARE @XML XML =
    (SELECT v_value as "@value", columnname as "@columnname", 
            updatedby as "@updatedby", emailid as "@emailid",
            updatedon as "@updatedon"              
     from nodetable, Progresss PGA, mastertable 
     where columnname ='SSNNO' 
           and PGA.ProjId=9 
           AND OBSID IN (SELECT NodeId FROM projecttable 
                         WHERE ParentId=mastertable.roles AND Title='some value') 
           AND UserId=1 
           AND mastertable.FileID=nodetable.fileid 
           AND mastertable.FileID=821  
     ORDER BY updatedon desc  FOR XML PATH('Forms'))

IF @XML IS NULL
  SELECT CAST('<Form SUCCESS="0"/>' AS XML)
ELSE
  SELECT @XML FOR XML PATH('Form')

作为一个声明

SELECT CASE WHEN T.X IS NULL THEN 0 END AS [@SUCCESS],
       T.X AS [*]
FROM 
    (SELECT v_value as "@value", columnname as "@columnname", 
            updatedby as "@updatedby", emailid as "@emailid",
            updatedon as "@updatedon"              
     from nodetable, Progresss PGA, mastertable 
     where columnname ='SSNNO' 
           and PGA.ProjId=9 
           AND OBSID IN (SELECT NodeId FROM projecttable 
                         WHERE ParentId=mastertable.roles AND Title='some value') 
           AND UserId=1 
           AND mastertable.FileID=nodetable.fileid 
           AND mastertable.FileID=821  
     ORDER BY updatedon desc  FOR XML PATH('Forms'), TYPE) AS T(X)
FOR XML PATH('Form'), TYPE