8wDlpd.png
8wDFp9.png
8wDEOx.png
8wDMfH.png
8wDKte.png

如何在 SQL 中查询 XML 以通过部分 text() 匹配来查找元素

vardhan 2月前

56 0

假设表列中有以下 XML:用户名测试用户

假设表列中有以下 XML:

<UserFields>
  <UserField>
    <Name>UserName</Name>
    <Value>test_user</Value>
  </UserField>
  <UserField>
    <Name>Age</Name>
    <Value>24</Value>
  </UserField>
  <UserField>
    <Name>Password</Name>
    <Value>test1234!</Value>
  </UserField>
  <UserField>
    <Name>AgentPassword</Name>
    <Value>1234Test!</Value>
  </UserField>
</UserFields>
<ActionAttributes>
  <Action>
   <Name>LoginPassword</Name>
   <Value>Test123!</Value>
  </Action>
</ActionAttributes>

我想查询 XML 以查找任何具有 <Name> 包含该单词 text() 的节点 password ,并将节点的文本替换 <Value> 为星号。在示例中,将有三次替换,最终我会得到以下 XML。

<UserFields>
  <UserField>
    <Name>UserName</Name>
    <Value>test_user</Value>
  </UserField>
  <UserField>
    <Name>Age</Name>
    <Value>24</Value>
  </UserField>
  <UserField>
    <Name>Password</Name>
    <Value>********</Value>
  </UserField>
  <UserField>
    <Name>AgentPassword</Name>
    <Value>********</Value>
  </UserField>
</UserFields>
<ActionAttributes>
  <Action>
   <Name>LoginPassword</Name>
   <Value>********</Value>
  </Action>
</ActionAttributes>

我想出了这个:

SET @MyDoc.modify('
  replace value of (//UserField[Name="Password"]/Value/text())[1]
  with "********"
');

该代码的问题是:

  1. 它对父元素的名称 ('UserField') 进行硬编码。
  2. 它对值的名称进行硬编码, <Name> 因此它只能找到“密码”而不是其他变体。

如果用英语来说,我会说:

\'对于具有 <Name> <Value> 子节点的任何节点。 如果子节点 <Name> 包含单词“密码”,则 <Value> 用星号替换子节点的文本。\'

帖子版权声明 1、本帖标题:如何在 SQL 中查询 XML 以通过部分 text() 匹配来查找元素
    本站网址:http://xjnalaquan.com/
2、本网站的资源部分来源于网络,如有侵权,请联系站长进行删除处理。
3、会员发帖仅代表会员个人观点,并不代表本站赞同其观点和对其真实性负责。
4、本站一律禁止以任何方式发布或转载任何违法的相关信息,访客发现请向站长举报
5、站长邮箱:yeweds@126.com 除非注明,本帖由vardhan在本站《sql-server》版块原创发布, 转载请注明出处!
最新回复 (0)
  • 正如许多人已经提到的,SQL Server 不支持一次性更新多元素 XML。

    请尝试以下基于 SQL Server 的 XQuery 及其 FLWOR 表达式的解决方案。

    SQL#1

    -- DDL and sample data population, start
    DECLARE @tbl TABLE (id INT IDENTITY PRIMARY KEY, xml_data XML);
    INSERT INTO @tbl (xml_data) VALUES
    (N'<UserFields>
        <UserField>
            <Name>UserName</Name>
            <Value>test_user</Value>
        </UserField>
        <UserField>
            <Name>Age</Name>
            <Value>24</Value>
        </UserField>
        <UserField>
            <Name>Password</Name>
            <Value>test1234!</Value>
        </UserField>
        <UserField>
            <Name>AgentPassword</Name>
            <Value>1234Test!</Value>
        </UserField>
    </UserFields>');
    -- DDL and sample data population, end
    
    -- just to see
    SELECT * 
        , xml_data.query('<UserFields>{
            for $x in /UserFields/*
            return element UserField {
                if (contains(($x/Name/text())[1],"Password")) then
                    ($x/*[local-name() != "Value"], element Value {"********"})
                else $x/*
            }
        }</UserFields>') AS modified_xml
    FROM @tbl;
    
    -- real deal
    UPDATE @tbl
    SET xml_data = xml_data.query('<UserFields>{
        for $x in /UserFields/*
        return element UserField {
            if (contains(($x/Name/text())[1],"Password")) then
                ($x/*[local-name() != "Value"], element Value {"********"})
            else $x/*
        }
    }</UserFields>');
    
    -- test
    SELECT * FROM @tbl;
    

    SQL#2

    对于问题中改变的 XML,我们可以使用不同的方法。

    -- DDL and sample data population, start
    DECLARE @tbl TABLE (id INT IDENTITY PRIMARY KEY, xml_data XML);
    INSERT INTO @tbl (xml_data) VALUES
    (N'<UserFields>
      <UserField>
        <Name>UserName</Name>
        <Value>test_user</Value>
      </UserField>
      <UserField>
        <Name>Age</Name>
        <Value>24</Value>
      </UserField>
      <UserField>
        <Name>Password</Name>
        <Value>test1234!</Value>
      </UserField>
      <UserField>
        <Name>AgentPassword</Name>
        <Value>1234Test!</Value>
      </UserField>
    </UserFields>
    <ActionAttributes>
      <Action>
       <Name>LoginPassword</Name>
       <Value>Test123!</Value>
      </Action>
    </ActionAttributes>');
    -- DDL and sample data population, end
    
    -- before
    SELECT * FROM @tbl;
    
    DECLARE @ReplaceWith VARCHAR(10) = '*********';
    DECLARE @UPDATE_STATUS BIT = 1;
    
    WHILE @UPDATE_STATUS > 0 
    BEGIN
        UPDATE t
        SET xml_data.modify('replace value of 
            (/*/*[contains((Name/text())[1], "Password") and Value/text()!=sql:variable("@ReplaceWith")]/Value/text())[1] 
            with sql:variable("@ReplaceWith")')
        FROM @tbl AS t
        WHERE xml_data.exist('/*/*[contains((Name/text())[1], "Password") and Value/text()!=sql:variable("@ReplaceWith")]')=1;
            
        SET @UPDATE_STATUS = @@ROWCOUNT;
        PRINT @UPDATE_STATUS;
    END;
    
    -- after
    SELECT * FROM @tbl;
    

    SQL#3

    这个是针对 XML 数据类型的 SQL Server 变量/参数。

    DECLARE @xml_data XML =
    N'<Event>
        <CaseNumber>PD200731245151</CaseNumber>
        <City>
            <CaseNumber>PD200731245770</CaseNumber>
        </City>
    </Event>';
    
    DECLARE @ReplaceWith VARCHAR(10) = '*********';
    
    WHILE (@xml_data.exist('//*[contains((./text())[1], "PD")]') = 1)
    BEGIN 
        SET @xml_data.modify('replace value of 
                (//*[contains((./text())[1], "PD")]/text())[1] 
                with sql:variable("@ReplaceWith")');
    END;
    
    -- test
    SELECT @xml_data;
    
返回
作者最近主题: