查询表中列转换为json

DECLARE @sql VARCHAR(MAX)
SET @sql=
(SELECT (select ‘+‘‘,"‘+column_name+‘":"‘‘+CAST(‘+column_name+‘ AS VARCHAR(8000))+‘‘"‘‘‘ from information_schema.columns where TABLE_NAME = ‘caADDRESS‘ for xml path( ‘‘)))
+
‘ from caADDRESS‘
SELECT @sql=‘select TOP 1 ‘‘‘+STUFF(@sql,1,3,‘‘)
SELECT @sql


select TOP 1 ‘"ID":"‘+CAST(ID AS VARCHAR(8000))+‘"‘+‘,"APPLICATIONNO":"‘+CAST(APPLICATIONNO AS VARCHAR(8000))+‘"‘+‘,"AREA":"‘+CAST(AREA AS VARCHAR(8000))+‘"‘+‘,"COUNTY":"‘+CAST(COUNTY AS VARCHAR(8000))+‘"‘+‘,"STREET":"‘+CAST(STREET AS VARCHAR(8000))+‘"‘+‘,"ADDRESS":"‘+CAST(ADDRESS AS VARCHAR(8000))+‘"‘+‘,"PROPERTYTYPE":"‘+CAST(PROPERTYTYPE AS VARCHAR(8000))+‘"‘+‘,"ISDEFAULT":"‘+CAST(ISDEFAULT AS VARCHAR(8000))+‘"‘+‘,"ADDRESSTYPE":"‘+CAST(ADDRESSTYPE AS VARCHAR(8000))+‘"‘+‘,"CAPARTYID":"‘+CAST(CAPARTYID AS VARCHAR(8000))+‘"‘+‘,"DEFAULTADD":"‘+CAST(DEFAULTADD AS VARCHAR(8000))+‘"‘+‘,"POSTNO":"‘+CAST(POSTNO AS VARCHAR(8000))+‘"‘+‘,"CDATE":"‘+CAST(CDATE AS VARCHAR(8000))+‘"‘+‘,"SysStartTime":"‘+CAST(SysStartTime AS VARCHAR(8000))+‘"‘+‘,"SysEndTime":"‘+CAST(SysEndTime AS VARCHAR(8000))+‘"‘ from caADDRESS


"ID":"000000004415A0000UC5","APPLICATIONNO":"000008B0CCEE80000U43","AREA":"430100","COUNTY":"","STREET":"","ADDRESS":"岳麓区郡原广场4栋3单元2107","PROPERTYTYPE":"00003","ISDEFAULT":"0","ADDRESSTYPE":"00003","CAPARTYID":"0000B8ADB39000000U52","DEFAULTADD":"1","POSTNO":"410000","CDATE":"06  1 2010 12:00AM","SysStartTime":"2018-08-14 10:07:27.3596352","SysEndTime":"9999-12-31 23:59:59.9999999"