假设表名为 tabselect pollution.id,pollution.name,pollution.address,
master.name1 as name,master.phone1 as phone,
contact.name2 as name,contact.phone2 as phone from tab pollution
inner join tab master on pollution.id = master.id
inner join tab contact on pollution .id = contact.id
master.name1 as name,master.phone1 as phone,
contact.name2 as name,contact.phone2 as phone from tab pollution
inner join tab master on pollution.id = master.id
inner join tab contact on pollution .id = contact.id
解决方案 »
- 求一SQL语句,在线等!
- 用DTS从SQL2005往ORACLE 10G导入的时候报错.. ORA-01017:用户名/口令无效;登录被拒绝
- 下面两种方法等效吗?能够得到相同的结果吗?谢谢
- 删除数据库中的数据
- 急!传入整型“列表”参数到存储过程IN语句中的解决办法???
- 紧急求助,储存过程执行速度突然变的很慢!
- MySQL Query Browser浏览数据库问题
- 有关sql语句中时间格式的转换问题,40分送上
- 关于SQL server数据库分页
- sql server2005 以时间建分区表,更新时间其它字段内容莫名其妙的变了
- 请问,如何用sql语句实现排除问题!
- 一个MS SQL查询慢的问题
master.name1 as name,master.phone1 as phone,
contact.name2 as name,contact.phone2 as phone from tab pollution
inner join tab master on pollution.id = master.id
inner join tab contact on pollution .id = contact.id
for xml auto
id AS [@id],
name AS [@name],
address AS [@address],
name1 AS [master/@name],
phone1 AS [master/@phone],
fax AS [master/@fax],
name2 AS [contact/@name],
phone2 AS [contact/@phone]
FROM tb
FOR XML PATH('pollution')
列名 'name' 重复。不能对同一 XML 标记多次生成相同的属性。
go
create table [F_FACTINFO]([id] varchar(15),[name] varchar(24),[address] varchar(30),[name1] varchar(6),[phone1] int,[fax] int,[name2] varchar(6),[phone2] int)
insert [F_FACTINFO]
select '72475414-X','惠州美锐电子科技有限公司','广东省惠州市陈江德赛第三工业区','唐润光',261711,2617222,'肖育联',2568945--select * from [F_FACTINFO]select 1 as Tag,NULL as Parent
,id as [pollution!1!id]
,name as [pollution!1!name]
,address as [pollution!1!address]
,null as [master!2!name]
,null as [master!2!phone]
,null as [master!2!fax]
,null as [contact!3!name]
,null as [contact!3!phone]
from dbo.[F_FACTINFO]
union all
select 2 as Tag,1 as Parent
,id
,name
,address
,name1
,phone1
,fax
,null
,null
from dbo.[F_FACTINFO]
union all
select 3 as Tag,1 as Parent
,id
,name
,address
,null
,null
,null
,name2
,phone2
from dbo.[F_FACTINFO]
for xml explicit
--测试结果:
/*
<pollution id="72475414-X" name="惠州美锐电子科技有限公司" address="广东省惠州市陈江德赛第三工业区">
<master name="唐润光" phone="261711" fax="2617222" />
<contact name="肖育联" phone="2568945" />
</pollution>
*/
---------------------------------
-- Author: liangCK 小梁
---------------------------------
--> 生成测试数据: @T
DECLARE @T TABLE (id VARCHAR(10),name VARCHAR(24),address VARCHAR(30),name1 VARCHAR(6),phone1 INT,fax INT,name2 VARCHAR(6),phone2 INT)
INSERT INTO @T
SELECT '72475414-X','惠州美锐电子科技有限公司','广东省惠州市陈江德赛第三工业区','唐润光',261711,2617222,'肖育联',2568945--SQL查询如下:SELECT
id AS [@id],
name AS [@name],
address AS [@address],
name1 AS [master/@name],
phone1 AS [master/@phone],
fax AS [master/@fax],
name2 AS [contact/@name],
phone2 AS [contact/@phone]
FROM @T
FOR XML PATH('pollution')/*
<pollution id="72475414-X" name="惠州美锐电子科技有限公司" address="广东省惠州市陈江德赛第三工业区">
<master name="唐润光" phone="261711" fax="2617222" />
<contact name="肖育联" phone="2568945" />
</pollution>
*/
参见4楼。