在SQL Server中将选择查询结果转换为XML格式[重复]

This question already has an answer here:

这个问题在这里已有答案:

• How to make sql query result to xml file 5 answers

如何使sql查询结果到xml文件5个答案

I am trying to convert a result to XML format using a stored procedure my code is as below

我试图使用存储过程将结果转换为XML格式,我的代码如下所示

SELECT 
    K.KeyWord as Result 
FROM 
    SearchKeyword K 
WHERE 
    K.KeyWord LIKE @SearchWord + '%'   
FOR XML PATH 'Root', BINARY BASE64)

But that return as below

但是返回如下

<Root>
   <Result>1</result>
<Root>

<Root>
   <Result>2</result>
<Root>

But I need result like below remove one root element

但我需要像下面的结果删除一个根元素

<Root>
    <Result>1</Result>
    <Result>2</Result>
    .
    .
    .
</Root>

Anyone please help

有人请帮忙

2 个解决方案

#1

select K.KeyWord as Result 
from ... as k
where ...
for xml path(''), root('Root'), binary base64

sql fiddle demo

sql小提琴演示

#2

DECLARE @tab TABLE ([A]NVARCHAR(MAX))

INSERT INTO @tab values ('test')
INSERT INTO @tab values ('test')

SELECT [A] + '' FROM @tab
FOR XML PATH

Add a concat to allow for what you want.

添加concat以允许您想要的内容。

Your Code :

你的代码:

SELECT K.KeyWord + '' as Result 
FROM SearchKeyword K 
WHERE K.KeyWord LIKE @SearchWord+'%'   
FOR XML PATH 'Root', BINARY BASE64

Let me know if that works.

如果有效,请告诉我。