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 XMLdata based on the schema

How to generate XMLdata based on the schema in SQL SERVER 2005?

Are there any features in SQLServer2005 to achieve this using Xml datatype ,SQLXML 4.0?

Please suggest me in this regard.

Thanks

vaishu

SQL Server has the FOR XML xml construction modes which you can use to generate XML from your relational schema. Examples of this support are here:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsql90/html/forxml2k5.asp

In addition SQLXML 4.0 has mappings from XSD schema to relational schema, in case you need to map between these two types for schemas. An introduction to these can be found here:

http://msdn2.microsoft.com/en-us/library/ms171870(SQL.90).aspx

|||

Thanks for the reply

you can close treat this request as closed.

vaishu

Generating XMLdata based on the schema

How to generate XMLdata based on the schema in SQL SERVER 2005?

Are there any features in SQLServer2005 to achieve this using Xml datatype ,SQLXML 4.0?

Please suggest me in this regard.

Thanks

vaishu

SQL Server has the FOR XML xml construction modes which you can use to generate XML from your relational schema. Examples of this support are here:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsql90/html/forxml2k5.asp

In addition SQLXML 4.0 has mappings from XSD schema to relational schema, in case you need to map between these two types for schemas. An introduction to these can be found here:

http://msdn2.microsoft.com/en-us/library/ms171870(SQL.90).aspx

|||

Thanks for the reply

you can close treat this request as closed.

vaishu

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 Schema from db tables - including field lengths

I'm working on a C# project that essentially features a dump of the
database (on SQL Server 2005) into an XML file, with schema.
Basically we want the users to be able to take this data anywhere, as
easily as is possible. I initially selected the information "For XML
Auto" etc. etc. but found that when I tested simple imports of that
XML with the likes of Access and Excel, those programs didn't take
well to the schema at all.
Switching gears, I queried the databases normally and did the XML
conversion in C# using the dataset.writeXML function with schema,
which generated schema that made Access and Excel much happier. The
only thing is that Access, upon creating tables based on the schema,
took the string fields to be text, rather than memo. (i.e.
varchar(255) instead of text) ... likewise, SQL Server, when
wrestling to create tables based on the schema, behaves similarly.
Mind you, when the tables are created beforehand, and just populated
with the XML data, that's fine, it works great.
I do realize that's how it's supposed to work, but I'm being asked to
generate a schema that includes maxLength for varchar fields -- even
though I doubt any program that would be importing this data would
even be able to read that from the schema and use it appropriately.
I'm hoping someone here can tell me there's a nicer way of doing that
than SELECTing FOR XML RAW and drawing up an XSLT.
Takers?Did you use the xmlschema directive on FOR XML AUTO or RAW?
Best regards
Michael
"Matthew Dunphy" <leviathant@.gmail.com> wrote in message
news:1172246553.987853.109810@.j27g2000cwj.googlegroups.com...
> I'm working on a C# project that essentially features a dump of the
> database (on SQL Server 2005) into an XML file, with schema.
> Basically we want the users to be able to take this data anywhere, as
> easily as is possible. I initially selected the information "For XML
> Auto" etc. etc. but found that when I tested simple imports of that
> XML with the likes of Access and Excel, those programs didn't take
> well to the schema at all.
> Switching gears, I queried the databases normally and did the XML
> conversion in C# using the dataset.writeXML function with schema,
> which generated schema that made Access and Excel much happier. The
> only thing is that Access, upon creating tables based on the schema,
> took the string fields to be text, rather than memo. (i.e.
> varchar(255) instead of text) ... likewise, SQL Server, when
> wrestling to create tables based on the schema, behaves similarly.
> Mind you, when the tables are created beforehand, and just populated
> with the XML data, that's fine, it works great.
> I do realize that's how it's supposed to work, but I'm being asked to
> generate a schema that includes maxLength for varchar fields -- even
> though I doubt any program that would be importing this data would
> even be able to read that from the schema and use it appropriately.
> I'm hoping someone here can tell me there's a nicer way of doing that
> than SELECTing FOR XML RAW and drawing up an XSLT.
> Takers?
>|||Both -- when I used AUTO, it worked great except that it doesn't
output the maxlength for the varchar fields. Everything else it does
is kind of magical.
When I use RAW, it outputs each column, but again, nothing about the
(3000) for a row that is varchar(3000).
To be specific, the select statement that comes closest to what I want
looks basically like this:
SELECT *
FROM table
WHERE id=@.id
FOR XML AUTO, ELEMENTS, XMLDATA
(I do this for about a dozen tables)
In the resulting schema, the ElementType nodes have attributes for
name, content, model, and dt:type... is there some way to also specify
maxLength, based off the table schema in SQL server, so that I can do
a simple query like this -- or do I just have to bite the bullet and
write the schemas manually?
Thanks!
--Matt Dunphy
On Feb 24, 1:42 am, "Michael Rys [MSFT]" <m...@.online.microsoft.com>
wrote:
> Did you use the xmlschema directive on FOR XML AUTO or RAW?
> Best regards
> Michael
> "Matthew Dunphy" <leviath...@.gmail.com> wrote in message
> news:1172246553.987853.109810@.j27g2000cwj.googlegroups.com...
>
>
>
>|||Actually... never mind that last post, I think you've pointed me in
the right direction. (That's what I get for posting first thing in
the morning!) Thanks for your help.
On Feb 26, 9:14 am, "Matthew Dunphy" <leviath...@.gmail.com> wrote:
> Both -- when I used AUTO, it worked great except that it doesn't
> output the maxlength for the varchar fields. Everything else it does
> is kind of magical.
> When I use RAW, it outputs each column, but again, nothing about the
> (3000) for a row that is varchar(3000).
> To be specific, the select statement that comes closest to what I want
> looks basically like this:
> SELECT *
> FROM table
> WHERE id=@.id
> FOR XML AUTO, ELEMENTS, XMLDATA
> (I do this for about a dozen tables)
> In the resulting schema, the ElementType nodes have attributes for
> name, content, model, and dt:type... is there some way to also specify
> maxLength, based off the table schema in SQL server, so that I can do
> a simple query like this -- or do I just have to bite the bullet and
> write the schemas manually?
> Thanks!
> --Matt Dunphy
>

