sql - 从 SQL Server 中的重复数据填充不同的列表

Question

我需要从一个 XML 文件中收集不同员工的列表，该文件包含每个员工的销售记录。不幸的是，XML 文件中的数据并不完全“一致”。该文件的结构如下:

<Sale EmployeeId="67890" EmployeeName="John" EmployeeManagerId="12345" 
      CustomerName="Bob" SaleNumber="..." />
<Sale EmployeeId="67890" EmployeeName="John" EmployeeManagerId="12345" 
      CustomerName="Pat" SaleNumber="..." />
<Sale EmployeeId="67890" EmployeeName=""     EmployeeManagerId="12345" 
      CustomerName="Sally" SaleNumber="..." />
<Sale EmployeeId="67890" EmployeeName=""     EmployeeManagerId="12345" 
      CustomerName="Sue" SaleNumber="..." />
<Sale EmployeeId="67890" EmployeeName="John" EmployeeManagerId=""      
      CustomerName="Jack" SaleNumber="..." />
<Sale EmployeeId="58203" EmployeeName="Fred" EmployeeManagerId=""      
      CustomerName="Bill" SaleNumber="..." />

此 XML 文件被上传到 Web 应用程序，该应用程序将其内容(作为 XML)传递给 SQL Server 中的存储过程进行处理。由于此文件的大小(最多 30,000 个元素)，我希望尽可能少地在 Web 应用程序中进行处理。

到目前为止，我想到的最佳解决方案是为每个不同的 EmployeeId 和 ManagerId 值创建一个包含一行的临时表。然后，对于表中的每一行，循环遍历具有匹配 EmployeeId 的 XML 元素，直到找到名称不为空的条目(然后对 ManagerId 重复)。

因此，对于每个唯一的员工 ID，我将对结果进行两次迭代，以查看是否可以找到他们的姓名和经理的 ID。

处理文件后，我希望 Employee 表看起来像这样:

+---------+------+------------+
| Id (PK) | Name | ManagerId  |
+---------+------+------------+
| 12345   | NULL | NULL       |
| 67890   | John | 12345      |
| 58203   | Fred | NULL       |
+---------+------+------------+

是否有更有效(且程序更少)的解决方案？

Answer 1

这会得到结果，但如果样本数据不同，可能需要进行一些清理工作。

DECLARE @T TABLE ( x XML )
INSERT  INTO @T
        ( x )
VALUES  ( '<Sale EmployeeId="67890" EmployeeName="John" EmployeeManagerId="12345"        CustomerName="Bob" SaleNumber="..." />' )
    ,   ( '<Sale EmployeeId="67890" EmployeeName="John" EmployeeManagerId="12345"        CustomerName="Pat" SaleNumber="..." />' ),
        ( '<Sale EmployeeId="67890" EmployeeName=""     EmployeeManagerId="12345"        CustomerName="Sally" SaleNumber="..." />' )
     ,  ( '<Sale EmployeeId="67890" EmployeeName=""     EmployeeManagerId="12345"        CustomerName="Sue" SaleNumber="..." />' ),
        ( '<Sale EmployeeId="67890" EmployeeName="John" EmployeeManagerId=""             CustomerName="Jack" SaleNumber="..." />' ),
        ( '<Sale EmployeeId="58203" EmployeeName="Fred" EmployeeManagerId=""             CustomerName="Bill" SaleNumber="..." />' ) 

;WITH c 
AS (

SELECT DISTINCT ID = x.value('(/Sale/@EmployeeId)[1]', 'int')
      , NAME = x.value('(/Sale/@EmployeeName)[1]', 'varchar(4)')
      , ManagerID = x.value('(/Sale/@EmployeeManagerId)[1]', 'int')
FROM    @t
WHERE  x.value('(/Sale/@EmployeeName)[1]', 'varchar(4)') <> ''
)

SELECT ID, NAME, ManagerID =MIN( NULLIF(ManagerID, 0))
FROM c 
GROUP BY ID, Name
UNION 
SELECT ManagerID, NULL, NULL
FROM c
WHERE ManagerID NOT IN (SELECT DISTINCT ID FROM c)
    AND ManagerID <> 0