说三道四技术文摘-感悟人生的经典句子
说三道四 > 文档快照

如何替换xml中所有节点指定字符串如“AAA”替换为“BBB”,在线等!!

编辑:说三道四文库 发布时间:2018-08-19 09:26
HTML文档下载 WORD文档下载 PDF文档下载
Table Name: Response
Field: ConfigXML
如何把Response表中ConfigXML字段中所有节点中字符串“AAA”替换为“BBB”
不懂 学习 友情UP
贴出测试数据
<ConfigXML>         
<A>  
  <QuestionResponse>
    <ContentType>FileUploadQuestion</ContentType>
    <Response>OneAAA</Response>
  </QuestionResponse>  <QuestionResponse>
    <ContentType>FileUploadQuestion</ContentType>
    <Response>TwoAAA</Response>
  </QuestionResponse>
</A>         
<B>  
  <QuestionResponse>
    <ContentType>FileUploadQuestion</ContentType>
    <Response>1AAA</Response>
  </QuestionResponse>  <QuestionResponse>
    <ContentType>FileUploadQuestion</ContentType>
    <Response>2AAA</Response>
  </QuestionResponse>
</B>
</ConfigXML>
把所有ContentType=FileUploadQuestion,节点名为Response里的文本中“AAA”替换为“BBB”
你完整表结构也贴出来,你的field字段什么类型?
CREATE TABLE [dbo].[ApplicationResponse]
(
[ResponseId] [int] IDENTITY(1,1) NOT NULL,
[ConfigurationResponseXML] [xml] NULL
)
可能需要先循环表里所有row,再循环ConfigurationResponseXML里的节点ContentType=FileUploadQuestion,把节点Response里的文本中“AAA”替换为“BBB”
update Response set ConfigXML= replace(convert(varchar(max),ConfigXML) ,'AAA','BBB')
不懂,帮顶.
或者看这里有没有你需要的内容?

SQLServer2005 XML在T-SQL查询中的典型应用
http://topic.csdn.net/u/20081107/17/68aaf5a9-c596-4ab5-ae18-f3370b2ab35b.html
给个例子吧,确实比较麻烦。
6#的操作,有可能多替换掉相关内容。


DECLARE @myDoc xml
SET @myDoc = '<root>
    <item ID="1">
        <title>Ajax实战</title>
        <author>张洪举</author>
    </item>
    <item ID="2">
        <title>ASP.NET实战</title>
        <author>卢桂章</author>
    </item>
</root>'
SELECT @myDoc
 
-- 更新ID为1的item中的title元素的文本
SET @myDoc.modify('
 replace value of (/root/item[@ID=1]/title/text())[1]
 with "Ajax实战攻略"
')
SELECT @myDoc
 
-- 更新属性值
SET @myDoc.modify('
 replace value of (/root/item[@ID=2]/@ID)[1]
 with "3"
')
SELECT @myDoc 
declare @t table(id int,x xml);
insert @t select 1,N'<ConfigXML>   
<A>   
  <QuestionResponse>
  <ContentType>FileUploadQuestion</ContentType>
  <Response>OneAAA</Response>
  </QuestionResponse> 
<QuestionResponse>
  <ContentType>FileUploadQuestion</ContentType>
  <Response>TwoAAA</Response>
  </QuestionResponse>
</A>   
<B>   
  <QuestionResponse>
  <ContentType>FileUploadQuestion</ContentType>
  <Response>1AAA</Response>
  </QuestionResponse> 
<QuestionResponse>
  <ContentType>FileUploadQuestion</ContentType>
  <Response>2AABA</Response>
  </QuestionResponse>
</B>
</ConfigXML>'

insert @t select 2,N'<ConfigXML>   
<A>   
  <QuestionResponse>
  <ContentType>FileUploadQuestion</ContentType>
  <Response>123ABC</Response>
  </QuestionResponse> 
<QuestionResponse>
  <ContentType>FileUploadQuestion</ContentType>
  <Response>12AAA44</Response>
  </QuestionResponse>
</A>   
<B>   
  <QuestionResponse>
  <ContentType>FileUploadQuestion</ContentType>
  <Response>BBCAABCAAADC</Response>
  </QuestionResponse> 
<QuestionResponse>
  <ContentType>FileUploadQuestion</ContentType>
  <Response>CCAFA</Response>
  </QuestionResponse>
</B>
</ConfigXML>'


--更新
WHILE EXISTS(SELECT * FROM @t
             WHERE x.exist('//QuestionResponse/Response[contains(.,"AAA")]')=1)
    UPDATE @t SET
        x.modify('replace value of (//QuestionResponse/Response[contains(.,"AAA")]/text())[1]
                     with "BBB"')

SELECT * FROM @t;
备案号:鲁ICP备13029499号-2 说三道四 www.s3d4.cn 说三道四技术文摘