sql - sql语言 - 将SELECT *查询转换为string

我有一个返回行的查询


SELECT *


FROM table


WHERE id = 1;



我想将结果保存到nvarchar sql变量中,


select *


from table


where id = 1


for xml path ('')



有没有方法实现这个?

时间:

可以轻松地将结果存储为XML字符串:


select *


from (values (1, 'x', getdate())) v(id, a, b)


where id = 1


for xml path ('');



或者作为JSON字符串:


select *


from (values (1, 'x', getdate())) v(id, a, b)


where id = 1


for json auto;



例子


Declare @YourTable Table (id int,[col_1] varchar(50),[col_2] varchar(50),[col_3] varchar(50),[col_n] varchar(50)) Insert Into @YourTable Values 


 (1,'data1','data2','data3','data4')


,(2,'data5','data6','data7','data8')



-- Entire Table 


Declare @XML xml = (Select *,_RN=Row_Number() over (Order By (Select null)) From @YourTable for XML RAW,ELEMENTS XSINIL )


Select [dbo].[svf-str-Data-To-Delimited]('|',char(13)+char(10),@XML)



Returns


1|data1|data2|data3|data4


2|data5|data6|data7|data8



或基于行


Select A.ID


 ,AsAString = [dbo].[svf-str-Data-To-Delimited]('|',char(13)+char(10),B.XMLData)


 From @YourTable A


 Cross Apply ( values ( convert(xml,(select a.* for xml RAW,ELEMENTS XSINIL ))) )B(XMLData)



Returns


ID AsAString


1 1|data1|data2|data3|data4


2 2|data5|data6|data7|data8



如果感兴趣,用函数


ALTER Function [dbo].[svf-str-Data-To-Delimited] (@Delim varchar(50),@EOL varchar(50),@XML xml)


Returns varchar(max)


Begin



Return(


 Select convert(varchar(max),(


 Select case when Item='_RN' then ''


 else case when nullif(lead(Item,1) over (Order by Seq),'_RN') is not null 


 then concat(Value,@Delim)


 else concat(Value,@EOL)


 end 


 end


 From (


 Select Seq = row_number() over(order by (select null))


 ,Item = xAttr.value('local-name(.)', 'nvarchar(100)')


 ,Value = xAttr.value('.','nvarchar(max)')


 From @XML.nodes('/row/*') xNode(xAttr)


 ) A


 Order By Seq


 For XML Path (''),TYPE).value('.', 'nvarchar(max)') )


)



End



...