Generating XSD schema from an sql server table

I am using XSD schemas in .net but have to generate them manually.
Is it possible to automatically get the validation information from a table in SQL server?
Posted using Wimdows.net NntpNews Component -
Post Made from http://www.SqlJunkies.com/newsgroups Our newsgroup engine supports Post Alerts, Ratings, and Searching.
<chris4england> wrote in message
news:%23Xb8L4NlEHA.3520@.tk2msftngp13.phx.gbl...
>I am using XSD schemas in .net but have to generate them manually.
> Is it possible to automatically get the validation information from a
> table in SQL server?
You could probably write a stored procedure that generated XSD from the
sysobjects table, but it would be pretty complex and might not contain all
the validation you need.
This would be the only way that I know of. Part of the problem is that XSD's
generally contain information that isn't in SQL Server.
Bryant
|||In SQL Server 2005, you will be able to infer an XSD for the RAW and AUTO
modes in FOR XML.
E.g.,
select top 0 * from table for xml auto, xmlschema
In SQL Server 2000, you can generate an XDR schema in the following way
select top 0 * from table for xml auto, xmldata
and then use one of the XDR->XSD tools to generate the XSD from it.
Note that you probably still want to then edit the schema to add your own
constraints.
Best regards
Michael
"Bryant Likes" <bryant@.suespammers.org> wrote in message
news:u9ykRxblEHA.3104@.TK2MSFTNGP14.phx.gbl...
> <chris4england> wrote in message
> news:%23Xb8L4NlEHA.3520@.tk2msftngp13.phx.gbl...
> You could probably write a stored procedure that generated XSD from the
> sysobjects table, but it would be pretty complex and might not contain all
> the validation you need.
> This would be the only way that I know of. Part of the problem is that
> XSD's generally contain information that isn't in SQL Server.
> --
> Bryant
>

generating XML to validated against the schema

