替换xml属性值中的特殊字符

Raj 发表于 Dev

拉吉

我需要更新表中的xml列，以删除特定节点Rating Ratingtype =“ unknown”的特殊char（％）。除上述节点外，其他节点中出现的特殊字符不应更改。以下是我的意见和期望的结果。

            Input xml:
            <Ratings>
              <Rating Ratingtype="unknown">
                <AppliedKnowledge value="Yes%">3.0</AppliedKnowledge>
                <ToolSkills>3.5</ToolSkills>
              </Rating>
              <Rating Ratingtype="known">
              <AppliedKnowledge value="%Yes%">3.0</AppliedKnowledge>
                <Experience>9.5</Experience>
                <Education>16.0</Education>
                <DbDevelopment>4.5</DbDevelopment>
              </Rating>
              <Rating Ratingtype="unknown">
                <AppliedKnowledge value="%No%">4.0</AppliedKnowledge>
                <ToolSkills>4.5</ToolSkills>
              </Rating>
            </Ratings>

            Expected output xml:
            <Ratings>
              <Rating Ratingtype="unknown">
                <AppliedKnowledge value="Yes">3.0</AppliedKnowledge>
                <ToolSkills>3.5</ToolSkills>
              </Rating>
              <Rating Ratingtype="known">
              <AppliedKnowledge value="%Yes%">3.0</AppliedKnowledge>
                <Experience>9.5</Experience>
                <Education>16.0</Education>
                <DbDevelopment>4.5</DbDevelopment>
              </Rating>
              <Rating Ratingtype="unknown">
                <AppliedKnowledge value="No">4.0</AppliedKnowledge>
                <ToolSkills>4.5</ToolSkills>
              </Rating>
            </Ratings>

Shnugo

与您的最后一个问题类似，这并不容易。

该.modify()方法将允许每个调用一次更改。这意味着您必须使用某种循环。

仍然有一些方法：

如果`%`未在其他任何地方使用，则可以简单地执行以下操作：

将XML强制转换为VARCHAR，进行简单的文本替换并将其强制转换回

SELECT CAST(REPLACE(CAST(@YourXml AS VARCHAR(MAX)),'%','')  AS XML)

比循环更好的是

切细XML并重新构建。REPLACE仅在目标值上使用：

DECLARE @x XML=
'<Ratings>
  <Rating Ratingtype="unknown">
    <AppliedKnowledge value="Yes%">3.0</AppliedKnowledge>
    <ToolSkills>3.5</ToolSkills>
  </Rating>
  <Rating Ratingtype="known">
    <AppliedKnowledge value="%Yes%">3.0</AppliedKnowledge>
    <Experience>9.5</Experience>
    <Education>16.0</Education>
    <DbDevelopment>4.5</DbDevelopment>
  </Rating>
  <Rating Ratingtype="unknown">
    <AppliedKnowledge value="%No%">4.0</AppliedKnowledge>
    <ToolSkills>4.5</ToolSkills>
  </Rating>
</Ratings>';

SELECT CAST(REPLACE(CAST(@x AS VARCHAR(MAX)),'%','')  AS XML)

SELECT R.value('@Ratingtype','varchar(max)') AS [@Ratingtype]
      ,CASE WHEN R.value('@Ratingtype','varchar(max)')='unknown'
            THEN REPLACE(R.value('(AppliedKnowledge/@value)[1]','varchar(max)'),'%','')
            ELSE R.value('(AppliedKnowledge/@value)[1]','varchar(max)') END AS [AppliedKnowledge/@Value]
      ,R.value('AppliedKnowledge[1]','varchar(max)') AS [AppliedKnowledge]
      ,R.value('ToolSkills[1]','varchar(max)') AS [ToolSkills]
      ,R.value('Experience[1]','varchar(max)') AS [Experience]
      ,R.value('Education[1]','varchar(max)') AS [Education]
      ,R.value('DbDevelopment[1]','varchar(max)') AS [DbDevelopment]
FROM @x.nodes('/Ratings/Rating') AS A(R)
FOR XML PATH('Rating'),ROOT('Ratings')

结果

<Ratings>
  <Rating Ratingtype="unknown">
    <AppliedKnowledge Value="Yes">3.0</AppliedKnowledge>
    <ToolSkills>3.5</ToolSkills>
  </Rating>
  <Rating Ratingtype="known">
    <AppliedKnowledge Value="%Yes%">3.0</AppliedKnowledge>
    <Experience>9.5</Experience>
    <Education>16.0</Education>
    <DbDevelopment>4.5</DbDevelopment>
  </Rating>
  <Rating Ratingtype="unknown">
    <AppliedKnowledge Value="No">4.0</AppliedKnowledge>
    <ToolSkills>4.5</ToolSkills>
  </Rating>
</Ratings>