高分求城市表生成XML的算法

[Xpengfee]

诚心请教各位大侠，如何将数据库里的表生成如2中XML文件的样式
1.数据库里的表
iSysCode cName iCode
1 安徽 10
2 北京 11
3 福建 12
4 甘肃 13
5 广东 14
6 广西 15
.....................
2.XmL文件
<?xml version="1.0" encoding="utf-8" ?>
<Position>
<province name="山东">
<city name="青岛">
<area name="市南区" />
<area name="市北区" />
<area name="莱西" />
</city>
<city name="济南">
<area name="历下区" />
<area name="历城区" />
</city>
<city name="烟台">
<area name="莱阳" />
<area name="福山区" />
</city>
</province>
<province name="辽宁">
<city name="沈阳">
<area name="法库县" />
<area name="辽中县" />
<area name="沈阳市" />
</city>
<city name="大连">
<area name="长海县" />
<area name="大连市" />
</city>
<city name="本溪">
<area name="本溪市" />
<area name="桓仁满族自治县" />
</city>
</province>
</Position>

[Xpengfee]

说的不怎么清楚，请大家帮帮忙啊

[贝贝壳壳]

城市呢？没说城市在数据库里是什么样的，省市区怎么对应的

[Xpengfee]

Sorry ,数据表里的iCode,长度为2的是省，4 的是市， 6 的是区县。如下面
＝＝＝＝＝＝省份＝＝＝＝＝＝＝
1 安徽 10
＝＝＝＝＝＝＝市＝＝＝＝＝＝＝＝＝＝
35 安庆 1001
36 蚌埠 1002

＝＝＝＝＝＝＝＝区县＝＝＝＝＝＝＝＝＝＝＝
404 安庆市 100101
405 怀宁县 100102
406 潜山县 100103
407 宿松县 100104
408 太湖县 100105
409 桐城市 100106

[贝贝壳壳]

把所有数据Fill到一个DataSet ds;

class Province
{
public string icode;
public string cname;
}

DataView dvCity=ds.Tables[0].DefaultView;

dvCity.RowFilter="len(icode)=2"
ArrayList list=new ArrayList();
foreach(DataRowView drv in dvCity)
{
Province p=new Province();
p.icode=drv["icode"].ToString();
p.cname=drv["cname"].ToString()
list.Add(p);

}

string str="";
foreach(Province prov in list)
{
str+=String.Format("<province name=\"{0}\" >",prov.cname);

dvCity.RowFilter="icode like '"+prov.icode+"%'";
foreach(DataRowView drv in dvCity)
{
///写城市信息到字符串.
}



}

[Xpengfee]

to viena(维也纳nn-下潜15米，潜望镜升起)
======================================
别站着说话不腰疼，有种你写一个看看，别干什么都想当然，写出来才是真本事。估计你Y的连要求都没明白就在这狂吠，你还是自己找个地凉快地吧！！！

[tianzhenjing]

用存储过程，将结果拷出来就行了
/*
RetrieveXML ''//执行这句
*/
alter Procedure RetrieveXML
(
@iParentCode int
)
AS
declare @cName varchar(50)
declare @iCode int

if Len(@iParentCode) = 1 Begin
declare curProvince cursor for
select cName, iCode from CommonArea
where len(iCode)= 2

OPEN curProvince

FETCH NEXT FROM curProvince
INTO @cName, @iCode

