大家好：向各位高手请教个问题：sql server2000创建表的时候能否自动生成xml文档，查询的时候用for xml auto可以，但是创建的时候好像不行，不知道有没有别的办法能够把数据库的结构生成xml文档。
哪位知道的话，请帮帮忙，谢谢了！如果解决了，四点钟结帖。再次谢谢！
致，
礼！
下面的语句查询当前数据库的所有表结构:
SELECT
表名=case when a.colorder=1 then d.name else '' end,
字段序号=a.colorder,
字段名=a.name,
标识=case when COLUMNPROPERTY( a.id,a.name,'IsIdentity')=1 then '√'else '' end,
主键=case when exists(SELECT 1 FROM sysobjects where xtype='PK' and name in (
SELECT name FROM sysindexes WHERE indid in(
SELECT indid FROM sysindexkeys WHERE id = a.id AND colid=a.colid
))) then '√' else '' end,
类型=b.name,
占用字节数=a.length,
长度=COLUMNPROPERTY(a.id,a.name,'PRECISION'),
小数位数=isnull(COLUMNPROPERTY(a.id,a.name,'Scale'),0),
允许空=case when a.isnullable=1 then '√'else '' end,
默认值=isnull(e.text,''),
字段说明=isnull(g.[value],'')
FROM syscolumns a
left join systypes b on a.xtype=b.xusertype
inner join sysobjects d on a.id=d.id and d.xtype='U' and d.name<>'dtproperties'
left join syscomments e on a.cdefault=e.id
left join sysproperties g on a.id=g.id and a.colid=g.smallid
order by a.id,a.colorder
---------------------------------------------------------------
select rtrim(b.name) as colname
,case when h.id is not null then 'PK' else '' end as primarykey
,type_name(b.xusertype) + case when b.colstat & 1 = 1 then '[ID(' + convert(varchar,ident_seed(a.name)) + ',' + convert(varchar,ident_incr(a.name)) + ')]' else '' end as type
,b.length
,case b.isnullable when 0 then 'N' else 'Y' end as [isnull]
,isnull(e.text,'') as [default]
,isnull(c.value,'') as descript
from sysobjects a,syscolumns b
left outer join sysproperties c on b.id = c.id and b.colid = c.smallid
left outer join syscomments e on b.cdefault = e.id
left outer join (select g.id,g.colid from sysindexes f,sysindexkeys g where f.id = g.id and f.indid = g.indid and f.indid > 0 and f.indid < 255 and (f.status & 2048)<>0) h on b.id = h.id and b.colid = h.colid
where a.id = b.id
and a.id = object_id('sales') --tablename改成你要导出的表的名称
order by b.colid
for xml raw
---------------------------------------------------------------
1<row colname="stor_id" default="" descript="" isnull="N" length="4" primarykey="PK" type="char"></row>
result:
1<row colname="ord_num" default="" descript="" isnull="N" length="20" primarykey="PK" type="varchar"></row>
1<row colname="ord_date" default="" descript="" isnull="N" length="8" primarykey="" type="datetime"></row>
1<row colname="qty" default="" descript="" isnull="N" length="2" primarykey="" type="smallint"></row>
1<row colname="payterms" default="" descript="" isnull="N" length="12" primarykey="" type="varchar"></row>
1<row colname="title_id" default="" descript="" isnull="N" length="6" primarykey="PK" type="tid"></row>
---------------------------------------------------------------
Study
---------------------------------------------------------------
1.如果你想将表的行和列生成的XML文档，既有元素和子元素，也有属性，你要用FOR XML EXPLICIT
2.可以通过SP的方式来实现：
create proc prGetTable_xml
@tablename sysname
as
set nocount on
begin
select '
'+'<'+@tablename+'>'
select rtrim(b.name) as colname
,case when h.id is not null then 'PK' else '' end as primarykey
,type_name(b.xusertype) + case when b.colstat & 1 = 1 then '[ID(' + convert(varchar,ident_seed(a.name)) + ',' + convert(varchar,ident_incr(a.name)) + ')]' else '' end as type
,b.length
,case b.isnullable when 0 then 'N' else 'Y' end as [isnull]
,isnull(e.text,'') as [default]
,isnull(c.value,'') as [description]
from sysobjects a,
syscolumns b
left outer join sysproperties c on b.id = c.id and b.colid = c.smallid
left outer join syscomments e on b.cdefault = e.id
left outer join (select g.id,g.colid from sysindexes f,sysindexkeys g
where f.id = g.id and f.indid = g.indid and f.indid > 0
and f.indid < 255 and (f.status & 2048)<>0) h on b.id = h.id and b.colid = h.colid
where a.id = b.id
and a.id = object_id(@tablename)
order by b.colid
for xml raw
select '
'
end
go
这样你可以通过HTTP访问：
http://localhost/gettable?sql=execute%prGetTable_xml%20@tablename='sales'
---------------------------------------------------------------
---------------------------------------------------------------
alter proc usp_getXmltable
@xmldoc text
as
set nocount on
Declare @hdoc int
begin
exec sp_xml_preparedocument @hdoc output,@xmldoc
SELECT * FROM OPENXML(@hDoc, '/xmltable/row', 1)
WITH ( tablename varchar(50) '../tablename',
colname varchar(50),
primarykey char(2),
type varchar(10),
length int,
[isnull] char(1),
[default] varchar(100),
descript varchar(500)
)
exec sp_xml_removedocument @hdoc
end
go
create table #temptable (
tablename varchar(50) ,
colname varchar(50),
primarykey char(2),
type varchar(10),
length int,
[isnull] char(1),
[default] varchar(100),
descript varchar(500)
)
GO
declare @xmldoc varchar(2000)
set @xmldoc='
1<xmltable>
2<tablename>store</tablename>
3<row colname="stor_id" default="" descript="" isnull="N" length="4" primarykey="PK" type="char"></row>
4<row colname="ord_num" default="" descript="" isnull="N" length="20" primarykey="PK" type="varchar"></row>
5<row colname="ord_date" default="" descript="" isnull="N" length="8" primarykey="" type="datetime"></row>
6<row colname="qty" default="" descript="" isnull="N" length="2" primarykey="" type="smallint"></row>
7<row colname="payterms" default="" descript="" isnull="N" length="12" primarykey="" type="varchar"></row>
8<row colname="title_id" default="" descript="" isnull="N" length="6" primarykey="PK" type="int"></row>
9</xmltable>
'
insert into #temptable exec usp_getXmltable @xmldoc
go
select * from #temptable
declare @tempsql nvarchar(4000)
Declare @tablename varchar(50)
Declare @column varchar(50)
Declare @tempcol varchar(2000)
--Get table neme from #temptable
select @tablename =max(tablename) from #temptable
-- Init @tempsql string
Set @tempsql='create table '+@tablename+'('
Set @tempcol =''
Declare tabletype cursor for
select colname +' '+type+case when type in ('char','varchar','nchar','nvarchar','varbinary','binary','decimal')
then '('+ convert(varchar(8),length)+')'
else ''end
from #temptable
open tabletype
fetch next from tabletype into @column
while @@fetch_status=0
begin
select @tempcol=@tempcol+','+@column
fetch next from tabletype into @column
end
close tabletype
deallocate tabletype
if @tempcol is not null
begin
select @tempsql=@tempsql+substring(@tempcol,2,len(@tempcol))+')'
exec sp_executesql @tempsql
end