假设表名为 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
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楼。