如何用存储过程将一张表的内容按层次放入xml表中
有一个表a，他里面有数据（Previous_Flight，Next_Flight，Enter_Corridor_Time， Recent_Abnormal_Status ,Reported_Abnormal，Delay_Code， Cause_Of_Delay等有100多个）有200行的样子，现在要从a表里取出所有数据，利用存储过程，做成XML格式。例如
Previous_Flight AC133
Next_Flight CA355
Enter_Corridor_Time 2007-09-21 19:00:00
Recent_Abnormal_Status 1
Reported_Abnormal 2
Delay_Code 0
Cause_Of_Delay rain
<Dailys>
<daily_values instance= "Previous_Flight "> AC133 <daily_values>
<daily_values instance= "Next_Flight "> CA355 <daily_values>
<daily_values instance= "Enter_Corridor_Time "> 2007-09-21 19:00:00 <daily_values>
<daily_values instance= "Recent_Abnormal_Status "> 1 <daily_values>
<daily_values instance= "Reported_Abnormal "> 2 <daily_values>
<daily_values instance= "Delay_Code "> 0 <daily_values>
<daily_values instance= "Cause_Of_Delay "> rain <daily_values>
......
</Dailys>
请教一下，它的存储过程应该怎么样
[解决办法]
准备数据
Create table Tb_ Customer (id int,name nvarchar(10),Sex nvarchar(10),Age int)
insert into tb_Customer (Name,Sex,Age)(
select '张三' as name,'男' as Sex,20 as Age union all
select '李三' as name,'男' as Sex,20 as Age union all
select '王三' as name,'男' as Sex,20 as Age union all
select '赵三' as name,'女' as Sex,19 as Age
)
解决方案
declare @DataXml XML
declare @XMLString varchar(4000)
set @DataXml=(select id as "daily_values instance='ID'" ,
name as "daily_values instance='name'" ,
Sex as "daily_values instance='Sex'" ,
Age as "daily_values instance='Age'"
from tb_customer
for xml auto,ELEMENTS,ROOT('Dailys'))
set @XMLString =convert(varchar(4000),@DataXml)
set @XMLString=replace(@XMLString,'_x0020_',' ')
set @XMLString=replace(@XMLString,'_x003D_','=')
set @XMLString=replace(@XMLString,'_x0027_','"')
print @XMLString