hi,
I have a sample xml data and sample xsd as follows: the xsd is really big
and runs into 4 pages. I need to stored data into the data base
and generate xml that validates against this schema.
the sqlxml bulk load and view mapper fails to recognize the xsd fine.
Can some one point me to the right direction .
Infact storing is working but generating the xml
using EXPLICIT option is really a pain.
Regards,
Balajee
<?xml version="1.0" encoding="utf-8" ?>
<xs:schema id="StrategicPerformanceContract"
targetNamespace="http://tempuri.org/StrategicPerformanceContract.xsd"
elementFormDefault="qualified"
xmlns="http://tempuri.org/StrategicPerformanceContract.xsd"
xmlns:mstns="http://tempuri.org/StrategicPerformanceContract.xsd"
xmlns:xs="http://www.w3.org/2001/XMLSchema">
<xs:annotation>
<xs:documentation>Strategic Performance Contract</xs:documentation>
</xs:annotation>
<xs:element name="root" type="contractType" />
<xs:complexType name="contractType">
<xs:sequence>
<xs:element name="transactionHeader" type="transactionHeaderType" />
<xs:element name="documentHeader" type="documentHeaderType" />
<xs:element name="strategicDirection" type="strategicDirectionType" />
<xs:element name="strategicChoices" type="strategicChoicesType" />
<xs:element name="riskManagement" type="riskManagementType" />
<xs:element name="performanceContract" type="performanceContractType" />
</xs:sequence>
</xs:complexType>
<xs:complexType name="transactionHeaderType">
<xs:sequence>
<xs:element name="userName" type="xs:string" />
</xs:sequence>
<xs:attribute name="gid" type="xs:unsignedLong"/>
</xs:complexType>
<xs:complexType name="documentHeaderType">
<xs:sequence>
<xs:element name="contractType" type="xs:ID" />
<xs:element name="abbr" type="xs:string" />
<xs:element name="leftTitle1" type="xs:string" />
<xs:element name="leftTitle2" type="xs:string" />
<xs:element name="rightTitle1" type="xs:string" />
<xs:element name="asofDate" type="xs:string" />
</xs:sequence>
<xs:attribute name="gid" type="xs:unsignedLong"/>
</xs:complexType>
<xs:complexType name="strategicDirectionType">
<xs:sequence>
<xs:element name="header" type="headerType" />
<xs:element name="strategicObjective" type="strategicObjectiveType"
minOccurs="0" maxOccurs="unbounded" />
<xs:element name="footnotes" type="xs:string" />
</xs:sequence>
</xs:complexType>
<xs:complexType name="headerType">
<xs:sequence>
<xs:element name="vpuMission" type="xs:string" />
<xs:element name="strategicContext" type="xs:string" />
</xs:sequence>
</xs:complexType>
<xs:complexType name="strategicObjectiveType">
<xs:sequence>
<xs:element name="objective" type="objectiveType" />
<xs:element name="subObjective" type="objectiveType" minOccurs="0"
maxOccurs="unbounded" />
</xs:sequence>
</xs:complexType>
What are the problems you get when using the Schema and adding mapping
annotations?
Thanks
Michael
"balajee" <balajee@.discussions.microsoft.com> wrote in message
news:65C72C0A-D8C6-4CA3-ADD4-CBACEB16F58F@.microsoft.com...
> hi,
> I have a sample xml data and sample xsd as follows: the xsd is really big
> and runs into 4 pages. I need to stored data into the data base
> and generate xml that validates against this schema.
> the sqlxml bulk load and view mapper fails to recognize the xsd fine.
> Can some one point me to the right direction .
> Infact storing is working but generating the xml
> using EXPLICIT option is really a pain.
> Regards,
> Balajee
>
> <?xml version="1.0" encoding="utf-8" ?>
> <xs:schema id="StrategicPerformanceContract"
> targetNamespace="http://tempuri.org/StrategicPerformanceContract.xsd"
> elementFormDefault="qualified"
> xmlns="http://tempuri.org/StrategicPerformanceContract.xsd"
> xmlns:mstns="http://tempuri.org/StrategicPerformanceContract.xsd"
> xmlns:xs="http://www.w3.org/2001/XMLSchema">
> <xs:annotation>
> <xs:documentation>Strategic Performance Contract</xs:documentation>
> </xs:annotation>
> <xs:element name="root" type="contractType" />
> <xs:complexType name="contractType">
> <xs:sequence>
> <xs:element name="transactionHeader" type="transactionHeaderType" />
> <xs:element name="documentHeader" type="documentHeaderType" />
> <xs:element name="strategicDirection" type="strategicDirectionType" />
> <xs:element name="strategicChoices" type="strategicChoicesType" />
> <xs:element name="riskManagement" type="riskManagementType" />
> <xs:element name="performanceContract" type="performanceContractType" />
> </xs:sequence>
> </xs:complexType>
> <xs:complexType name="transactionHeaderType">
> <xs:sequence>
> <xs:element name="userName" type="xs:string" />
> </xs:sequence>
> <xs:attribute name="gid" type="xs:unsignedLong"/>
> </xs:complexType>
> <xs:complexType name="documentHeaderType">
> <xs:sequence>
> <xs:element name="contractType" type="xs:ID" />
> <xs:element name="abbr" type="xs:string" />
> <xs:element name="leftTitle1" type="xs:string" />
> <xs:element name="leftTitle2" type="xs:string" />
> <xs:element name="rightTitle1" type="xs:string" />
> <xs:element name="asofDate" type="xs:string" />
> </xs:sequence>
> <xs:attribute name="gid" type="xs:unsignedLong"/>
> </xs:complexType>
> <xs:complexType name="strategicDirectionType">
> <xs:sequence>
> <xs:element name="header" type="headerType" />
> <xs:element name="strategicObjective" type="strategicObjectiveType"
> minOccurs="0" maxOccurs="unbounded" />
> <xs:element name="footnotes" type="xs:string" />
> </xs:sequence>
> </xs:complexType>
> <xs:complexType name="headerType">
> <xs:sequence>
> <xs:element name="vpuMission" type="xs:string" />
> <xs:element name="strategicContext" type="xs:string" />
> </xs:sequence>
> </xs:complexType>
> <xs:complexType name="strategicObjectiveType">
> <xs:sequence>
> <xs:element name="objective" type="objectiveType" />
> <xs:element name="subObjective" type="objectiveType" minOccurs="0"
> maxOccurs="unbounded" />
> </xs:sequence>
> </xs:complexType>
|||Michael,
I am trying to map the schema using sql viewmapper and
it gives me an error :
Error while parsing
:Incorrect definition for the root element in schema.
Line 7 Position 63
xmlns:xs="http://www.w3.org/2001/XMLSchema">
regards,
Balajee
"Michael Rys [MSFT]" wrote:

> What are the problems you get when using the Schema and adding mapping
> annotations?
> Thanks
> Michael
> "balajee" <balajee@.discussions.microsoft.com> wrote in message
> news:65C72C0A-D8C6-4CA3-ADD4-CBACEB16F58F@.microsoft.com...
>
>
|||Unfortunately, the SQL Viewmapper only works for the older Microsoft schema
language known as XDR and not with the W3C XML Schema language (that you
seem to be using). This unfortunately means that you either need a
third-party tool vendor or notepad.
Best regards
Michael
"balajee" <balajee@.discussions.microsoft.com> wrote in message
news:0EA7E4AD-F94D-4021-8A22-FE327F1B786B@.microsoft.com...[vbcol=seagreen]
> Michael,
> I am trying to map the schema using sql viewmapper and
> it gives me an error :
> Error while parsing
> :Incorrect definition for the root element in schema.
> Line 7 Position 63
> xmlns:xs="http://www.w3.org/2001/XMLSchema">
>
> regards,
> Balajee
>
> "Michael Rys [MSFT]" wrote:
sql

generating xml from sql server

Hi,
I have generated classes form an XSD (where complex), currently I am
reading the database and filling he classes and serializinf the classes to
get the xml. The problem is there are a lot of records (in the range from
10000 +) and looping the dataset to fill will take a long time. Is there any
other approach to do this.
Can i use SQLXML to generate the xml is this a good idea?
Any thought are welcome.
ThanksUsing the SQLXMLBulLoad utility seems like a good solution for your
scenario. See BOL or MSDN for more information on this topic. I see a lot of
newsgroup postings on SQLXMLBulLoad - that can also give you some idea.
Thank you,
Shankar
This posting is provided "AS IS" with no warranties, and confers no rights.
"Error while executing SP" <ErrorwhileexecutingSP@.discussions.microsoft.com>
wrote in message news:092A4CCB-F3D0-4860-8DF5-666B048A925B@.microsoft.com...
> Hi,
> I have generated classes form an XSD (where complex), currently I am
> reading the database and filling he classes and serializinf the classes to
> get the xml. The problem is there are a lot of records (in the range from
> 10000 +) and looping the dataset to fill will take a long time. Is there
> any
> other approach to do this.
> Can i use SQLXML to generate the xml is this a good idea?
> Any thought are welcome.
> Thanks
>

generating xml from sql server

Hi,
I have generated classes form an XSD (where complex), currently I am
reading the database and filling he classes and serializinf the classes to
get the xml. The problem is there are a lot of records (in the range from
10000 +) and looping the dataset to fill will take a long time. Is there any
other approach to do this.
Can i use SQLXML to generate the xml is this a good idea?
Any thought are welcome.
Thanks
Using the SQLXMLBulLoad utility seems like a good solution for your
scenario. See BOL or MSDN for more information on this topic. I see a lot of
newsgroup postings on SQLXMLBulLoad - that can also give you some idea.
Thank you,
Shankar
This posting is provided "AS IS" with no warranties, and confers no rights.
"Error while executing SP" <ErrorwhileexecutingSP@.discussions.microsoft.com >
wrote in message news:092A4CCB-F3D0-4860-8DF5-666B048A925B@.microsoft.com...
> Hi,
> I have generated classes form an XSD (where complex), currently I am
> reading the database and filling he classes and serializinf the classes to
> get the xml. The problem is there are a lot of records (in the range from
> 10000 +) and looping the dataset to fill will take a long time. Is there
> any
> other approach to do this.
> Can i use SQLXML to generate the xml is this a good idea?
> Any thought are welcome.
> Thanks
>
sql

Generating table from xsd file in SQL Server 2005

Hi there,

I would like to create a table in SQL Server 2005 from an xsd file. Is this possible? From what I've read it seems possible but I haven't found the way to do it yet...

Thanks!