while @@FETCH_STATUS=0 Begin
print '<Province name="'+@cName+'">'
exec('RetrieveXML ''' + @iCode + '''')
Print '</Province>'

FETCH NEXT FROM curProvince
INTO @cName, @iCode
End

close curProvince
DEALLOCATE curProvince
End
Else if Len(@iParentCode) = 2 Begin
declare curCity cursor for
select cName, iCode from CommonArea
where len(iCode)=len(@iParentCode) + 2
and left(iCode, len(@iParentCode)) = @iParentCode

OPEN curCity

FETCH NEXT FROM curCity
INTO @cName, @iCode

while @@FETCH_STATUS=0 Begin
print '<City name="'+@cName+'">'
exec('RetrieveXML ''' + @iCode + '''')
Print '</City>'
FETCH NEXT FROM curCity
INTO @cName, @iCode
End

close curCity
DEALLOCATE curCity
End
Else if Len(@iParentCode) = 4 Begin
declare curArea cursor for
select cName, iCode from CommonArea
where len(iCode)=len(@iParentCode) + 2
and left(iCode, len(@iParentCode)) = @iParentCode

OPEN curArea

FETCH NEXT FROM curArea
INTO @cName, @iCode

while @@FETCH_STATUS=0 Begin
print '<Area name="'+@cName+'" />'

FETCH NEXT FROM curArea
INTO @cName, @iCode
End

close curArea
DEALLOCATE curArea
End

[Xpengfee]

非常感谢 fupip(小)¤(贝) 和 tianzhenjing(宁静夏天) 两位，特别是在 “宁静夏天”的帮助下问题终于解决，谢谢大家

[blackant2]

我帮你写吧.而且我赞成维也纳的意见
viena说的对,其实每个人的星星都不知道帮了多少人.不是你说的那样

using System;
using System.Collections.Generic;
using System.Data;
using System.IO;
using System.Xml;

public class MyClass
{
public static void Main()
{
try{
DataTable dt=CreateTable();
dt.Columns.Add(new DataColumn("iParentCode",typeof(string)));
//加入父iCODE
foreach(DataRow dr in dt.Rows){
dr["iParentCode"]= dr["iCode"].ToString().Substring(0,dr["iCode"].ToString().Length-2);
}
//加入根在最后一行
dt.Rows.Add(0,"Position","",null);

//建立数据集
DataSet ds=new DataSet();
ds.Tables.Add(dt);
//建立关联
DataRelation relation=new DataRelation("pc",dt.Columns["iCode"],dt.Columns["iParentCode"]);
ds.Relations.Add(relation);
DataRow RootRow=dt.Rows[dt.Rows.Count-1];
XmlWriterSettings settings = new XmlWriterSettings();
settings.Indent = true;
using (XmlWriter writer = XmlWriter.Create("e:\\test.xml", settings)) {
writexml(writer,RootRow,relation);
}
}
catch(Exception e){
Console.WriteLine(e.ToString());
}
finally{
Console.Read();
}
}
static void writexml(XmlWriter writer,DataRow dr,DataRelation relation ){

string Key="NONE";
string iCode=dr["iCode"].ToString();
switch(iCode.Length){
case 0:
Key="Position";
break;
case 2:
Key="province";
break;
case 4:
Key="city";
break;
case 6:
Key="area";
break;
}
writer.WriteStartElement(Key);
if(iCode.Length>0){
writer.WriteStartAttribute("name");
writer.WriteValue(dr[1].ToString());
writer.WriteEndAttribute();
}
foreach(DataRow dr1 in dr.GetChildRows(relation)){
writexml(writer,dr1,relation);
}
writer.WriteEndElement();
}

static DataTable CreateTable(){
DataTable dt=new DataTable();
dt.Columns.Add(new DataColumn("iSysCode ",typeof(int)));
dt.Columns.Add(new DataColumn("cName",typeof(string)));
dt.Columns.Add(new DataColumn("iCode",typeof(string)));
dt.Rows.Add(1,"安徽","10");
dt.Rows.Add(2,"安徽","20");
dt.Rows.Add(35,"安庆","1001");
dt.Rows.Add(36,"蚌埠","1002");
dt.Rows.Add(404,"安庆市","100101");
dt.Rows.Add(405,"怀宁县","100102");


return dt;
}

}


<?xml version="1.0" encoding="utf-8"?>
<Position>
<province name="安徽">
<city name="安庆">
<area name="安庆市" />
<area name="怀宁县" />
</city>
<city name="蚌埠" />
</province>
<province name="安徽" />
</Position>

[Xpengfee]

请各位Help me.给个能用的代码啊，谢谢了。

[viena]

我晕，这么简单的都不会，还写什么程序，哪儿凉快哪儿呆着去！
读数据库，遍历数据集，拼字符串都是最基本的了！

[viena]

你要是哪里遇到解决不了的问题，可以问一下
但是自己一点都不写，直接要现成代码
除了鄙视，还是鄙视~

[luck0235]

同意楼上...

foreach(省表...)
{
foreach(城市表...)
{
xml += "...";
}
}

[viena]

to 楼上
拼字符串最好用StringBuilder，直接拼字符串会用大量中间变量，效率极低