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/

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
sergeHello 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_collec
tion_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/

A simple Problem with XDR and xsd

Hi,
i am using sql server 2000 SP 4 and i had also installed SQLXML 3.0 SP 2
i had some XSD in my virtual directory when i query my xsd then for simle xsd
which are based on a single table it works fine as show below
<?xml version="1.0" encoding="utf-8" ?>
<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"
xmlns:sql="urn:schemas-microsoft-com:mapping-schema">
<xsd:element name="Customers" sql:relation="Customers">
<xsd:complexType>
<sequence>
<xsd:attribute name="CustomerID" sql:field="CustomerID" type="xsd:
string"/>
<xsd:attribute name="CompanyName" sql:field="CompanyName" type="xsd:
string"/>
<xsd:attribute name="ContactName" sql:field="ContactName" type="xsd:
string"/>
</sequence>
</xsd:complexType>
</xsd:element>
</xsd:schema>
but when my xsd conatins multiple table it same cannot open the page the
problem i am facing with my xdr the xdr below does not works
<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"
xmlns:sql="urn:schemas-microsoft-com:mapping-schema">
<xsd:element name="CustomerRecord" sql:relation="Customers" >
<xsd:complexType>
<xsd:sequence>
<xsd:element name="Contact" sql:field="ContactName"
type="xsd:string" />
<xsd:element name="Title" sql:field="ContactTitle" type="xsd:
string" />
<xsd:element name="Company" sql:field="CompanyName"
type="xsd:string" />
<xsd:element ref="Order" sql:relationship="CustomerOrder" />
</xsd:sequence>
<xsd:attribute name="ID" sql:field="CustomerID" type="xsd:
string" />
</xsd:complexType>
</xsd:element>
<xsd:element name="Order" sql:relation="Orders" >
<xsd:complexType>
<xsd:sequence>
<xsd:element ref="Items" sql:relationship="OrderOrderDetails"
/>
</xsd:sequence>
<xsd:attribute name="OrderID" type="xsd:int"/>
<xsd:attribute name="Ordered" sql:field="OrderDate" type="xsd:
dateTime"/>
<xsd:attribute name="Shipped" sql:field="ShippedDate" type="xsd:
dateTime"/>
</xsd:complexType>
</xsd:element>
<xsd:element name="Items" sql:relation="[Order Details]" >
<xsd:complexType>
<xsd:sequence>
<xsd:element ref="ItemPurchased"
sql:relationship="OrderDetailsProducts" />
</xsd:sequence>
<xsd:attribute name="Price" sql:field="UnitPrice" type="xsd:
int"/>
<xsd:attribute name="Quantity" type="xsd:int"/>
</xsd:complexType>
</xsd:element>
<xsd:element name="ItemPurchased" type="Products" sql:relation="Products"
/>
<xsd:complexType name="Products">
<xsd:sequence>
<xsd:element name="Name" sql:field="ProductName" type="xsd:
string" />
<xsd:element name="NumberInStock" sql:field="UnitsInStock"
type="xsd:int" />
</xsd:sequence>
</xsd:complexType>
<xsd:annotation>
<xsd:appinfo>
<sql:relationship name="CustomerOrder"
parent="Customers"
parent-key="CustomerID"
child="Orders"
child-key="CustomerID" />
<sql:relationship name="OrderOrderDetails"
parent="Orders"
parent-key="OrderID"
child="[Order Details]"
child-key="OrderID" />
<sql:relationship name="OrderDetailsProducts"
parent="[Order Details]"
parent-key="ProductID"
child="Products"
child-key="ProductID" />
</xsd:appinfo>
</xsd:annotation>
</xsd:schema>
Thanks in advance
vinod nauriyal
Hi,
Could you please provide more details on why it doesn't work?
What error do you get?
Thanks,
Monica Frintu
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
|||Hi Monica,
Thanks for you response .When i search the log it is giving me
Object_not_a_collection kind of error .
stype=www&vtype=vdir&title=IISAdmin&spath=&dpath=I IS%
3A//localhost/W3SVC/2/ROOT/IISAdmin|17|800a01c3|Object_not_a_collection 500
Mozilla/4.0+(compatible;+MSIE+6.0;+Windows+NT+5.0)
2005-06-20 14:51:43 127.0.0.1 IN002\ic002260 127.0.0.1 6198 GET /iistat.asp
thisState= 200 Mozilla/4.0+(compatible;+MSIE+6.0;+Windows+NT+5.0)
Even in my simple XSD if i include the tag <SEQUENCE> it gives me this error
Regards
Vinod Nauriyal
Message posted via http://www.droptable.com
|||This doesn't look like an error from SQLXML. Could you be more precise about
your steps? For example:
- Your IIS and SqlXml virtual directory configuration/
- The query executed.
Thanks.
Bertan ARI
This posting is provided "AS IS" with no warranties, and confers no rights.
"vinod nauriyal via droptable.com" <forum@.droptable.com> wrote in message
news:502AABA931FA0@.droptable.com...
> Hi Monica,
> Thanks for you response .When i search the log it is giving me
> Object_not_a_collection kind of error .
> stype=www&vtype=vdir&title=IISAdmin&spath=&dpath=I IS%
> 3A//localhost/W3SVC/2/ROOT/IISAdmin|17|800a01c3|Object_not_a_collection
> 500
> Mozilla/4.0+(compatible;+MSIE+6.0;+Windows+NT+5.0)
> 2005-06-20 14:51:43 127.0.0.1 IN002\ic002260 127.0.0.1 6198 GET
> /iistat.asp
> thisState= 200 Mozilla/4.0+(compatible;+MSIE+6.0;+Windows+NT+5.0)
> Even in my simple XSD if i include the tag <SEQUENCE> it gives me this
> error
> Regards
> Vinod Nauriyal
> --
> Message posted via http://www.droptable.com