求sql,高手请进！

Question

id     name        address
1      张三        北京
2      李四        河北
3      王五        湖南
4      赵六        北京
将此表的name,addres中省份名称插入到数据库表中，cid为int类型必填非标识字段，并按顺序递增。
效果如下：
cid       name      province
1         张三赵六  北京
2         李四      河北
3         王五      湖南

Answer 1

--trydeclare @tb table (id nvarchar(10),
                   name nvarchar(10),
                   address nvarchar(10))
insert into @tb select '1','张三','北京'
      union all select '2','李四','河北'
      union all select '3','王五','湖南'
      union all select '4','赵六','北京'
select cid=row_number()over( order by address),
        number=(select ''+ltrim(name) from @tb where a.address=address for xml path('')),
        address as province
        from @tb a  group by address/*
cid                  number                                                                                                                                                                                                                                                           province
-------------------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ----------
1                    张三赵六                                                                                                                                                                                                                                                             北京
2                    李四                                                                                                                                                                                                                                                               河北
3                    王五                                                                                                                                                                                                                                                               湖南(3 行受影响)
*/

Answer 2

--2005
inert into new_tb
select cid=row_number()over( order by id ),
       number=(select ''+name from tb where address=t.address for xml path('')),
       address 
from tb t  group by address

Answer 3

不太明白  for xml path('')在这里的作用？有人能帮忙解释一下吗？