将 FOR XML AUTO 结果保存到 SQL 中的变量
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/914009/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
saving the FOR XML AUTO results to variable in SQL
提问by petergo
select @[email protected]('*')
for xml raw,type
Above statement will generate following alert:
Msg 6819, Level 16, State 3, Line 2
The FOR XML clause is not allowed in a ASSIGNMENT statement.
以上语句将生成以下警报：
消息 6819，级别 16，状态 3，第 2 行
ASSIGNMENT 语句中不允许使用 FOR XML 子句。
回答by sesame
For example
例如
DECLARE @xml_var XML
SET @xml_var =
(
SELECT *,
(
SELECT *
FROM Orders
WHERE Orders.CustomerID=Customers.CustomerID
FOR XML AUTO, TYPE
)
FROM Customers WHERE CustomerID='ALFKI'
FOR XML AUTO, TYPE
)
refer to : http://blogs.msdn.com/sqlprogrammability/articles/576095.aspx
参考：http: //blogs.msdn.com/sqlprogrammability/articles/576095.aspx
回答by ViPuL5
DECLARE @RESULTS XML
SET @RESULTS = (SELECT * FROM Table_Name FOR XML AUTO)
SELECT @RESULTS
回答by joedotnot
For those using SQL SERVER 2000, FOR XML is next to useless; You cannot assign the generated XML into a variable, insert into a table, etc. I had to do it manually when required, here's my sample:
对于那些使用 SQL SERVER 2000 的人来说，FOR XML 几乎是无用的；您不能将生成的 XML 分配给变量、插入表等。我必须在需要时手动执行此操作，这是我的示例：
SET NOCOUNT ON
create table #parcelData (
parcelID int
, [description] varchar(20)
)
--insert sample data
insert into #parcelData
select 1, 'apples' UNION
select 3, 'oranges' UNION
select 25, 'bananas' UNION
select 69, 'maracuja'
print '-- Example 1: FOR XML RAW --'
select parcelID as pID
from #parcelData
ORDER BY parcelID
FOR XML RAW
--> <row pID="17"/><row pID="25"/><row pID="26"/><row pID="333"/>
print '-- Example 2: Build XML Manually --'
declare @parcelRow varchar(50), @dummy int
, @xmlRowStr varchar(8000)
set @xmlRowStr = '' -- initialize.
DECLARE parcel_cursor CURSOR FOR
select DISTINCT '<row pID="' + cast(parcelID as varchar(10)) + '"/>', parcelID as parcelRow
from #parcelData
ORDER BY parcelID
OPEN parcel_cursor
FETCH NEXT FROM parcel_cursor
INTO @parcelRow, @dummy
WHILE @@FETCH_STATUS = 0 BEGIN
-- build the xml row by row.
set @xmlRowStr = @xmlRowStr + @parcelRow
-- Get next row
FETCH NEXT FROM parcel_cursor
INTO @parcelRow, @dummy
END
CLOSE parcel_cursor
DEALLOCATE parcel_cursor
--print @xmlRowStr
-- wrap a root element around
if @xmlRowStr != '' begin
set @xmlRowStr = '<ROOT>' + @xmlRowStr + '</ROOT>'
end
select @xmlRowStr as XmlOut
DROP TABLE #parcelData
SET NOCOUNT OFF