Generating XML Schema from db tables - including field lengths

I'm working on a C# project that essentially features a dump of the
database (on SQL Server 2005) into an XML file, with schema.
Basically we want the users to be able to take this data anywhere, as
easily as is possible. I initially selected the information "For XML
Auto" etc. etc. but found that when I tested simple imports of that
XML with the likes of Access and Excel, those programs didn't take
well to the schema at all.
Switching gears, I queried the databases normally and did the XML
conversion in C# using the dataset.writeXML function with schema,
which generated schema that made Access and Excel much happier. The
only thing is that Access, upon creating tables based on the schema,
took the string fields to be text, rather than memo. (i.e.
varchar(255) instead of text) ... likewise, SQL Server, when
wrestling to create tables based on the schema, behaves similarly.
Mind you, when the tables are created beforehand, and just populated
with the XML data, that's fine, it works great.
I do realize that's how it's supposed to work, but I'm being asked to
generate a schema that includes maxLength for varchar fields -- even
though I doubt any program that would be importing this data would
even be able to read that from the schema and use it appropriately.
I'm hoping someone here can tell me there's a nicer way of doing that
than SELECTing FOR XML RAW and drawing up an XSLT.
Takers?
Did you use the xmlschema directive on FOR XML AUTO or RAW?
Best regards
Michael
"Matthew Dunphy" <leviathant@.gmail.com> wrote in message
news:1172246553.987853.109810@.j27g2000cwj.googlegr oups.com...
> I'm working on a C# project that essentially features a dump of the
> database (on SQL Server 2005) into an XML file, with schema.
> Basically we want the users to be able to take this data anywhere, as
> easily as is possible. I initially selected the information "For XML
> Auto" etc. etc. but found that when I tested simple imports of that
> XML with the likes of Access and Excel, those programs didn't take
> well to the schema at all.
> Switching gears, I queried the databases normally and did the XML
> conversion in C# using the dataset.writeXML function with schema,
> which generated schema that made Access and Excel much happier. The
> only thing is that Access, upon creating tables based on the schema,
> took the string fields to be text, rather than memo. (i.e.
> varchar(255) instead of text) ... likewise, SQL Server, when
> wrestling to create tables based on the schema, behaves similarly.
> Mind you, when the tables are created beforehand, and just populated
> with the XML data, that's fine, it works great.
> I do realize that's how it's supposed to work, but I'm being asked to
> generate a schema that includes maxLength for varchar fields -- even
> though I doubt any program that would be importing this data would
> even be able to read that from the schema and use it appropriately.
> I'm hoping someone here can tell me there's a nicer way of doing that
> than SELECTing FOR XML RAW and drawing up an XSLT.
> Takers?
>
|||Both -- when I used AUTO, it worked great except that it doesn't
output the maxlength for the varchar fields. Everything else it does
is kind of magical.
When I use RAW, it outputs each column, but again, nothing about the
(3000) for a row that is varchar(3000).
To be specific, the select statement that comes closest to what I want
looks basically like this:
SELECT *
FROM table
WHERE id=@.id
FOR XML AUTO, ELEMENTS, XMLDATA
(I do this for about a dozen tables)
In the resulting schema, the ElementType nodes have attributes for
name, content, model, and dt:type... is there some way to also specify
maxLength, based off the table schema in SQL server, so that I can do
a simple query like this -- or do I just have to bite the bullet and
write the schemas manually?
Thanks!
--Matt Dunphy
On Feb 24, 1:42 am, "Michael Rys [MSFT]" <m...@.online.microsoft.com>
wrote:[vbcol=seagreen]
> Did you use the xmlschema directive on FOR XML AUTO or RAW?
> Best regards
> Michael
> "Matthew Dunphy" <leviath...@.gmail.com> wrote in message
> news:1172246553.987853.109810@.j27g2000cwj.googlegr oups.com...
>
>
|||Actually... never mind that last post, I think you've pointed me in
the right direction. (That's what I get for posting first thing in
the morning!) Thanks for your help.
On Feb 26, 9:14 am, "Matthew Dunphy" <leviath...@.gmail.com> wrote:
> Both -- when I used AUTO, it worked great except that it doesn't
> output the maxlength for the varchar fields. Everything else it does
> is kind of magical.
> When I use RAW, it outputs each column, but again, nothing about the
> (3000) for a row that is varchar(3000).
> To be specific, the select statement that comes closest to what I want
> looks basically like this:
> SELECT *
> FROM table
> WHERE id=@.id
> FOR XML AUTO, ELEMENTS, XMLDATA
> (I do this for about a dozen tables)
> In the resulting schema, the ElementType nodes have attributes for
> name, content, model, and dt:type... is there some way to also specify
> maxLength, based off the table schema in SQL server, so that I can do
> a simple query like this -- or do I just have to bite the bullet and
> write the schemas manually?
> Thanks!
> --Matt Dunphy
>

