数据库表的结构为三表:科表Family, 属表Genus, 物种表Species
查询生成XML文档的SQL代码:
--两张表链接查询科、属生成XML文件
SELECT Family.FamilyName, Genus.GenusName
FROM Family
JOIN Genus ON Family.FamilyID = Genus.FamilyID
ORDER BY Family.FamilyName, Genus.GenusName
FOR XML AUTO, ROOT('Taxon')
--三张表科、属、种关联查询生成XML文件
SELECT Family.FamilyName,
Genus.GenusName,
Species.AbbreviateName
FROM Family
INNER JOIN Genus ON Family.FamilyID = Genus.FamilyID
INNER JOIN Species ON Genus.GenusID = Species.GenusID
WHERE dbo.Family.FamilyName='Apiaceae'
ORDER BY FamilyName,GenusName,AbbreviateName
FOR XML AUTO,
ROOT('Taxon')
所生成的XML文档示例:
?xml version="1.0" encoding="UTF-8"?>
<Taxon>
<Family FamilyName="Apiaceae">
<Genus GenusName="Acronema">
<Species AbbreviateName="Acronema alpinum" />
<Species AbbreviateName="Acronema astrantiifolium" />
<Species AbbreviateName="Acronema brevipedicellatum" />
<Species AbbreviateName="Acronema chienii" />
<Species AbbreviateName="Acronema chienii var. dissectum" />
<Species AbbreviateName="Acronema chinense" />
<Species AbbreviateName="Acronema chinense var. humile" />
<Species AbbreviateName="Acronema commutatum" />
<Species AbbreviateName="Acronema edosmioides" />
<Species AbbreviateName="Acronema forrestii" />
<Species AbbreviateName="Acronema gracile" />
<Species AbbreviateName="Acronema graminifolium" />
<Species AbbreviateName="Acronema handelii" />
<Species AbbreviateName="Acronema hookeri" />
<Species AbbreviateName="Acronema hookeri var. graminifolium" />
<Species AbbreviateName="Acronema minus" />
<Species AbbreviateName="Acronema muscicola" />
<Species AbbreviateName="Acronema nervosum" />
<Species AbbreviateName="Acronema paniculatum" />
<Species AbbreviateName="Acronema schneideri" />
<Species AbbreviateName="Acronema sichuanense" />
<Species AbbreviateName="Acronema tenerum" />
<Species AbbreviateName="Acronema xizangense" />
<Species AbbreviateName="Acronema yadongense" />
</Genus>
<Genus GenusName="Aegopodium">
<Species AbbreviateName="Aegopodium alpestre" />
<Species AbbreviateName="Aegopodium alpestre f. scabrum" />
<Species AbbreviateName="Aegopodium alpestre f. tenerum" />
<Species AbbreviateName="Aegopodium alpestre f. tenuisectum" />
<Species AbbreviateName="Aegopodium alpestre var. daucifolium" />
<Species AbbreviateName="Aegopodium anthriscoides" />
<Species AbbreviateName="Aegopodium brachycarpum" />
<Species AbbreviateName="Aegopodium handelii" />
<Species AbbreviateName="Aegopodium henryi" />
<Species AbbreviateName="Aegopodium latifolium" />
<Species AbbreviateName="Aegopodium tadshikorum" />
</Genus>
<Genus GenusName="Aethusa">
<Species AbbreviateName="Aethusa leptophylla" />
</Genus>
<Genus GenusName="Albertia">
<Species AbbreviateName="Albertia commutata" />
</Genus>
<Genus GenusName="Ammi">
<Species AbbreviateName="Ammi copticum" />
<Species AbbreviateName="Ammi ehrenbergii" />
<Species AbbreviateName="Ammi majus" />
<Species AbbreviateName="Ammi visnaga" />
</Genus>
<Genus GenusName="Anethum">
<Species AbbreviateName="Anethum foeniculum" />
<Species AbbreviateName="Anethum graveolens" />
<Species AbbreviateName="Anethum graveolens subsp. sowa" />
<Species AbbreviateName="Anethum japonicum" />
<Species AbbreviateName="Anethum pannorium" />
<Species AbbreviateName="Anethum pastinaca" />
<Species AbbreviateName="Anethum sowa" />
</Genus>
......
</Taxon>
执行代码:
1: using System;
2: using System.Web.UI.WebControls;
3: using System.Xml;
4:
5: public partial class Default2 : System.Web.UI.Page
6: {
7: private string xmlFilePath = @"c:\Data\Apiaceae.xml";
8: protected void Page_Load(object sender, EventArgs e)
9: {
10: XmlDocument doc = new XmlDocument();
11: doc.Load(xmlFilePath);
12: TreeNode root = new TreeNode(doc.DocumentElement.Name);
13: TreeView1.Nodes.Add(root);
14: foreach (XmlNode familynode in doc.DocumentElement.ChildNodes)
15: {
16: TreeNode family = new TreeNode("Family: " + familynode.Attributes["FamilyName"].Value + " (" + familynode.ChildNodes.Count.ToString() + ")");
17: root.ChildNodes.Add(family);
18: if (familynode.HasChildNodes)
19: {
20: foreach (XmlNode genusnode in familynode.ChildNodes)
21: {
22: TreeNode n2 = new TreeNode("Genus: " + genusnode.Attributes["GenusName"].Value+" ("+genusnode.ChildNodes.Count.ToString()+")");
23: family.ChildNodes.Add(n2);
24: if (genusnode.HasChildNodes)
25: {
26: foreach (XmlNode speciesnode in genusnode.ChildNodes)
27: {
28: TreeNode n3=new TreeNode("Species: "+speciesnode.Attributes["AbbreviateName"].Value);
29: n2.ChildNodes.Add(n3);
30: }
31: }
32: }
33: }
34: }
35: }
36: }
执行结果: