Sql-Server

查看 XML 元素是否存在於文件中具有特定值的任何級別

  • March 18, 2022

是否可以查詢 XML 以查找特定元素是否具有特定值？例如，如果我想查看下面的 XML 是否在<ContactFName>.

但請注意，元素的位置可能會發生變化。在某些情況下，它可能在 中/root/MCTLocations/MCTLocation，或者它可能跳到根目錄下，或者出現在其他地方……

而且，是否可以參數化元素名稱？

DECLARE @table TABLE (XmlCol XML)

INSERT INTO @table (XmlCol) VALUES ('
<root>
<MCTClientName>John</MCTClientName>
<MCTClientCity>Palm Beach</MCTClientCity>
<MCTLocations>
   <MCTLocation>
       <Address>1234 Main Street</Address>
       <ContactFName>Chris</ContactFName>
       <ContactLName>Brandt</ContactLName>
   </MCTLocation>
</MCTLocations>
</root>')

SELECT * FROM @table WHERE ??

為此，您需要使用.exist()XML 函式，因為它將返回一個 BIT（即布爾值）值，指示 XQuery 是否找到任何內容。

要處理元素的非靜態位置，您可以使用*（表示它應該檢查特定級別的所有節點，但不檢查其他級別）或//（表示它應該檢查該級別及以下的所有節點）。

以下範例使用來自問題的範例查詢作為基礎，並添加一些測試案例以將元素放置在不同的級別，並添加一個更改名稱的測試案例以表明 XQuery 不只是選擇所有內容。

測試設置（執行一次）

SET NOCOUNT ON;
CREATE TABLE #Table (ID INT NOT NULL, XmlCol XML);

INSERT INTO #Table (ID, XmlCol) VALUES (1, N'
<root>
<MCTClientName>John</MCTClientName>
<MCTClientCity>Palm Beach</MCTClientCity>
<MCTLocations>
   <MCTLocation>
       <Address>1234 Main Street</Address>
       <ContactFName>Chris</ContactFName>
       <ContactLName>Brandt</ContactLName>
   </MCTLocation>
</MCTLocations>
</root>');

INSERT INTO #Table (ID, XmlCol) VALUES (2, N'
<root>
<MCTClientName>John</MCTClientName>
<MCTClientCity>Palm Beach</MCTClientCity>
<MCTLocations>
   <MCTLocation>
       <Address>1234 Main Street</Address>
       <ContactFName>Chris</ContactFName>
       <ContactLName>Grandt</ContactLName>
   </MCTLocation>
</MCTLocations>
</root>');

INSERT INTO #Table (ID, XmlCol) VALUES (3, N'
<root>
<MCTClientName>John</MCTClientName>
<MCTClientCity>Palm Beach</MCTClientCity>
<MCTLocations>
   <MCTLocation>
       <Address>1234 Main Street</Address>
       <ContactFName>Chris</ContactFName>
   </MCTLocation>
</MCTLocations>
<ContactLName>Brandt</ContactLName>
</root>');

INSERT INTO #Table (ID, XmlCol) VALUES (4, N'
<root>
<MCTClientName>John</MCTClientName>
<MCTClientCity>Palm Beach</MCTClientCity>
<MCTLocations>
   <MCTLocation>
       <Address>1234 Main Street</Address>
       <ContactFName>Chris</ContactFName>
   </MCTLocation>
   <NewElement>
      <SubElement>
         <ContactLName>Brandt</ContactLName>
      </SubElement>
   </NewElement>
</MCTLocations>
</root>');

INSERT INTO #Table (ID, XmlCol) VALUES (5, N'
<root>
<MCTClientName>John</MCTClientName>
<MCTClientCity>Palm Beach</MCTClientCity>
<MCTLocations>
   <MCTLocation>
       <Address>1234 Main Street</Address>
       <ContactFName>Chris</ContactFName>
   </MCTLocation>
   <NewerElement>
      <ContactLName>Brandt</ContactLName>
   </NewerElement>
</MCTLocations>
</root>');

INSERT INTO #Table (ID, XmlCol) VALUES (6, N'
<root>
<MCTClientName>John</MCTClientName>
<MCTClientCity>Palm Beach</MCTClientCity>
<MCTLocations>
   <MCTLocation>
       <Address>1234 Main Street</Address>
       <ContactFName>Chris</ContactFName>
   </MCTLocation>
   <NewerElement>
   </NewerElement>
</MCTLocations>
</root>
<ContactLName>Brandt</ContactLName>
');

測試 1（*代替節點名稱）

這將檢查指定級別的所有節點，在本例中，它位於<root>. 但它不會檢查其他級別。

SELECT *
FROM   #Table tmp
WHERE  tmp.[XmlCol].exist(N'/*/ContactLName[text()="Brandt"]') = 1;

返回ID值為 3 的行。

測試 2（*代替節點名稱）

這將檢查指定級別的所有節點，在本例中，它位於<root><MCTLocations>. 但它不會檢查其他級別。

SELECT *
FROM   #Table tmp
WHERE  tmp.[XmlCol].exist(N'/root/MCTLocations/*/ContactLName[text()="Brandt"]') = 1;

返回ID值為 1 和 5 的行。

測試 3（//代替節點名稱）

這將檢查從指定級別開始的所有節點，在這種情況下<root><MCTLocations>，它正好在和 之下。

SELECT *
FROM   #Table tmp
WHERE  tmp.[XmlCol].exist(N'/root/MCTLocations//ContactLName[text()="Brandt"]') = 1;

返回ID值為 1、4 和 5 的行。

測試 4（/*或*/代替節點名稱）

SELECT *
FROM   #Table tmp
WHERE  tmp.[XmlCol].exist(N'*//ContactLName[text()="Brandt"]') = 1;

-- and:

SELECT *
FROM   #Table tmp
WHERE  tmp.[XmlCol].exist(N'//*/ContactLName[text()="Brandt"]') = 1;

兩者都返回ID值為 1、3、4 和 5 的行。

*由於是單個節點的佔位符，它們不會返回 6 的行 ID ，因此允許的最高級別將位於<root>（或任何頂級節點）之下。

測試 5（//在頂層）

這將檢查從頂層開始的所有節點。

SELECT *
FROM   #Table tmp
WHERE  tmp.[XmlCol].exist(N'//ContactLName[text()="Brandt"]') = 1;

返回ID值為 1、3、4、5 和 6 的行。

測試 6（對 XQuery 中的元素文本使用局部變數值）

DECLARE @Name NVARCHAR(50) = N'Brandt';

SELECT *
FROM   #Table tmp
WHERE  tmp.[XmlCol].exist(N'//ContactLName[text()=sql:variable("@Name")]') = 1;

SET @Name = N'Grandt';

-- exact same query, just different value in the variable
SELECT *
FROM   #Table tmp
WHERE  tmp.[XmlCol].exist(N'//ContactLName[text()=sql:variable("@Name")]') = 1;

第一個查詢返回ID值為 1、3、4、5 和 6 的行。

第二個查詢返回ID值為 2 的行。

測試 7（在 XQuery 中使用函式和字元串文字作為元素名稱）

SELECT *
FROM   #Table tmp
WHERE  tmp.[XmlCol].exist(N'//.[local-name()="NewerElement"]') = 1;

返回ID值為 5 和 6 的行。

測試 8（在 XQuery 中使用具有局部變數值的函式作為元素名稱）

DECLARE @Node NVARCHAR(50) = N'SubElement';

SELECT *
FROM   #Table tmp
WHERE  tmp.[XmlCol].exist(N'//.[local-name()=sql:variable("@Node")]') = 1;

返回ID值為 4 的行。

測試 9（將所有部分放在一起）

DECLARE @NodeName NVARCHAR(50) = N'ContactLName',
       @NodeText NVARCHAR(500) = N'Brandt';

SELECT *
FROM   #Table tmp
WHERE  tmp.[XmlCol].exist(N'//.[local-name()=sql:variable("@NodeName")]
  [text()=sql:variable("@NodeText")]') = 1;

返回ID值為 1、3、4、5 和 6 的行。

通用 XML 註釋：

XML 數據（在 SQL Server 中）被編碼為 UTF-16 Little Endian，與NVARCHAR/相同NCHAR。N因此，當值實際上是 XML 時，最好在字元串文字前加上大寫 - 。

引用自：https://dba.stackexchange.com/questions/152680

相關問答

Sql-Server

如何使用 SQL Server 中 XML 數據中的序號位置（不是名稱）解析列名？

  • April 26, 2021
檢視問答
Sql-Server

為什麼 SQL 使用相同的 XML 查詢結構進行索引掃描和查找？

  • March 31, 2021
檢視問答
Sql-Server

如何在轉換為 XML 之前在 TSQL 中轉義 XML 字元？

  • April 21, 2020
檢視問答
Sql-Server

幫助 SQL 查詢到 XML 輸出

  • March 15, 2020
檢視問答
Sql-Server

T-SQL LIKE 謂詞無法與 XML 轉換的 varchar 中的空格匹配

  • March 17, 2019
檢視問答
Sql-Server

更新格式錯誤的 XML 以允許解析

  • December 17, 2018
檢視問答