問題描述

讓我聲明我是 XML 新手.也就是說，我的問題是我有一個(gè)創(chuàng)建 XML 數(shù)據(jù)的 SQL Server，并將其放入一個(gè)必須通過安全門到達(dá)另一臺(tái)服務(wù)器的文件中.門有幾個(gè)臟"詞的列表，如果包含這些詞，將導(dǎo)致文件失敗.我需要的是一種讓 SQL 搜索 XML 數(shù)據(jù)、每個(gè)節(jié)點(diǎn)的方法，如果存在臟"值，則將其刪除(替換為空白).XML 不是強(qiáng)類型的，臟"字可能是較長(zhǎng)字符串的一部分.在這種情況下，字符串的其余部分必須保持完整.

Let me state I am an XML novice. That said, my issue is I have a SQL Server that creates XML data, and places that into a file that must pass through a security gate to another server. The gate has a list of several "dirty"words that will cause the files to fail if they are included. What I need, is a way for SQL to search the XML data, every node, and if the "dirty" value is present, strip it out (replace with blank). The XML is not strongly typed, and the "dirty"word could possibly be part of a longer string. In that case, the rest of the string must remain intact.

例如，如果臟"字是hold"，那么字符串我們認(rèn)為這些真理是不言自明的"將變成我們這些真理是不言自明的".

For example, if the "dirty" word is "hold," the string "We hold these truths to be self evident" would become "We these truths to be self evident."

同樣，這個(gè)臟"字可以在任何節(jié)點(diǎn)中，并且標(biāo)簽不會(huì)總是相同的.我需要編寫一個(gè)過程或觸發(fā)器來分析基于臟詞列表的 XML 值來清理它.

Again, this "dirty" word could be in any node, and the tags will not always be the same. I need to write a procedure or trigger that analyzes the XML value based on the dirty word list to clean it up.

推薦答案

將 XML 分解為每個(gè)節(jié)點(diǎn)一行的表格.該表需要一個(gè) id 與該節(jié)點(diǎn)在粉碎的 XML 中的位置相對(duì)應(yīng)，以便能夠?qū)懟馗?

Shred the XML to a table with one row for each node. The table needs an id that corresponds to the position of the node in the shredded XML to be able to write back the changes.

將你的壞詞放在一個(gè)表中，對(duì)于每個(gè)詞，使用 replace 將它們從帶有節(jié)點(diǎn)值的表中刪除.

Have your bad words in a table and for each word use replace to remove them from the table with the nodes values.

最后，您遍歷清理過的值并將它們一次一個(gè)節(jié)點(diǎn)寫回 XML，以用于實(shí)際修改的節(jié)點(diǎn).

Finally you loop through the cleaned values and write them back to the XML one node at a time for the nodes that was actually modified.

-- A table to hold the bad words
declare @BadWords table
(
  ID int identity,
  Value nvarchar(10)
)

-- These are the bad ones.
insert into @BadWords values
('one'),
('three'),
('five'),
('hold')

-- XML that needs cleaning
declare @XML xml = '
<root>
  <itemone ID="1one1">1one1</itemone>
  <itemtwo>2two2</itemtwo>
  <items>
    <item>1one1</item>
    <item>2two2</item>
    <item>onetwothreefourfive</item>
  </items>
  <hold>We hold these truths to be self evident</hold>
</root>
'

-- A helper table to hold the values to modify
declare @T table
(
  ID int identity,
  Pos int,
  OldValue nvarchar(max),
  NewValue nvarchar(max),
  Attribute bit
)

-- Get all attributes from the XML
insert into @T(Pos, OldValue, NewValue, Attribute)
select row_number() over(order by T.N),
       T.N.value('.', 'nvarchar(max)'),
       T.N.value('.', 'nvarchar(max)'),
       1
from @XML.nodes('//@*') as T(N)

-- Get all values from the XML
insert into @T(Pos, OldValue, NewValue, Attribute)
select row_number() over(order by T.N),
       T.N.value('text()[1]', 'nvarchar(max)'),
       T.N.value('text()[1]', 'nvarchar(max)'),
       0
from @XML.nodes('//*') as T(N)

declare @ID int
declare @Pos int
declare @Value nvarchar(max)
declare @Attribute bit

-- Remove the bad words from @T, one bad word at a time
select @ID = max(ID) from @BadWords
while @ID > 0
begin
  select @Value = Value
  from @BadWords
  where ID = @ID

  update @T
  set NewValue = replace(NewValue, @Value, '')

  set @ID -= 1
end

-- Write the cleaned values back to the XML
select @ID = max(ID) from @T
while @ID > 0
begin
  select @Value = nullif(NewValue, OldValue),
         @Attribute = Attribute,
         @Pos = Pos
  from @T
  where ID = @ID

  print @Attribute

  if @Value is not null
    if @Attribute = 1  
      set @XML.modify('replace value of ((//@*)[sql:variable("@Pos")])[1] 
                       with sql:variable("@Value")')
    else
      set @XML.modify('replace value of ((//*)[sql:variable("@Pos")]/text())[1] 
                           with sql:variable("@Value")')
  set @ID -= 1
end

select @XML

注意:在某些情況下，上面的代碼不會(huì)處理修改本身產(chǎn)生錯(cuò)誤值的值.

Note: In some cases the code above will not deal with values where the modification itself creates the bad value.

<item>fioneve</item>

將被修改為

<item>five</item>

這篇關(guān)于SQL Server XML 字符串操作的文章就介紹到這了，希望我們推薦的答案對(duì)大家有所幫助，也希望大家多多支持html5模板網(wǎng)！