A way to get Table shema as xml ?

Dear all,
Is there a way to get from SQL table and XML shema file (XSD) that can be
read afterwards from a .NEt application ?
I know that I could read frommy ASP.NET code the whole table structure but
having the local xsd file would be faster for reading
regards
serge
Hello serge,

> Is there a way to get from SQL table and XML shema file (XSD) that can
> be read afterwards from a .NEt application ?
> I know that I could read frommy ASP.NET code the whole table structure
> but having the local xsd file would be faster for reading
AFAIK, not directly. One of the things I've done in the past is generate
information about the schema from the metadata. Something like this:
alter function dbo.GetColumnsForTable(@.TableObjectID int)
returns xml
as begin
declare @.rv xml
set @.rv = (select
c.column_id'@.position'
, c.name'name'
, y.name'dataType'
, c.max_length'maxLength'
, c.precision'precision'
, c.scale'scale'
, c.collation_name'collationName'
, c.is_nullable'nullable'
, c.is_rowguidcol'isRowGUID'
, c.is_identity'isIdentity'
, c.is_computed'isComputed'
, x.name
from sys.columns c
join sys.types y on c.system_type_id = y.system_type_id
left join sys.xml_schema_collections x on c.xml_collection_id = x.xml_collection_id
where c.object_id = @.TableObjectID
for xml path('column'),type)
return @.rv
end
go
select t.name'name',
dbo.GetColumnsForTable(t.object_id) as 'table/columns'
from sys.tables t
for xml path('table'),root('tables')
go
While its not a schema per se, you can get a lot of information doing this
kind of coding.
Thank you,
Kent Tegels
DevelopMentor
http://staff.develop.com/ktegels/