sql-server - T-SQL 查询中的 XPath

Question

我有两个表，XMLtable 和 filterTable。

我需要 XMLtable 中的所有 XMLtable.ID 值，其中 Col_X 中的数据包含 MyElement，其内容与filterTable中的filterColumn匹配。

Col_X 中每一行的 XML 可能包含多个 MyElement，并且我需要该 ID，以防这些元素中的任何一个匹配filterColumn 中的任意值。

问题是这些列实际上是 varchar(max) 数据类型，并且表本身很大(比如 50GB)。所以这个查询需要尽可能优化。

这是我现在所在位置的一个示例，它仅返回第一个匹配元素等于我正在查找的元素之一的行。由于大量不同的错误消息，我似乎无法更改它以与我想要的所有相同命名的元素进行比较。

SELECT ID, 
   CAST(Col_X AS XML).value('(//*[local-name()=''MyElement''])', N'varchar(25)') 
FROM  XMLtable

...然后将结果与 filterTable 进行比较。这已经需要 5 分钟以上了。

我想要实现的目标是:

SELECT ID
FROM XMLtable
WHERE CAST(Col_X AS XML).query('(//*[local-name()=''MyElement''])') 
   IN (SELECT filterColumn FROM filterTable)

我目前实现此目的的唯一方法是使用 LIKE 运算符，这会花费大约一千倍的时间。

现在，显然不能开始更改列的数据类型或其他任何内容。这就是我必须处理的事情。 :)

Answer 1

试试这个:

SELECT
  ID,
  MyElementValue
FROM 
  (
    SELECT ID, myE.value('(./text())[1]', N'VARCHAR(25)') AS 'MyElementValue'
    FROM XMLTable
      CROSS APPLY (SELECT CAST(Col_X AS XML)) as X(Col_X)
      CROSS APPLY X.Col_X.nodes('(//*[local-name()="MyElement"])') as T2(myE)
  ) T1
WHERE MyElementValue IN (SELECT filterColumn FROM filterTable)

还有这个:

SELECT
  ID,
  MyElementValue
FROM 
  (
    SELECT ID, myE.value('(./text())[1]', N'VARCHAR(25)') AS 'MyElementValue'
    FROM XMLTable
      CROSS APPLY (SELECT CAST(Col_X AS XML)) as X(Col_X)
      CROSS APPLY X.Col_X.nodes('//MyElement') as T2(myE)
  ) T1
WHERE MyElementValue IN (SELECT filterColumn FROM filterTable)

更新

我认为您正在经历此处描述的情况 Compute Scalars, Expressions and Execution Plan Performance 。到 XML 的转换被推迟到每次调用 value 函数时。您应该进行的测试是将 Col_X 的数据类型更改为 XML。

如果这不是一个选项，您可以将 XMLTable 中所需的行查询到具有 XML 列的临时表中，然后对临时表执行上面的查询，而无需转换为 XML。

CREATE TABLE #XMLTable
(
  ID int,
  Col_X xml
)

INSERT INTO #XMLTable(ID, Col_X)
SELECT ID, Col_X
FROM XMLTable

SELECT
      ID,
      MyElementValue
    FROM 
      (
        SELECT ID, myE.value('(./text())[1]', N'varchar(25)') AS 'MyElementValue'
        FROM #XMLTable
          CROSS APPLY Col_X.nodes('//MyElement') as T2(myE)
      ) T1
    WHERE MyElementValue IN (SELECT filterColumn FROM filterTable)


DROP TABLE #XMLTable