将xml字段中的某个节点数据更新到一个其他varchar的字段如何实现呀？

[自由小菜园]

people表中有一个xml格式的字段ready1
里面存的数据是这样的
<ExtPhoto>
<isOpenInfo>0</isOpenInfo>
<isOpenPic>1</isOpenPic>
<takeCity>
<takeCityInfo>
<cityName>福建;福州市</cityName>
<cityCode>3502</cityCode>
</takeCityInfo>
<takeCityInfo>
<cityName>江苏;南京</cityName>
<cityCode>3201</cityCode>
</takeCityInfo>
</takeCity>
<winningMark>1</winningMark>
</ExtPhoto>
现在新建了一个字段cityCodes，要将ready1字段中的城市cityCode存到改新建字段中并且以逗号隔开，要如何处理啊,求救？？

[快溜]

http://www.cnblogs.com/l1pe1/archive/2010/07/28/1787254.html XML操作

[自由小菜园]

有点难度，看不太懂……

[自由小菜园]

各位大虾帮忙写一下改sql啊

[叶子]

create table people (ready1 xml)
insert into people
select 
'<ExtPhoto>
  <isOpenInfo>0</isOpenInfo>
  <isOpenPic>1</isOpenPic>
  <takeCity>
  <takeCityInfo>
  <cityName>福建;福州市</cityName>
  <cityCode>3502</cityCode>
  </takeCityInfo>
  <takeCityInfo>
  <cityName>江苏;南京</cityName>
  <cityCode>3201</cityCode>
  </takeCityInfo>
  </takeCity>
  <winningMark>1</winningMark>
</ExtPhoto>'
go
alter table people add cityCodes varchar(max)


update people
set cityCodes=
replace(replace(replace(cast(ready1.query('//cityName') as varchar(max)),'</cityName><cityName>',','),
'<cityName>',''),'</cityName>','')

select cityCodes from people
/*
cityCodes
-------------------------
福建;福州市,江苏;南京  
*/

[快溜]

declare @xml xml
set @xml='<ExtPhoto>
  <isOpenInfo>0</isOpenInfo>
  <isOpenPic>1</isOpenPic>
  <takeCity>
  <takeCityInfo>
  <cityName>福建;福州市</cityName>
  <cityCode>3502</cityCode>
  </takeCityInfo>
  <takeCityInfo>
  <cityName>江苏;南京</cityName>
  <cityCode>3201</cityCode>
  </takeCityInfo>
  </takeCity>
  <winningMark>1</winningMark>
</ExtPhoto>'

select (tb.c.value('(./cityName)[1]','varchar(20)')) as cityName,
       (tb.c.value('(./cityCode)[1]','varchar(20)')) as cityCode
 from @xml.nodes('//takeCityInfo') as tb(c)
 
 /*
 cityName             cityCode
-------------------- --------------------
福建;福州市               3502
江苏;南京                3201

(2 行受影响)

[自由小菜园]

[Quote=引用 4 楼 maco_wang 的回复:]

SQL code


create table people (ready1 xml)
insert into people
select
'<ExtPhoto>
<isOpenInfo>0</isOpenInfo>
<isOpenPic>1</isOpenPic>
<takeCity>
<takeCityInfo>
<cityName>福建;福州市</cityName>……
[/Quote]
谢了，非常好用

[自由小菜园]

学习了下sql中的xml操作，尝试解决自己的问题……呵呵

 create  table people (ready1 xml)
insert into people
select 
'<ExtPhoto>
  <isOpenInfo>0</isOpenInfo>
  <isOpenPic>1</isOpenPic>
  <takeCity>
  <takeCityInfo>
  <cityName>福建;福州市</cityName>
  <cityCode>3502</cityCode>
  </takeCityInfo>
  <takeCityInfo>
  <cityName>江苏;南京</cityName>
  <cityCode>3201</cityCode>
  </takeCityInfo>
  </takeCity>
  <winningMark>1</winningMark>
</ExtPhoto>'

alter table people add cityCodes varchar(max)

update people
set cityCodes= cast(ready1.query('for $c in //cityCode where string($c)!="" return if(count((//takeCityInfo)[.>>($c/..)[1]])=0) then string($c) else concat($c,",")') as varchar(max)) from people
select cityCodes from people
/*
(1 行受影响)
cityCodes

3502, 3201

(1 行受影响)
*/