Craig

Hello,

You can use SQLXMLBulkload object and add annotations to the xsd schema file to map elements/attributes to tables/columns.

Read more about this here: http://msdn2.microsoft.com/en-us/library/ms171993(SQL.90).aspx.

You have to set SchemaGen property to true and Bulkload property to false.

Hope this helps.

Regards,

Monica Frintu

Generate XSD from XML

Hi,

I'm new to SQL2005 and even newer to .XML. I've been using SSIS to bring in some .XML files using the XML datasource in SSIS. I use the "generate XSD" button to read the XML file and build the XSD for me. This works great but it seems that the "generate" button always ends up normalizing out every table attribute into seperate tables, even 1:1 attributes. A 3 table schema with 10 attributes per table ends up coming in as a 30 table schema. In my case it ended up as a 104 table schema. Not knowing a whole lot about .XML I'm wondering if there are tools out there that can be used to gen an XSD off of an XML file but do it in a smarter manner such as keeping all the 1:1 attribute in a single table? I Googled that and found no utilities like that.

Any help is appreciated.

G

After a week I can answer my own question. Inside of Visual Studio there is a utility to generate "intelligent" XSD files. It took my 104 table schema and boiled it down to 3 tables.|||

Hello PM2k - we have a similar need. Could you please point out how to locate the utility you mentioned in Visual Studio that generates the "intelligent" xsd? Any additional background / info you can provide would be most appreciated!

Thanks.

Generate XSD from XML

Hi,

I'm new to SQL2005 and even newer to .XML. I've been using SSIS to bring in some .XML files using the XML datasource in SSIS. I use the "generate XSD" button to read the XML file and build the XSD for me. This works great but it seems that the "generate" button always ends up normalizing out every table attribute into seperate tables, even 1:1 attributes. A 3 table schema with 10 attributes per table ends up coming in as a 30 table schema. In my case it ended up as a 104 table schema. Not knowing a whole lot about .XML I'm wondering if there are tools out there that can be used to gen an XSD off of an XML file but do it in a smarter manner such as keeping all the 1:1 attribute in a single table? I Googled that and found no utilities like that.

Any help is appreciated.

G

After a week I can answer my own question. Inside of Visual Studio there is a utility to generate "intelligent" XSD files. It took my 104 table schema and boiled it down to 3 tables.|||

Hello PM2k - we have a similar need. Could you please point out how to locate the utility you mentioned in Visual Studio that generates the "intelligent" xsd? Any additional background / info you can provide would be most appreciated!

Thanks.

Generate XSD from XML

Hi,

I'm new to SQL2005 and even newer to .XML. I've been using SSIS to bring in some .XML files using the XML datasource in SSIS. I use the "generate XSD" button to read the XML file and build the XSD for me. This works great but it seems that the "generate" button always ends up normalizing out every table attribute into seperate tables, even 1:1 attributes. A 3 table schema with 10 attributes per table ends up coming in as a 30 table schema. In my case it ended up as a 104 table schema. Not knowing a whole lot about .XML I'm wondering if there are tools out there that can be used to gen an XSD off of an XML file but do it in a smarter manner such as keeping all the 1:1 attribute in a single table? I Googled that and found no utilities like that.

Any help is appreciated.

G

After a week I can answer my own question. Inside of Visual Studio there is a utility to generate "intelligent" XSD files. It took my 104 table schema and boiled it down to 3 tables.|||

Hello PM2k - we have a similar need. Could you please point out how to locate the utility you mentioned in Visual Studio that generates the "intelligent" xsd? Any additional background / info you can provide would be most appreciated!

Thanks.

Generate SQL Server tables from an XSD

Hi, I need to generate tables from an XSD document. Does anyone knows about
a way to do this? ThanksHi,
You can use Xml Bulkload component to generate the tables from a supplied
annotated schema file.You have to set SchemaGen property to True.
Check out paragraph E from the below link for an example on how to do it:
http://msdn.microsoft.com/library/d...>
h_SQLXML.asp
Best regards,
Monica Frintu
"Daniele Romagnoli" wrote:

> Hi, I need to generate tables from an XSD document. Does anyone knows abou
t
> a way to do this? Thanks
>
>