非常感谢 CN_SQL:你写的这个T-sql可以参考哪本SQL书:update a set col.modify(' insert <repTest OcrLanguages="{sql:column("b.OcrLanguages")}" Index="{sql:column("b.Index")}" Action="verygood" /> as first into (FlowTemplate/FlowActionProperty)[1] ') from t1 a join (select id, OcrLanguages = col.value('(FlowTemplate/FlowActionProperty/ConvertToPdfProperty/@OcrLanguages)[1]','varchar(10)'), [Index] = col.value('(FlowTemplate/FlowActionProperty/ConvertToPdfProperty/@Index)[1]','varchar(10)') from t1) as b on b.id = a.idupdate t1 set col.modify(' delete (FlowTemplate/FlowActionProperty/*[2]) ')
首先,2005里如果要超过8000,是要这样定义:varchar(max) ,这样的话可以存储2G的内容.其次,看懂你意思了,你能否把你的数据增加一下,你不是说有多种ConvertToPdfProperty?
那我想看看他的位置是什么样的,是否固定.
--你的需求是否可以这样简化为,假如我有这样的XML数据: <FlowTemplate>
<ConvertToPdfProperty Action="good"/>
<ConvertToPdfProperty1 Action="good"/>
<ConvertToPdfProperty Action="good"/>
<ConvertToPdfProperty2 Action="good"/>
<ConvertToPdfProperty Action="good"/>
</FlowTemplate>
我要替换为 <FlowTemplate>
<OcrLanguages Action="verygood"/>
<ConvertToPdfProperty1 Action="good"/>
<OcrLanguages Action="verygood"/>
<ConvertToPdfProperty2 Action="good"/>
<OcrLanguages Action="verygood"/>
</FlowTemplate>
你的意思是这个么?
是不是一定是包含在FlowActionProperty这个节点里.你要说清楚你的XML架构.
<ConvertToPdfProperty OcrLanguages="EN" Index="0" Action="good" />
<ImportToParaDocProperty UserName="" PassWord="" Index="1" Action="ImportToParaDoc" />
</FlowActionProperty> 这个节点里的内容是唯一的,
但是
有可能第二条记录(第二个xml)时是
<FlowActionProperty>
<ConvertToPdfProperty OcrLanguages="CN" Index="1" Action="good" />
<ImportToParaDocProperty UserName="" PassWord="" Index="1" Action="ImportToParaDoc" />
</FlowActionProperty>
改成:
<FlowActionProperty>
<repTest OcrLanguages="CN" Index="1" Action="verygood" />
<ImportToParaDocProperty UserName="" PassWord="" Index="1" Action="ImportToParaDoc" />
</FlowActionProperty>=========================
有可能第二条记录时是
<FlowActionProperty>
<ConvertToPdfProperty OcrLanguages="JP" Index="2" Action="good" />
<ImportToParaDocProperty UserName="" PassWord="" Index="1" Action="ImportToParaDoc" />
</FlowActionProperty> 改成:
<FlowActionProperty>
<repTest OcrLanguages="JP" Index="2" Action="verygood" />
<ImportToParaDocProperty UserName="" PassWord="" Index="1" Action="ImportToParaDoc" />
</FlowActionProperty>
有个表字段类型如下:
-----------------------------------
flowId uniqueidentifier (唯一)
flowXmlData XML
---------------------------------------------- 注,上面的xml是从 flowXmlData 取出来的。
还是多条纪录里的1个FlowActionProperty节点修改?我想确定你说的第1条,第2条的含义.
另外你描述的第1条,和第2条,结构是一样的,都是
<FlowActionProperty>
<ConvertToPdfProperty OcrLanguages="xx" Index="xx" Action="good" />
....其他节点
</FlowActionProperty> 你只是要把
ConvertToPdfProperty替换成repTest
以及把Action="good"替换成Action="vefygood"是这个意思么?
一条记录就一个 <FlowActionProperty>
ConvertToPdfProperty替换成repTest
以及把Action="good"替换成Action="vefygood"
没错,
还有OcrLanguages="xx" Index="xx" 照搬
create table tb (flowID int,flowXmlData xml)
insert into tb
select 1,'<FlowTemplate Id="1c4f6937-d971-41f2-9b9c-2b502bf2b3bf" Name="keith20080630" Description="">
<FlowDocument Id="acee25f3-3fcb-48e9-aa02-0634a046ffc8" PageCount="0">
<FlowPage>
<ContentZone />
</FlowPage>
</FlowDocument>
<FlowActionProperty>
<ConvertToPdfProperty OcrLanguages="EN" Index="0" Action="good" />
<ImportToParaDocProperty UserName="" PassWord="" Index="1" Action="ImportToParaDoc" />
</FlowActionProperty>
<FlowProfile>
</FlowProfile>
</FlowTemplate>
'
union all
select 2,'<FlowTemplate Id="1c4f6937-d971-41f2-9b9c-2b502bf2b3bf" Name="keith20080630" Description="">
<FlowDocument Id="acee25f3-3fcb-48e9-aa02-0634a046ffc8" PageCount="0">
<FlowPage>
<ContentZone />
</FlowPage>
</FlowDocument>
<FlowActionProperty>
<ConvertToPdfProperty OcrLanguages="EN" Index="1" Action="good" />
<ImportToParaDocProperty UserName="" PassWord="" Index="1" Action="ImportToParaDoc" />
</FlowActionProperty>
<FlowProfile>
</FlowProfile>
</FlowTemplate>
'
union all
select 3,'<FlowTemplate Id="1c4f6937-d971-41f2-9b9c-2b502bf2b3bf" Name="keith20080630" Description="">
<FlowDocument Id="acee25f3-3fcb-48e9-aa02-0634a046ffc8" PageCount="0">
<FlowPage>
<ContentZone />
</FlowPage>
</FlowDocument>
<FlowActionProperty>
<tttt OcrLanguages="EN" Index="0" Action="good" />
<ImportToParaDocProperty UserName="" PassWord="" Index="1" Action="ImportToParaDoc" />
</FlowActionProperty>
<FlowProfile>
</FlowProfile>
</FlowTemplate>
'update tb
set flowXmlData.modify('replace value of(/FlowTemplate/FlowActionProperty/ConvertToPdfProperty/@Action)[1] with "verygood"')
where flowXmlData.exist('/FlowTemplate/FlowActionProperty/ConvertToPdfProperty[@Action = "good"]') = 1
select * from tb
drop table tb
use tempdb
go
create table t1(id int identity(1,1),col xml)insert t1 select '
<FlowTemplate Id="1c4f6937-d971-41f2-9b9c-2b502bf2b3bf" Name="keith20080630" Description="">
<FlowDocument Id="acee25f3-3fcb-48e9-aa02-0634a046ffc8" PageCount="0">
<FlowPage>
<ContentZone />
</FlowPage>
</FlowDocument>
<FlowActionProperty>
<ConvertToPdfProperty OcrLanguages="CN" Index="1" Action="good" />
<ImportToParaDocProperty UserName="" PassWord="" Index="1" Action="ImportToParaDoc" />
</FlowActionProperty>
<FlowProfile>
</FlowProfile>
</FlowTemplate>
'
union all select '
<FlowTemplate Id="1c4f6937-d971-41f2-9b9c-2b502bf2b3bf" Name="keith20080630" Description="">
<FlowDocument Id="acee25f3-3fcb-48e9-aa02-0634a046ffc8" PageCount="0">
<FlowPage>
<ContentZone />
</FlowPage>
</FlowDocument>
<FlowActionProperty>
<ConvertToPdfProperty OcrLanguages="JP" Index="2" Action="good" />
<ImportToParaDocProperty UserName="" PassWord="" Index="1" Action="ImportToParaDoc" />
</FlowActionProperty>
<FlowProfile>
</FlowProfile>
</FlowTemplate> '
goupdate a
set col.modify('
insert <repTest OcrLanguages="{sql:column("b.OcrLanguages")}" Index="{sql:column("b.Index")}" Action="verygood" />
as first into (FlowTemplate/FlowActionProperty)[1]
')
from t1 a join
(select
id,
OcrLanguages = col.value('(FlowTemplate/FlowActionProperty/ConvertToPdfProperty/@OcrLanguages)[1]','varchar(10)'),
[Index] = col.value('(FlowTemplate/FlowActionProperty/ConvertToPdfProperty/@Index)[1]','varchar(10)')
from t1) as b
on b.id = a.idupdate t1
set col.modify('
delete (FlowTemplate/FlowActionProperty/*[2])
')select * from t1
godrop table t1
go
你运行一下,看看是不是你要的结果
set col.modify('
insert <repTest OcrLanguages="{sql:column("b.OcrLanguages")}" Index="{sql:column("b.Index")}" Action="verygood" />
as first into (FlowTemplate/FlowActionProperty)[1]
')
from t1 a join
(select
id,
OcrLanguages = col.value('(FlowTemplate/FlowActionProperty/ConvertToPdfProperty/@OcrLanguages)[1]','varchar(10)'),
[Index] = col.value('(FlowTemplate/FlowActionProperty/ConvertToPdfProperty/@Index)[1]','varchar(10)')
from t1) as b
on b.id = a.idupdate t1
set col.modify('
delete (FlowTemplate/FlowActionProperty/*[2])
')