来自表的XML嵌套节点 [英] Nested Nodes in XML from a table
问题描述
亲爱的，
我有一个名为CATEGORY的表，其定义如下：
CREATE TABLE CATEGORY
（
CATEGORY_ID INTEGER IDENTITY（1,1）NOT NULL，
CATEGORY_NAME VARCHAR（40）NOT NULL约束
UC__CATEGORY__CATEGORY_NAME独特，
PARENT_CATEGORY_ID INTEGER，
CATEGORY_ICON IMAGE，
DEPTH INTEGER，
CONSTRAINT PK__CATEGORY PRIMARY KEY（CATEGORY_ID）
）
因此，稍后拍摄了以下快照：
========= ========================================= ========= ===
| CATEGORY_ID | CATEGORY_NAME | PARENT_CATEGORY_ID |深度|
======================================== ========== ============
| 1 | PC | NULL | 1 |
---------------------------------------- ----------------------
| 2 |网络| 1 | 2 |
---------------------------------------- ----------------------
| 3 |音频| 1 | 2 |
---------------------------------------- ----------------------
| 4 |视频| 1 | 2 |
---------------------------------------- ----------------------
| 5 |电视卡| 4 | 3 |
---------------------------------------- ----------------------
| 6 |显卡| 4 | 3 |
---------------------------------------- ----------------------
| 7 | AGP | 6 | 4 |
---------------------------------------- ----------------------
| 8 | PCI | 6 | 4 |
---------------------------------------- ----------------------
| 9 |输入设备| 1 | 2 |
---------------------------------------- ----------------------
我想在这个层次结构中创建以下所需的XML
档案：
<？xml version =" 1.0"编码= QUOT; UTF-8英寸？>
<硬件>
< Catgeory name =" PC" id =" 1">
< Catgeory name =" Networks" ID = QUOT; 2英寸/>
< Catgeory name =" Audio" ID = QUOT; 3英寸/>
< Catgeory name =" Video" id =" 4">
< Catgeory name =" TV Cards" ID = QUOT; 5英寸/>
< Catgeory name =" Graphics Cards" id =" 6">
< Catgeory name =" AGP" ID = QUOT; 7英寸; />
< Catgeory name =" PCI" ID = QUOT; 8英寸/>
< / Category>
< / Category>
< Catgeory name ="输入设备" ID = QUOT; 9" />
< / Catgeory>
< / Hardware>
这个文件的原因是它会是asp.net 2.0中新的
TreeView控件的数据源。
现在，使用C＃.net编程，我开始使用XmlDocument，
XmlTextWriter和XmlTextReader命名空间并开始使用可疑的
recurrsion从
快照中的记录中生成这个所需的XML文件，但是......
使用带有新的
数据类型XML的SqlServer 2005有一种简单的方法吗？
*任何提示也可以*
最好的问候
Dear all,
I have table called CATEGORY, which is defined as follows:
CREATE TABLE CATEGORY
(
CATEGORY_ID INTEGER IDENTITY(1,1) NOT NULL,
CATEGORY_NAME VARCHAR(40) NOT NULL CONSTRAINT
UC__CATEGORY__CATEGORY_NAME UNIQUE,
PARENT_CATEGORY_ID INTEGER,
CATEGORY_ICON IMAGE,
DEPTH INTEGER,
CONSTRAINT PK__CATEGORY PRIMARY KEY (CATEGORY_ID)
)
Supposly, the following snap shot was taken later:
================================================== ============
| CATEGORY_ID | CATEGORY_NAME | PARENT_CATEGORY_ID | DEPTH |
================================================== ============
| 1 | PC | NULL | 1 |
--------------------------------------------------------------
| 2 | Networks | 1 | 2 |
--------------------------------------------------------------
| 3 | Audio | 1 | 2 |
--------------------------------------------------------------
| 4 | Video | 1 | 2 |
--------------------------------------------------------------
| 5 | TV Cards | 4 | 3 |
--------------------------------------------------------------
| 6 | Graphics Cards | 4 | 3 |
--------------------------------------------------------------
| 7 | AGP | 6 | 4 |
--------------------------------------------------------------
| 8 | PCI | 6 | 4 |
--------------------------------------------------------------
| 9 | Input Devices | 1 | 2 |
--------------------------------------------------------------
and I''d like to create out of this hierarchy the following desired XML
file:
<?xml version="1.0" encoding="utf-8" ?>
<Hardware>
<Catgeory name="PC" id="1">
<Catgeory name="Networks" id="2" />
<Catgeory name="Audio" id="3" />
<Catgeory name="Video" id="4">
<Catgeory name="TV Cards" id="5" />
<Catgeory name="Graphics Cards" id="6">
<Catgeory name="AGP" id="7" />
<Catgeory name="PCI" id="8" />
</Category>
</Category>
<Catgeory name="Input Devices" id="9" />
</Catgeory>
</Hardware>
The reason for this file is that it will be a datasource of the
TreeView Control new in asp.net 2.0.
Now, programmateiclally using C#.net i started using the XmlDocument,
XmlTextWriter and XmlTextReader Namespaces and started using susing
recurrsion to genearete this desired XML file out of the records in the
snapshot, but ...
Is there an easy way of doing this using SqlServer 2005 with the new
datatype XML?
*Any hint would also be ok*
Best regards
推荐答案
coosa（co ***** @ gmail .com）写道：
coosa (co*****@gmail.com) writes:
我想在这个层次结构中创建以下所需的XML
文件：
<？xml version =" ; 1.0"编码= QUOT; UTF-8英寸？>
<硬件>
< Catgeory name =" PC" id =" 1">
< Catgeory name =" Networks" ID = QUOT; 2英寸/>
< Catgeory name =" Audio" ID = QUOT; 3英寸/>
< Catgeory name =" Video" id =" 4">
< Catgeory name =" TV Cards" ID = QUOT; 5英寸/>
< Catgeory name =" Graphics Cards" id =" 6">
< Catgeory name =" AGP" ID = QUOT; 7英寸; />
< Catgeory name =" PCI" ID = QUOT; 8英寸/>
< / Category>
< / Category>
< Catgeory name ="输入设备" ID = QUOT; 9" />
< / Catgeory>
< / Hardware>
这个文件的原因是它将是一个新的TreeView Control的数据源在asp.net 2.0中。
现在，程序化地使用C＃.net我开始使用XmlDocument，XmlTextWriter和XmlTextReader命名空间，并开始使用可疑的
recurrsion来生成这个所需的XML文件在
快照中的记录中，但是......
使用带有新的
数据类型XML的SqlServer 2005有一种简单的方法吗？
*任何提示也都可以*
and I''d like to create out of this hierarchy the following desired XML
file:
<?xml version="1.0" encoding="utf-8" ?>
<Hardware>
<Catgeory name="PC" id="1">
<Catgeory name="Networks" id="2" />
<Catgeory name="Audio" id="3" />
<Catgeory name="Video" id="4">
<Catgeory name="TV Cards" id="5" />
<Catgeory name="Graphics Cards" id="6">
<Catgeory name="AGP" id="7" />
<Catgeory name="PCI" id="8" />
</Category>
</Category>
<Catgeory name="Input Devices" id="9" />
</Catgeory>
</Hardware>
The reason for this file is that it will be a datasource of the
TreeView Control new in asp.net 2.0.
Now, programmateiclally using C#.net i started using the XmlDocument,
XmlTextWriter and XmlTextReader Namespaces and started using susing
recurrsion to genearete this desired XML file out of the records in the
snapshot, but ...
Is there an easy way of doing this using SqlServer 2005 with the new
datatype XML?
*Any hint would also be ok*
我不太擅长XML，所以我问过SQL Server MVP Kent Tegels
救命。他举了一个在AdventureWorks数据库中运行的例子：
drop function dbo.directReports
go
create function dbo.directReports
（@managerID int，@ depth tinyint）
返回xml
为
开始
声明@x xml
如果（@managerID为空）
开始
选择@x =（
选择e.EmployeeID为''@employeeID''
，c.lastName +''，''+ c.firstName as''name''
， @depth为''深度''
，dbo.directReports（e.employeeID，0）
来自HumanResources.Employee e
加入的人.contact c
on e.contactID = c.contactID
其中e.managerID为空
按lastName，firstName排序
for xml path（''employee''），type）
end
else
begin
选择@x =（
选择e.EmployeeID为''@employeeID''
，c.lastName +''，' '+ c.firstName为''name''
，@ depth + 1为''depth''
，dbo.directReports（e.employeeID，@ depth + 1）
来自HumanResources.Employee e
加入person.contact c
on e.contactID = c.contactID
其中e.managerID = @managerID
by lastName，firstName
for xml path（''employee''），type）
结束
返回@x
结束
go
选择dbo.directReports（null， 0）
来自humanresources.employee的
其中managerID为空
表示xml路径（''''），root（''employees' '），输入
go
我希望你能从中工作。
-
Erland Sommarskog，SQL Server MVP， es****@sommarskog.se
SQL Server 2005联机丛书
http://www.microsoft.com/technet/pro...ads/books.mspx
SQL Server 2000联机丛书
http://www.microsoft.com/ sql / prodinf ... ons / books.mspx
I''m not so good at XML, so I asked SQL Server MVP Kent Tegels for
help. He gave this example that runs in the AdventureWorks database:
drop function dbo.directReports
go
create function dbo.directReports
(@managerID int,@depth tinyint)
returns xml
as
begin
declare @x xml
if(@managerID is null)
begin
select @x = (
select e.EmployeeID as ''@employeeID''
,c.lastName + '', '' + c.firstName as ''name''
,@depth as ''depth''
,dbo.directReports(e.employeeID,0)
from HumanResources.Employee e
join person.contact c
on e.contactID = c.contactID
where e.managerID is null
order by lastName,firstName
for xml path(''employee''),type)
end
else
begin
select @x = (
select e.EmployeeID as ''@employeeID''
,c.lastName + '', '' + c.firstName as ''name''
,@depth + 1 as ''depth''
,dbo.directReports(e.employeeID,@depth + 1)
from HumanResources.Employee e
join person.contact c
on e.contactID = c.contactID
where e.managerID = @managerID
order by lastName,firstName
for xml path(''employee''),type)
end
return @x
end
go
select dbo.directReports(null,0)
from humanresources.employee
where managerID is null
for xml path(''''),root(''employees''),type
go
I hope you are able to work from this.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx
非常感谢
Thanks alot
谢谢Erland，
我已经尝试过你写的这个功能，但它确实有用，但是我需要它在
中我之前提到的格式;即：
<？xml version =" 1.0"编码= QUOT; UTF-8英寸？>
<硬件>
<类别名称=" PC" id =" 1">
< Category name =" Networks" ID = QUOT; 2英寸/>
< Category name =" Audio" ID = QUOT; 3英寸/>
< Category name =" Video" id =" 4">
< Category name =" TV Cards" ID = QUOT; 5英寸/>
< Category name =" Graphics Cards" id =" 6">
< Category name =" AGP" ID = QUOT; 7英寸; />
< Category name =" PCI" ID = QUOT; 8英寸/>
< / Category>
< / Category>
< Category name =" Input Devices" ID = QUOT; 9" />
< / Category>
< / Hardware>
您的函数给出的格式创建XML元素我不想要b $ b想要的。事实上，我也不擅长XML，所以我试图使用FOR XML AUTO代替FOR XML Path（''''''）。没有生成
元素，但是每个XML节点内部的属性名称根据每个表的allias自动生成
，例如：e。和c。
在我的情况下，它只有一张桌子而且它被制作成两张虽然在
现实中它是同一张桌子;所以我仍然可以写选择...从
类别作为[类别] ...加入类别为[SubCategory];这将是一个有意义且自我描述的XML标签，但不幸的是
对我的asp.net网络控制不方便我需要一个
标准名称;即分类。
最好的问候
Thanks Erland,
I have tried the function you wrote me and it works, but i need it in
the format i meantioned earlier; namely:
<?xml version="1.0" encoding="utf-8" ?>
<Hardware>
<Category name="PC" id="1">
<Category name="Networks" id="2" />
<Category name="Audio" id="3" />
<Category name="Video" id="4">
<Category name="TV Cards" id="5" />
<Category name="Graphics Cards" id="6">
<Category name="AGP" id="7" />
<Category name="PCI" id="8" />
</Category>
</Category>
<Category name="Input Devices" id="9" />
</Category>
</Hardware>
The format given by your function creates XML Elements which i don''t
want. As a matter of fact, i''m not good at XML either, so I tried to
use the FOR XML AUTO instead FOR XML Path(''....''); that generated no
elements, but the attribute names inside of each XML node where made
automatically based on the allias of each table such as: e. and c.
In my case it''s only one table and it''s made as two allises though in
reality it''s the same table; so still i might write select ... from
category as [Category] ... join Category as [SubCategory]; that will
genareta a meaningful and selfdescribing XML tags but unfortunatelly
will not be convenient for my asp.net web control whereby i need a
standard name; namely CATEGORY.
Best regards
这篇关于来自表的XML嵌套节点的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