Generating XML Schema for SQL Server Table

how can persist schema for a sql server table into an xml file from .net application?

thanks

If you already loaded the structure in a dataset, you can just use the WriteXMLSchema Method on the dataset object.

http://msdn2.microsoft.com/en-us/library/system.data.dataset.writexmlschema.aspx

Jens K. Suessmeyer.

http://www.sqlserver2005.de

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 XML from SQL

Hi all,

Can anybody advice me how can i go about creating an XML from SQL. The need arised when our jobs which create Xmls from the select statement using vbscript hang in the executing state because the file gets a sharing voilation so we have to change the generated filename on the vbscript code frquently so i thought about can i generate it directly from SQL......

Any ideas ?

this solution would be very helpful for me

Thanks in ADVANCE

Jacx

This may help you

XML support in SQL Server 2005 (just in case you're not familiar with what is available):
http://msdn2.microsoft.com/en-us/library/ms345117.aspx#sql2k5xml_topic3

Constructing XML using SQL:
http://msdn2.microsoft.com/en-us/ms178107.aspx

Generating XML data based on the Schema

Hi,

i have the following requirement

Can a stored procedure in SQL Server 2005 generate XML data based on the schema, We don't prefer to manually build an xml string inside the stored proc?

Is there any SQL Server 2005 feature to do it if possible?

i appreciate your help.

Thanks,

Vaishu

Can a stored procedure in SQL Server 2005 generate XML data based on the schema?

Yes...

Create Proc dbo.GenXMLWithSchemaValidation
As
Begin
Set Nocount On

DECLARE @.x XML (HumanResources.HRResumeSchemaCollection)
SET @.x = (
SELECT ContactID,
FirstName,
LastName,
AdditionalContactInfo.query('
declare namespace aci="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ContactInfo";
declare namespace act="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ContactTypes";
//act:telephoneNumber/act:number') as MorePhoneNumbers
FROM Person.Contact
FOR XML AUTO, TYPE)
SELECT @.x
Set Nocount Off
End

Hope this helps,

Derek

|||

Thanks Derek

i appreciate your help.

Vaishu

Generating XML data based on the Schema

Hi,

i have the following requirement

Can a stored procedure in SQL Server 2005 generate XML data based on the schema, We don't prefer to manually build an xml string inside the stored proc?

Is there any SQL Server 2005 feature to do it if possible?

i appreciate your help.

Thanks,

Vaishu

Can a stored procedure in SQL Server 2005 generate XML data based on the schema?

Yes...

Create Proc dbo.GenXMLWithSchemaValidation
As
Begin
Set Nocount On

DECLARE @.x XML (HumanResources.HRResumeSchemaCollection)
SET @.x = (
SELECT ContactID,
FirstName,
LastName,
AdditionalContactInfo.query('
declare namespace aci="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ContactInfo";
declare namespace act="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ContactTypes";
//act:telephoneNumber/act:number') as MorePhoneNumbers
FROM Person.Contact
FOR XML AUTO, TYPE)
SELECT @.x
Set Nocount Off
End

Hope this helps,

Derek

|||

Thanks Derek

i appreciate your help.

Vaishu

Generating XML based on XMLSchema in a SQLSERVER2005 Stored procedure

Can a stored procedure in SQLSERVER 2005 generate XMLdata based on the schema?

Are there any features in SQLServer2005 to achieve this ?

Please suggest me in this regard.

Thanks

vaishu

Yes this is possible. Michael Rys' article about FOR XML support and the Xml datatype shows examples of this:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsql90/html/forxml2k5.asp

|||

thanks for the reply.

vaishu

|||

Can sqlserver 2005 generete XML schema for the whole database ?

Can anyone suggest us on this.

thanks

oak-net

Generating XML based on XMLSchema in a SQLSERVER2005 Stored procedure

Can a stored procedure in SQLSERVER 2005 generate XMLdata based on the schema?

Are there any features in SQLServer2005 to achieve this ?

Please suggest me in this regard.

Thanks

vaishu

Yes this is possible. Michael Rys' article about FOR XML support and the Xml datatype shows examples of this:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsql90/html/forxml2k5.asp

|||

thanks for the reply.

vaishu

|||

Can sqlserver 2005 generete XML schema for the whole database ?

Can anyone suggest us on this.

thanks

oak-net

sql

Generating XML based on Schema

Hi,

i need Info regarding this

Can a stored procedure in SQL Server 2005 generate XML data based on the schema, We don't prefer to manually build an xml string inside the stored proc?

Is there any SQL Server 2005 feature to do it if possible?

Can i find any article on how to do this.

Thanks,

Vaishu

How about using "FOR XML AUTO" in your select!

select * from tbl1 FOR XML AUTO

Generating wrapper code for SQL Server Stored Procedure

Hi

How Can I Generating wrapper code for SQL Server Stored Procedure ??

If You will Go To The Following link you will see an example for Generating wrapper code for Oracle Database .. And Also the author say there is built in tool for Generating wrapper code for SQL Server
http://www.codeproject.com/vb/net/OracleSPWrapper.asp
my question .. where is this tools ?

and thanks with my regarding

FraasYou are probably talking about the Data Adapter wizard in the design view. See this walkthrough:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/vbcon/html/vbwlkWalkthroughUsingDataGridWebControlToReadWriteData.asp

Generating various statistics from data in MSSQL7

Sorry about the huge post, but I think this is the amount of
information necessary for someone to help me with a good answer.

I'm writing a statistical analysis program in ASP.net and MSSQL7 that
analyzes data that I've collected from my business's webpage and the
hits it's collecting from the various pay-per-click (PPC) engines.
I've arrived at problems writing a SQL call to generate certain
statistics.

Whenever someone enters our site from one of the PPC search engines, I
write out a row to the Hits table. In that table are the following
columns:
HitID - the Unique ID assigned to each hit that comes into the site
Keyword - the keyword the user searched on when he or she came to the
site
SearchEngine - the PPC engine the user came from
Source - this is pretty much always 'PPC'...if we were to do other
things, like a newsletter, then this would be different.
TimeArrived - the date and time the user arrived at the website. I
have no idea why I didn't call it "datearrived," since I use "date"
and not "time" pretty much everywhere else...
(I don't think the rest are important, but they might be, so I'll
include them for completeness's sake)
Referring URL - the URL the user came from
Referring Website - the string between the 'http://' and the first '/'
in the URL. I know it's redundant information, but when I designed
this part, I didn't know how to parse it out afterwards, so I just
figured I'd duplicate it.
Page Visited - the page the user first arrived at

When a person comes to the site, I also write out a session cookie
containing the user's hitID. If the person fills out an enrollment
form (a process which we refer to as "responding"), I attach that
session ID to the form. The response form (and thus the responses
table) is long; these are the important fields:
id - a unique ID for each response
date - the date and time of the response
status - a varchar field containing a status code. I would have made
it a number, but I wanted it to be viewable from looking at the raw
database.
hitid - the HitID of the user, taken from the session cookie. If there
is no session cookie (for whatever reason), the HidID is written out
as 0. While it wouldn't occur often, I can't guarantee that there will
never be more than one response record attached to a singular hitid.

Later, some of the responses turn into "confirmations", which means
that they've actually ordered from us, not just filled out the form.
This usually happens about three or four days after the initial
response. When this happens, the status of the response is changed to
a phrase containing the word "confirm" in it (there are a few of them,
but they all contain that word).

So now that we've collected all this marketing intel., I need to
analyze it.

I've written a parser that takes reports from various pay-per-click
companies and puts them into a table called PPC. Information in this
column is written out as one record per search engine per keyword per
day. The schema is as follows:
id - a unique ID for the record in the table
date - the date to which the information in the record applies
searchengine - the PPC engine to which the information applies
keyword - the keyword to which the information applies
clicks - the number of clicks on the applicable keyword on the
applicable search engine on the applicable day.
impressions - same as clicks, but for impressions
cpc - the cost per click on the applicable keyword ...
avgpos - (I don't always have a value for this field) The average
position that the keyword was shown in for the applicable keyword ...

With this data in, the last step is actually analyzing the three
tables for useful statistics on the various keywords, search engines,
and time frames. That's the step I've been trying to complete.

So what I need is a SQL call that I can run that generates a table
with the following information:
SearchEngine
Keyword
Cost / Click - When calculating the CPC, I can't just take an average
of all the records. I need to calculate the total amount spent per day
(clicks * cpc), add that up for every day, and then divide that by the
number of total clicks. Just doing an average doesn't take into
account the fact that some days we'll get more clicks than others.
Total Spent - # Clicks * CPC
#Responses - counting the number of records in the responses table
#Confirms - counting the number of records in the responses table with
"confirm" in their status
Total Spent / #Responses
Total Spent / #Confirms

Oh yeah, and I want to be able to order by any four of the fields in
any order, narrow my selection to only those keywords that either are
or contain a user-specified string, further narrow my selection to
only those records that fit other user-specified criteria for any of
the columns in the table I'm generating, and select only the top x
records (where x is a user-specified number). I already have
user-controls that output the SQL for all of these things, but I need
to have places in which I may put that SQL in my call.

After many trials and tribulations, I've come up with the following
SQL call. Right now, its output for nearly every row is incorrect, I
think in a large part due to the fact that the method that I'm using
to generate the number of clicks is yielding incorrect values.

If you'd like to help me and you think that modifying the following
call is easier than writing a whole new one, be my guest; if you'd
prefer to write a new one, I'm game for that, too. I'm just concerned
with its working right now, and any help you can give me is greatly
appreciated.

Anyway, here's the call:
/*sp_dboption @.dbname='NDP', @.optname='Select Into', @.optvalue=true;*/
/*Running the above might be necessary to get the "Select Into"s to
work*/

Drop table ResponsesPPC
Drop table ConfirmPPC
Drop table TempPPC

SELECT Responses.[ID] as [ID], Responses.Status, PPC.SearchEngine,
PPC.Keyword
Into ResponsesPPC
FROM Responses, PPC
WHERE Responses.HitID IN
(SELECT Hits.HitID
FROM Hits
WHERE Hits.SearchEngine = PPC.SearchEngine
AND Hits.Keyword = PPC.Keyword)

SELECT ID, Status, SearchEngine, Keyword
Into ConfirmPPC
FROM ResponsesPPC
WHERE Status LIKE "%confirm%"
Order by SearchEngine, Keyword

SELECT PPC.SearchEngine, PPC.Keyword,
SUM(PPC.Clicks), /*I noticed that this
column gives me incorrect values
(I don't need it in my final report, but it's useful for debugging).
For some keywords, it gives me huge numbers
(e.g. 265 clicks on one word that got ~10 clicks /day over five days),
and for others, it doesn't give me enough. I think this is a major
part
of what's throwing off the rest of the statistics*/
Case SUM(PPC.Clicks) WHEN 0 THEN 0 ELSE
SUM(PPC.clicks * PPC.cpc) / SUM(PPC.Clicks) END as CPC,
SUM(PPC.clicks * PPC.cpc) AS TotalCost,
count(ResponsesPPC.ID) As NumResponses,
Count(ConfirmPPC.ID) As Confirms,
(Case Count(ResponsesPPC.ID) WHEN 0 THEN 0 ELSE
SUM(PPC.clicks * PPC.cpc) / count(ResponsesPPC.ID) END) AS
CostPerResponse,
(Case Count(ConfirmPPC.ID) WHEN 0 THEN 0 ELSE
SUM(PPC.clicks * PPC.cpc) / count(ConfirmPPC.ID) END) As
CostPerConfirm
FROM (PPC LEFT JOIN ResponsesPPC ON PPC.SearchEngine =
ResponsesPPC.SearchEngine
AND PPC.Keyword = ResponsesPPC.Keyword)
LEFT JOIN ConfirmPPC ON PPC.SearchEngine = ConfirmPPC.SearchEngine
AND PPC.Keyword = ConfirmPPC.Keyword
GROUP BY PPC.SearchEngine, PPC.Keyword
Order by PPC.keyword desc

/*Drop table ResponsesPPC
Drop table ConfirmPPC
Drop table TempPPC
*/
/*I don't drop them right now so I can look at them,
but normally, one would drop those tables.*/

Thanks a lot for your help,
-StarwizJustin Lebar (starwiz@.innovate-inc.com) writes:
> Sorry about the huge post, but I think this is the amount of
> information necessary for someone to help me with a good answer.

Yes, there was a whole lot of information, but I was not able to get
a complete understanding of what's going on. Probably because I'm too
impatient to go through it over and over again to get the pieces together.

There is a however a standard advice for this kind of problems. Namely,
in your posting include CREATE TABLE statements for the involved tables
(it may be a good idea to trim irrelevant columns), INSERT statements
with sample data and the desired output from that data. I realize that
in your case you may need to provide some 100-200 rows to get some
representative data. An alternative is put the data in comma-separated
files that can be bulk-loaded. Put any such files in an attachments, to
avoid line-wrap problems.

>hitid - the HitID of the user, taken from the session cookie. If there
>is no session cookie (for whatever reason), the HidID is written out
>as 0. While it wouldn't occur often, I can't guarantee that there will
>never be more than one response record attached to a singular hitid.

Not that I think it matters here, but I would store a NULL in HitID when
there is no hit id.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Justin Lebar (webmaster@.ransomlatin.com) writes:
> Because I'm posting to this online and not through an e-mail list, I
> don't see where I can attach any files. What I've done is put CSVs of
> samples for each table inside this post; you can still open it with
> Excel, albeit it might be a little more difficult because of the
> inserted linebreaks. I couldn't think of any other method...oh well.

Most newsreaders provide facilities to make attachments. I am not
familiar with the DevDex interface to tell whether this is possible
there.

Anyway, the only file that wrapped was the Hits table, and I was able
to repair that part, and I even managed to load it. However, I gave
up with the other two, as the date format was funky. Hits was also a
little suspect, as there was one column missing.

And most of all: there was no expected results to work from!

It would be better if you could create the BCP files in this way:

BCP db..tbl out tbl.bcp -T -c -t,

When you review the BCP files, make sure that dates are in the format
YYYY-MM-DD hh:mm:ss (milliseconds may be present, but BCP does not
seem to like missing seconds.) I believe that BCP will always generate
this format, and not honour regional settings.

If you cannot find a way to make attachments, then just include the
files as last time.

Be careful that the data agrees with the CREATE TABLE statements you posted.

And don't forget to include the expected results!

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Actually, in the past few hours, I re-wrote the entire call, and I got
it to work! Sorry I put you through all that...thanks for working with
me; I appreciate it.

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!|||Starwiz (nospam@.nospam.com) writes:
> Actually, in the past few hours, I re-wrote the entire call, and I got
> it to work! Sorry I put you through all that...thanks for working with
> me; I appreciate it.

Glad to hear you got it working on your own!

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.aspsql

Generating values as part of a compound key

BEGINNER QUESTION

I have a table which has a compound primary key consisting of two columns.

One of these columns is a foreign key which is generated in another table by
an identity.

I want to be able to generate the other primary key column value
automatically when an insert occurs but assume that I cannot use an identity
because it would have to be unique for this table.

There will be potentially more than one user accessing this table so I want
to avoid generating the key on the client side.

How can I do this? Will it require some hardcore T-SQL?

I hope this is clear (I suspect it isn't) I'd be happy to supply more info.
I would be extremely grateful for any help!

Mark.Mark wrote:
> BEGINNER QUESTION
> I have a table which has a compound primary key consisting of two
> columns.
> One of these columns is a foreign key which is generated in another
> table by an identity.

But with "generated" you don't mean it's also inserted into the table with
the compound key at the same time, do you?

> I want to be able to generate the other primary key column value
> automatically when an insert occurs but assume that I cannot use an
> identity because it would have to be unique for this table.

I don't see a problem here because identity *is* unique to your compound
key table.

> There will be potentially more than one user accessing this table so
> I want to avoid generating the key on the client side.

Yes, of course.

> How can I do this? Will it require some hardcore T-SQL?
> I hope this is clear (I suspect it isn't) I'd be happy to supply more
> info. I would be extremely grateful for any help!

Not fully to be honest. Maybe you post some DDL so we can see the table
layout. Also, it's not 100% clear to me when inserts in your main table
occur.

Kind regards

robert|||I hope it may be clearer if I outline what the tables are for:

I'm basically writing an application that stores information about
'behaviour incidents' at a school. The table in question is the 'incidents'
table which is used to record information about individual incidents of
negative behaviour (ok - let's call it being naughty).

The primary key for the 'incidents' table is made up of an 'incidentID' and
'pupilID'. The pupilID indicates the pupil(s) who were involved in the
incident and is itself a foreign key into a 'pupils' table.

This is to reflect the possibility that more than one pupil can be involved
in the same incident. In this case, there may be for example three rows with
the same 'incidentID' - each having a unique pupilID to reflect one incident
in which three different pupils were involved.

My question really revolves around how to generate the 'incidentID' that is
unique at the time of insertion but allows duplicates if more than one pupil
is involved.

Can I insert the first row and retrieve the identity with a scope_identity
and then just insert the rest of the rows with the same incidentID? Wouldn't
that return an error as the identity column wouldn't contain all unique
values.

I hope this is clearer.

Thanks for your time!

Mark.

"Robert Klemme" <bob.news@.gmx.net> wrote in message
news:3nj2krF1nuhtU1@.individual.net...
> Mark wrote:
>> BEGINNER QUESTION
>>
>> I have a table which has a compound primary key consisting of two
>> columns.
>>
>> One of these columns is a foreign key which is generated in another
>> table by an identity.
> But with "generated" you don't mean it's also inserted into the table with
> the compound key at the same time, do you?
>> I want to be able to generate the other primary key column value
>> automatically when an insert occurs but assume that I cannot use an
>> identity because it would have to be unique for this table.
> I don't see a problem here because identity *is* unique to your compound
> key table.
>> There will be potentially more than one user accessing this table so
>> I want to avoid generating the key on the client side.
> Yes, of course.
>> How can I do this? Will it require some hardcore T-SQL?
>>
>> I hope this is clear (I suspect it isn't) I'd be happy to supply more
>> info. I would be extremely grateful for any help!
> Not fully to be honest. Maybe you post some DDL so we can see the table
> layout. Also, it's not 100% clear to me when inserts in your main table
> occur.
> Kind regards
> robert|||Mark wrote:
> I hope it may be clearer if I outline what the tables are for:
> I'm basically writing an application that stores information about
> 'behaviour incidents' at a school. The table in question is the
> 'incidents' table which is used to record information about
> individual incidents of negative behaviour (ok - let's call it being
> naughty).
> The primary key for the 'incidents' table is made up of an
> 'incidentID' and 'pupilID'. The pupilID indicates the pupil(s) who
> were involved in the incident and is itself a foreign key into a
> 'pupils' table.
> This is to reflect the possibility that more than one pupil can be
> involved in the same incident. In this case, there may be for example
> three rows with the same 'incidentID' - each having a unique pupilID
> to reflect one incident in which three different pupils were involved.
> My question really revolves around how to generate the 'incidentID'
> that is unique at the time of insertion but allows duplicates if more
> than one pupil is involved.
> Can I insert the first row and retrieve the identity with a
> scope_identity and then just insert the rest of the rows with the
> same incidentID? Wouldn't that return an error as the identity column
> wouldn't contain all unique values.

You are right, this table layout would not work with identity. However, I
figure your table layout may not be optimal because you really have a n-m
relationship between incidents and pupils. And as far as I can see
there's no place to store information where there is just one piece per
incident (for example date and time). With all that I know ATM I would
have it as follows:

table incidents with date, time, location whatever and incidentid
(identity)
table pupils with pupilid (identity), name, day of birth - whatever
table pupils_in_incidend with incidentid, pupilid (both foreign keys)

This seems the most normalized approach here.

Kind regards

robert|||> You are right, this table layout would not work with identity. However, I
> figure your table layout may not be optimal because you really have a n-m
> relationship between incidents and pupils. And as far as I can see
> there's no place to store information where there is just one piece per
> incident (for example date and time). With all that I know ATM I would
> have it as follows:
> table incidents with date, time, location whatever and incidentid
> (identity)
> table pupils with pupilid (identity), name, day of birth - whatever
> table pupils_in_incidend with incidentid, pupilid (both foreign keys)
> This seems the most normalized approach here.
> Kind regards
> robert

OF COURSE! I should have seen that it would be silly to duplicate all of the
incident information for every pupil involved in a given incident.

Thank you immensely for your help!

Mark.

generating user instances in sql server is disabled. use sp_configure user instances enabl

When I am in Visual Studio 2005, and I try to add an SQL database, I get the following error "generating user instances in sql server is disabled. use sp_configure user instances enabled to generate user instances."

I am currently using SQL server 2005 Express.

What do I need to do, to create an SQL database? Thanks in advance.

Execute this statement:

sp_configure'user instances enabled', 1;RECONFIGURE

You can execute it either from Management Studio for SQL Express (http://msdn.microsoft.com/vstudio/express/sql/download/) or from Visual Stuido (create new connection to SQL Express instance then New Query, then execute it!).

Good luck.

|||

Thanks for the advice. Being so new to this, a lot of that went over my head. So I have two questions...

1. How do I create a new connection the SQL Express instance?

2. How do I execute a query after creating it? (With F5?)

Thanks for your help CS4Ever

|||

Select theData | Add New Data Source menu command. This launches theData Source Configuration Wizard.

In theData Source Configuration Wizard, in theChoose a Data Source type step, selectDatabase and then clickNext,then complete the wizard.

Checkout this link:http://msdn.microsoft.com/vstudio/tour/vs2005_guided_tour/VS2005pro/Smart_Client/DataBinder.htm

Good luck.

|||Ok I got it to work. Thanks!

Generating unique nvarchar unique key

Hi,
Would like to know whether it is possible to generate a unique key for a
nvarchar column. This key will need to generate by a T-SQL Stored Proc, the
format as follow:
XX-ZZZZ
XX is month, where I think it should be able to generate using DATEPART(mm,
GETDATE())
- is fixed delimiter
ZZZZ is a running number, which will be reseted to 0000 when it reach 9999
So, everytime we run the Stored Proc to generate this key, the statement
will need to break the ZZZZ and increase by one in order to generate the new
id (concatenate with XX-).
But we run into duplication key issue when this Stored Proc was called by
several concurrent clients (like every few thousand records, duplication of
keys will occur once, but occurance totally random).
Would like to know whether there are any better ways to ensure the key
generated (with above format) will always be unique?
ThanksIf you plan to use this as a primary key, it's a very bad idea. The best
primary key is an auto-incremented integer. MSSQL will manage everything for
you with an identity field. If you absolutely need that field you're talking
about (XX-ZZZZ), then create a second field and populate it with a process
that runs every hour or every few minutes. And most importantly, don't make
it part of the primary key.
What you are suggesting is an invitation for deadlocks, and a whole bunch of
other problems. For instance, are you 150% sure that you won't have more
than 10 000 records per month?
Alain Quesnel
alainsansspam@.logiquel.com
www.logiquel.com
"Thalia Mei" <thaliamei@.gmail.com> wrote in message
news:F165CCBF-5AC9-4BAF-B48F-477728F8A399@.microsoft.com...
> Hi,
> Would like to know whether it is possible to generate a unique key for a
> nvarchar column. This key will need to generate by a T-SQL Stored Proc,
> the format as follow:
> XX-ZZZZ
> XX is month, where I think it should be able to generate using
> DATEPART(mm, GETDATE())
> - is fixed delimiter
> ZZZZ is a running number, which will be reseted to 0000 when it reach 9999
> So, everytime we run the Stored Proc to generate this key, the statement
> will need to break the ZZZZ and increase by one in order to generate the
> new id (concatenate with XX-).
> But we run into duplication key issue when this Stored Proc was called by
> several concurrent clients (like every few thousand records, duplication
> of keys will occur once, but occurance totally random).
> Would like to know whether there are any better ways to ensure the key
> generated (with above format) will always be unique?
> Thanks