sql - 带有case语句的外部应用和内部的Xpath返回错误

Question

我的表有 3 列：

• 员工ID
• 积极的
• 数据

数据是包含主题和标记的 XML 类型。我需要从 XML 中提取标记和主题，但是当员工 ID 为 InActive 时，即使 XML 具有主题和标记，我也只需将单个结果返回为 NULL。

以下是我尝试过的查询，但出现以下错误。

当不使用 EXISTS 引入子查询时，选择列表中只能指定一个表达式。

SELECT eo.employeeid, 
       Result.marks.[Subject], 
       Result.marks.[Mark] 
FROM   employee eo 
       OUTER apply (SELECT CASE 
             WHEN eo.active = 0 THEN (SELECT NULL AS 'Subject', 
                                             NULL AS 'Mark') 
             ELSE ((SELECT f.n.value('@Subject', 'varchar(100)') 
                           AS 
                           'Subject', 
                           f.n.value('@Mark', 'int') 
                           AS 
                           'Mark' 
                    FROM   eo.data.nodes('(/Employee/Results)') AS 
                           F(n))) 
           END AS marks) Result 

标签： sqlsql-serverxmltsqlsql-server-2012

Answer 1

DECLARE @employee TABLE
(
employeeid INT IDENTITY(1,1),
active BIT,
data XML
);

INSERT INTO @employee(active, data)
VALUES
(1, N'<Employee><Results Subject="subject 1" Mark="111"/></Employee>'),
(1, N'<Employee><Results Subject="subject 2" Mark="222"/></Employee>'),
(0, N'<Employee><Results Subject="subject 3" Mark="333"/></Employee>');

SELECT 
    eo.employeeid, eo.active,
    f.n.value('@Subject', 'varchar(100)') AS 'Subject', 
    f.n.value('@Mark', 'int') AS 'Mark' 
FROM
(
    SELECT employeeid, active,
        CASE active WHEN 1 THEN data END AS data
FROM @employee
) AS eo
OUTER APPLY eo.data.nodes('Employee/Results') AS F(n);