sql - 使用 XML 强制转换的子查询返回超过 1 个值

Question

子查询返回超过 1 个值。当子查询跟随 =、!=、<、<=、>、>= 或子查询用作表达式时，这是不允许的。

CREATE PROCEDURE list_select (@id INT)

AS

IF (@id IS NOT NULL)
BEGIN
    DECLARE @xml XML = CAST((SELECT data FROM messages WHERE id = @id) AS XML)
    DECLARE @reference VARCHAR(MAX) =  @xml.value('(/*:xml/*:reference/text())[1]', 'varchar(50)')
    DECLARE @name VARCHAR(MAX) = @xml.value('(/*:xml/*:name/text())[1]', 'varchar(50)')

END

SELECT
    @reference as reference,
    @name as name
GO

我怎样才能让它与多个值一起工作？

标签： sqlxmlselectdeclare

Answer 1

我认为 for xml path做你想要的：

DECLARE @xml XML = (SELECT data FROM messages WHERE id = @id FOR XML PATH (''))