Generating Script in sql2005

Previosly, in SqlServer2000, when i was putting database changes (stored procedures) to the production server, I used to generate script for all stored procedures on the source server and run it it on the production server. It had in itself all the "If Exist--drop" rows, so it was posible to copy the new ones and change the existing stored procedures .

Now I'm using SqlServer2005 Express and when i want to generate sript, that "If Exist--drop" is missing so i have trouble changing all stored procedures in few steps. My question is: How can i get the script to contain those "If Exist--drop" automaticaly?

thanks

Yes. Management Studio uses the following logic,

When you create, "Script Behavior” = “Generate Create Statement Only”

If you say “Include If NOT Exists” option as TRUE then it Generate the following template

IF NOT EXISTS (SELECT * FROM ..

BEGIN

CREATE ..

END

When you create, "Script Behavior” = “Generate Drop Statement Only”

If you say “Include If NOT Exists” option as TRUE then it Generate the following template

IF EXISTS (SELECT * FROM ..

BEGIN

DROP ..

END

Missing in Console:

When you create, "Script Behavior” = “Generate Drop Statements Followed By Create Statement”

If you say “Include If NOT Exists” option as TRUE then it Generate the following template

IF EXISTS (SELECT * FROM ..

BEGIN

DROP ..

END

CREATE ..

Since the above option is missing (may be bug), first generate the drop script & append with Create generation Script with/without If Not Exist.

|||thanks|||

(This is a related question, so I'll borrow the thread)

Is there a way to save the scripting options for the script wizard or customize the default options?

For various reasons (as this thread points out) I use script generaiton options changed from the defualts as:

Include Descriptive Headers = FALSE
Include If NOT EXISTS = True
Script Drop = True
Script USE DATABASE = FALSE
Script Indexes = True

Can I change my Script Generation Wizard defaults so I don't have to select these options each time?

|||

michaelplevy wrote:

Is there a way to save the scripting options for the script wizard or customize the default options?

With SQL Server 2005 Service Pack 2, we've introduced the Tools -> Options... -> Scripting dialog so that you can change the behavior of how Management Studio generates scripts. These settings will be the default settings for the Generate Script Wizard.

Does this help?

Paul A. Mestemaker II

Program Manager

Microsoft SQL Server

http://blogs.msdn.com/sqlrem/

|||Paul, thanks for the tip about changing the script options.|||Hello,

Not all of the options available during scripting appear to be configurable from Tools > Options.
I'm running 9.00.3042.00. Is there a hotfix that remedies this? Or is the selective availability by design? I'm mainly interested in the "Script Drop" option.

Vivek

Generating Script in sql2005

Previosly, in SqlServer2000, when i was putting database changes (stored procedures) to the production server, I used to generate script for all stored procedures on the source server and run it it on the production server. It had in itself all the "If Exist--drop" rows, so it was posible to copy the new ones and change the existing stored procedures .

Now I'm using SqlServer2005 Express and when i want to generate sript, that "If Exist--drop" is missing so i have trouble changing all stored procedures in few steps. My question is: How can i get the script to contain those "If Exist--drop" automaticaly?

thanks

Yes. Management Studio uses the following logic,

When you create, "Script Behavior” = “Generate Create Statement Only”

If you say “Include If NOT Exists” option as TRUE then it Generate the following template

IF NOT EXISTS (SELECT * FROM ..

BEGIN

CREATE ..

END

When you create, "Script Behavior” = “Generate Drop Statement Only”

If you say “Include If NOT Exists” option as TRUE then it Generate the following template

IF EXISTS (SELECT * FROM ..

BEGIN

DROP ..

END

Missing in Console:

When you create, "Script Behavior” = “Generate Drop Statements Followed By Create Statement”

If you say “Include If NOT Exists” option as TRUE then it Generate the following template

IF EXISTS (SELECT * FROM ..

BEGIN

DROP ..

END

CREATE ..

Since the above option is missing (may be bug), first generate the drop script & append with Create generation Script with/without If Not Exist.

|||thanks|||

(This is a related question, so I'll borrow the thread)

Is there a way to save the scripting options for the script wizard or customize the default options?

For various reasons (as this thread points out) I use script generaiton options changed from the defualts as:

Include Descriptive Headers = FALSE
Include If NOT EXISTS = True
Script Drop = True
Script USE DATABASE = FALSE
Script Indexes = True

Can I change my Script Generation Wizard defaults so I don't have to select these options each time?

|||

michaelplevy wrote:

Is there a way to save the scripting options for the script wizard or customize the default options?

With SQL Server 2005 Service Pack 2, we've introduced the Tools -> Options... -> Scripting dialog so that you can change the behavior of how Management Studio generates scripts. These settings will be the default settings for the Generate Script Wizard.

Does this help?

Paul A. Mestemaker II

Program Manager

Microsoft SQL Server

http://blogs.msdn.com/sqlrem/

|||Paul, thanks for the tip about changing the script options.|||Hello,

Not all of the options available during scripting appear to be configurable from Tools > Options.
I'm running 9.00.3042.00. Is there a hotfix that remedies this? Or is the selective availability by design? I'm mainly interested in the "Script Drop" option.

Vivek

Generating a list of databases in SQL2005?

Silly question but... in SQL 2000 it used to be possible to right click on
the "databases" folder in SQL enterprise administrator and export a list of
databases. I can't seem to do the same thing in SQL 2005 Management Studio.
Is there an equivelant way of doing this?
Thanks,
BradSees that feature is missing from the GUI. How about a SQL query instead?
SELECT name FROM sys.databases
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Brad Baker" <brad@.nospam.nospam> wrote in message news:uQp%234sj9GHA.3396@.TK2MSFTNGP04.phx.gbl...
> Silly question but... in SQL 2000 it used to be possible to right click on
> the "databases" folder in SQL enterprise administrator and export a list of
> databases. I can't seem to do the same thing in SQL 2005 Management Studio.
> Is there an equivelant way of doing this?
> Thanks,
> Brad
>|||Thanks! That does the trick.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:uBFcnEn9GHA.4224@.TK2MSFTNGP02.phx.gbl...
> Sees that feature is missing from the GUI. How about a SQL query instead?
> SELECT name FROM sys.databases
>
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Brad Baker" <brad@.nospam.nospam> wrote in message
> news:uQp%234sj9GHA.3396@.TK2MSFTNGP04.phx.gbl...
>> Silly question but... in SQL 2000 it used to be possible to right click
>> on the "databases" folder in SQL enterprise administrator and export a
>> list of databases. I can't seem to do the same thing in SQL 2005
>> Management Studio. Is there an equivelant way of doing this?
>> Thanks,
>> Brad

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.

General XML SQL 2005 Question

Hi All
I am new to Sql Xml and currently working through some examples to
understand XML processing in SQL2005 because we are planning to use SQL 2005
with XML. If this is not the right place to ask this question then my
apologies.
Currently using a XML Schema. Schema is
- <xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema">
<xsd:element name="logRecord" type="logRecordType" />
- <xsd:complexType name="errorType">
- <xsd:complexContent>
- <xsd:restriction base="xsd:anyType">
- <xsd:sequence>
<xsd:element name="message" type="xsd:string" />
<xsd:element name="module" type="xsd:string" />
</xsd:sequence>
<xsd:attribute name="number" type="xsd:int" />
</xsd:restriction>
</xsd:complexContent>
</xsd:complexType>
- <xsd:complexType name="informationType">
- <xsd:complexContent>
- <xsd:restriction base="xsd:anyType">
- <xsd:sequence>
<xsd:element name="message" type="xsd:string" />
</xsd:sequence>
<xsd:attribute name="flag" type="flagEnum" />
</xsd:restriction>
</xsd:complexContent>
</xsd:complexType>
- <xsd:complexType name="logRecordType">
- <xsd:complexContent>
- <xsd:restriction base="xsd:anyType">
- <xsd:choice maxOccurs="unbounded">
<xsd:element name="information" type="informationType" />
<xsd:element name="error" type="errorType" />
<xsd:element name="post" type="postType" />
</xsd:choice>
<xsd:attribute name="machine" type="xsd:string" />
<xsd:attribute name="timestamp" type="xsd:dateTime" />
</xsd:restriction>
</xsd:complexContent>
</xsd:complexType>
- <xsd:complexType name="postType">
- <xsd:complexContent>
- <xsd:restriction base="xsd:anyType">
- <xsd:sequence>
<xsd:element name="moreInformation" type="xsd:string" minOccurs="0" />
</xsd:sequence>
<xsd:attribute name="eventType" type="eventEnum" />
</xsd:restriction>
</xsd:complexContent>
</xsd:complexType>
- <xsd:simpleType name="eventEnum">
- <xsd:restriction base="xsd:string">
<xsd:enumeration value="appStart" />
<xsd:enumeration value="appClose" />
<xsd:enumeration value="logIn" />
<xsd:enumeration value="logOut" />
</xsd:restriction>
</xsd:simpleType>
- <xsd:simpleType name="flagEnum">
- <xsd:restriction base="xsd:string">
<xsd:enumeration value="warning" />
<xsd:enumeration value="information" />
<xsd:enumeration value="failure" />
<xsd:enumeration value="custom" />
</xsd:restriction>
</xsd:simpleType>
</xsd:schema>
When I execute
select top 1 * from Universallog For XML Auto
Output I get is
<Universallog ID="1" LogDateTime="2006-09-14T15:50:56.210"
ApplicationName="SalesApp">
<LogRecord>
<logRecord machine="server1" timestamp="2000-01-12T12:13:14Z">
<post eventType="appStart">
<moreInformation>All Services starting</moreInformation>
</post>
</logRecord>
</LogRecord>
</Universallog>
Question
I do not understand where is the Element <LogRecord> is coming from as it
is not defined in the XML Schema
Any feed back provided is greatly appreciated.
Thank you
Regards
Sanjay
You need to send more info such as the definition of the UniversalLog table.
Peter DeBetta, MVP - SQL Server
http://sqlblog.com
"Sanjay Narayan" <SanjayNarayan@.discussions.microsoft.com> wrote in message
news:1B0F12C7-5100-4B8E-B547-09982E733E22@.microsoft.com...
> Hi All
> I am new to Sql Xml and currently working through some examples to
> understand XML processing in SQL2005 because we are planning to use SQL
> 2005
> with XML. If this is not the right place to ask this question then my
> apologies.
>
> Currently using a XML Schema. Schema is
> - <xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema">
> <xsd:element name="logRecord" type="logRecordType" />
> - <xsd:complexType name="errorType">
> - <xsd:complexContent>
> - <xsd:restriction base="xsd:anyType">
> - <xsd:sequence>
> <xsd:element name="message" type="xsd:string" />
> <xsd:element name="module" type="xsd:string" />
> </xsd:sequence>
> <xsd:attribute name="number" type="xsd:int" />
> </xsd:restriction>
> </xsd:complexContent>
> </xsd:complexType>
> - <xsd:complexType name="informationType">
> - <xsd:complexContent>
> - <xsd:restriction base="xsd:anyType">
> - <xsd:sequence>
> <xsd:element name="message" type="xsd:string" />
> </xsd:sequence>
> <xsd:attribute name="flag" type="flagEnum" />
> </xsd:restriction>
> </xsd:complexContent>
> </xsd:complexType>
> - <xsd:complexType name="logRecordType">
> - <xsd:complexContent>
> - <xsd:restriction base="xsd:anyType">
> - <xsd:choice maxOccurs="unbounded">
> <xsd:element name="information" type="informationType" />
> <xsd:element name="error" type="errorType" />
> <xsd:element name="post" type="postType" />
> </xsd:choice>
> <xsd:attribute name="machine" type="xsd:string" />
> <xsd:attribute name="timestamp" type="xsd:dateTime" />
> </xsd:restriction>
> </xsd:complexContent>
> </xsd:complexType>
> - <xsd:complexType name="postType">
> - <xsd:complexContent>
> - <xsd:restriction base="xsd:anyType">
> - <xsd:sequence>
> <xsd:element name="moreInformation" type="xsd:string" minOccurs="0" />
> </xsd:sequence>
> <xsd:attribute name="eventType" type="eventEnum" />
> </xsd:restriction>
> </xsd:complexContent>
> </xsd:complexType>
> - <xsd:simpleType name="eventEnum">
> - <xsd:restriction base="xsd:string">
> <xsd:enumeration value="appStart" />
> <xsd:enumeration value="appClose" />
> <xsd:enumeration value="logIn" />
> <xsd:enumeration value="logOut" />
> </xsd:restriction>
> </xsd:simpleType>
> - <xsd:simpleType name="flagEnum">
> - <xsd:restriction base="xsd:string">
> <xsd:enumeration value="warning" />
> <xsd:enumeration value="information" />
> <xsd:enumeration value="failure" />
> <xsd:enumeration value="custom" />
> </xsd:restriction>
> </xsd:simpleType>
> </xsd:schema>
>
> When I execute
> select top 1 * from Universallog For XML Auto
> Output I get is
> <Universallog ID="1" LogDateTime="2006-09-14T15:50:56.210"
> ApplicationName="SalesApp">
> <LogRecord>
> <logRecord machine="server1" timestamp="2000-01-12T12:13:14Z">
> <post eventType="appStart">
> <moreInformation>All Services starting</moreInformation>
> </post>
> </logRecord>
> </LogRecord>
> </Universallog>
>
> Question
> I do not understand where is the Element <LogRecord> is coming from as it
> is not defined in the XML Schema
> Any feed back provided is greatly appreciated.
> Thank you
> Regards
> Sanjay
>
|||Your statement: 'select top 1 * from Universallog For XML Auto' does not
specify the schema anywhere. How were you expecting the schema to interact
with SQL Server? SQL Server 2005 allows you to define an XML datatype that
conforms to a particular schema but the closest SQL XML comes to this is
xpath queries using a mapping schema. For XML Explicit will allow you to
define what you want the output to look like but AUTO produces an XML
document based on the columns involved in the select statement.
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
"Sanjay Narayan" <SanjayNarayan@.discussions.microsoft.com> wrote in message
news:1B0F12C7-5100-4B8E-B547-09982E733E22@.microsoft.com...
> Hi All
> I am new to Sql Xml and currently working through some examples to
> understand XML processing in SQL2005 because we are planning to use SQL
> 2005
> with XML. If this is not the right place to ask this question then my
> apologies.
>
> Currently using a XML Schema. Schema is
> - <xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema">
> <xsd:element name="logRecord" type="logRecordType" />
> - <xsd:complexType name="errorType">
> - <xsd:complexContent>
> - <xsd:restriction base="xsd:anyType">
> - <xsd:sequence>
> <xsd:element name="message" type="xsd:string" />
> <xsd:element name="module" type="xsd:string" />
> </xsd:sequence>
> <xsd:attribute name="number" type="xsd:int" />
> </xsd:restriction>
> </xsd:complexContent>
> </xsd:complexType>
> - <xsd:complexType name="informationType">
> - <xsd:complexContent>
> - <xsd:restriction base="xsd:anyType">
> - <xsd:sequence>
> <xsd:element name="message" type="xsd:string" />
> </xsd:sequence>
> <xsd:attribute name="flag" type="flagEnum" />
> </xsd:restriction>
> </xsd:complexContent>
> </xsd:complexType>
> - <xsd:complexType name="logRecordType">
> - <xsd:complexContent>
> - <xsd:restriction base="xsd:anyType">
> - <xsd:choice maxOccurs="unbounded">
> <xsd:element name="information" type="informationType" />
> <xsd:element name="error" type="errorType" />
> <xsd:element name="post" type="postType" />
> </xsd:choice>
> <xsd:attribute name="machine" type="xsd:string" />
> <xsd:attribute name="timestamp" type="xsd:dateTime" />
> </xsd:restriction>
> </xsd:complexContent>
> </xsd:complexType>
> - <xsd:complexType name="postType">
> - <xsd:complexContent>
> - <xsd:restriction base="xsd:anyType">
> - <xsd:sequence>
> <xsd:element name="moreInformation" type="xsd:string" minOccurs="0" />
> </xsd:sequence>
> <xsd:attribute name="eventType" type="eventEnum" />
> </xsd:restriction>
> </xsd:complexContent>
> </xsd:complexType>
> - <xsd:simpleType name="eventEnum">
> - <xsd:restriction base="xsd:string">
> <xsd:enumeration value="appStart" />
> <xsd:enumeration value="appClose" />
> <xsd:enumeration value="logIn" />
> <xsd:enumeration value="logOut" />
> </xsd:restriction>
> </xsd:simpleType>
> - <xsd:simpleType name="flagEnum">
> - <xsd:restriction base="xsd:string">
> <xsd:enumeration value="warning" />
> <xsd:enumeration value="information" />
> <xsd:enumeration value="failure" />
> <xsd:enumeration value="custom" />
> </xsd:restriction>
> </xsd:simpleType>
> </xsd:schema>
>
> When I execute
> select top 1 * from Universallog For XML Auto
> Output I get is
> <Universallog ID="1" LogDateTime="2006-09-14T15:50:56.210"
> ApplicationName="SalesApp">
> <LogRecord>
> <logRecord machine="server1" timestamp="2000-01-12T12:13:14Z">
> <post eventType="appStart">
> <moreInformation>All Services starting</moreInformation>
> </post>
> </logRecord>
> </LogRecord>
> </Universallog>
>
> Question
> I do not understand where is the Element <LogRecord> is coming from as it
> is not defined in the XML Schema
> Any feed back provided is greatly appreciated.
> Thank you
> Regards
> Sanjay
>
|||Thanks. It make sense now and I can now understand why the output is as such.
"Roger Wolter[MSFT]" wrote:

> Your statement: 'select top 1 * from Universallog For XML Auto' does not
> specify the schema anywhere. How were you expecting the schema to interact
> with SQL Server? SQL Server 2005 allows you to define an XML datatype that
> conforms to a particular schema but the closest SQL XML comes to this is
> xpath queries using a mapping schema. For XML Explicit will allow you to
> define what you want the output to look like but AUTO produces an XML
> document based on the columns involved in the select statement.
> --
> 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
> "Sanjay Narayan" <SanjayNarayan@.discussions.microsoft.com> wrote in message
> news:1B0F12C7-5100-4B8E-B547-09982E733E22@.microsoft.com...
>
>

General XML SQL 2005 Question

Hi All
I am new to Sql Xml and currently working through some examples to
understand XML processing in SQL2005 because we are planning to use SQL 2005
with XML. If this is not the right place to ask this question then my
apologies.
Currently using a XML Schema. Schema is
- <xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema">
<xsd:element name="logRecord" type="logRecordType" />
- <xsd:complexType name="errorType">
- <xsd:complexContent>
- <xsd:restriction base="xsd:anyType">
- <xsd:sequence>
<xsd:element name="message" type="xsd:string" />
<xsd:element name="module" type="xsd:string" />
</xsd:sequence>
<xsd:attribute name="number" type="xsd:int" />
</xsd:restriction>
</xsd:complexContent>
</xsd:complexType>
- <xsd:complexType name="informationType">
- <xsd:complexContent>
- <xsd:restriction base="xsd:anyType">
- <xsd:sequence>
<xsd:element name="message" type="xsd:string" />
</xsd:sequence>
<xsd:attribute name="flag" type="flagEnum" />
</xsd:restriction>
</xsd:complexContent>
</xsd:complexType>
- <xsd:complexType name="logRecordType">
- <xsd:complexContent>
- <xsd:restriction base="xsd:anyType">
- <xsd:choice maxOccurs="unbounded">
<xsd:element name="information" type="informationType" />
<xsd:element name="error" type="errorType" />
<xsd:element name="post" type="postType" />
</xsd:choice>
<xsd:attribute name="machine" type="xsd:string" />
<xsd:attribute name="timestamp" type="xsd:dateTime" />
</xsd:restriction>
</xsd:complexContent>
</xsd:complexType>
- <xsd:complexType name="postType">
- <xsd:complexContent>
- <xsd:restriction base="xsd:anyType">
- <xsd:sequence>
<xsd:element name="moreInformation" type="xsd:string" minOccurs="0" />
</xsd:sequence>
<xsd:attribute name="eventType" type="eventEnum" />
</xsd:restriction>
</xsd:complexContent>
</xsd:complexType>
- <xsd:simpleType name="eventEnum">
- <xsd:restriction base="xsd:string">
<xsd:enumeration value="appStart" />
<xsd:enumeration value="appClose" />
<xsd:enumeration value="logIn" />
<xsd:enumeration value="logOut" />
</xsd:restriction>
</xsd:simpleType>
- <xsd:simpleType name="flagEnum">
- <xsd:restriction base="xsd:string">
<xsd:enumeration value="warning" />
<xsd:enumeration value="information" />
<xsd:enumeration value="failure" />
<xsd:enumeration value="custom" />
</xsd:restriction>
</xsd:simpleType>
</xsd:schema>
When I execute
select top 1 * from Universallog For XML Auto
Output I get is
<Universallog ID="1" LogDateTime="2006-09-14T15:50:56.210"
ApplicationName="SalesApp">
<LogRecord>
<logRecord machine="server1" timestamp="2000-01-12T12:13:14Z">
<post eventType="appStart">
<moreInformation>All Services starting</moreInformation>
</post>
</logRecord>
</LogRecord>
</Universallog>
Question
I do not understand where is the Element <LogRecord> is coming from as it
is not defined in the XML Schema
Any feed back provided is greatly appreciated.
Thank you
Regards
SanjayYou need to send more info such as the definition of the UniversalLog table.
Peter DeBetta, MVP - SQL Server
http://sqlblog.com
--
"Sanjay Narayan" <SanjayNarayan@.discussions.microsoft.com> wrote in message
news:1B0F12C7-5100-4B8E-B547-09982E733E22@.microsoft.com...
> Hi All
> I am new to Sql Xml and currently working through some examples to
> understand XML processing in SQL2005 because we are planning to use SQL
> 2005
> with XML. If this is not the right place to ask this question then my
> apologies.
>
> Currently using a XML Schema. Schema is
> - <xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema">
> <xsd:element name="logRecord" type="logRecordType" />
> - <xsd:complexType name="errorType">
> - <xsd:complexContent>
> - <xsd:restriction base="xsd:anyType">
> - <xsd:sequence>
> <xsd:element name="message" type="xsd:string" />
> <xsd:element name="module" type="xsd:string" />
> </xsd:sequence>
> <xsd:attribute name="number" type="xsd:int" />
> </xsd:restriction>
> </xsd:complexContent>
> </xsd:complexType>
> - <xsd:complexType name="informationType">
> - <xsd:complexContent>
> - <xsd:restriction base="xsd:anyType">
> - <xsd:sequence>
> <xsd:element name="message" type="xsd:string" />
> </xsd:sequence>
> <xsd:attribute name="flag" type="flagEnum" />
> </xsd:restriction>
> </xsd:complexContent>
> </xsd:complexType>
> - <xsd:complexType name="logRecordType">
> - <xsd:complexContent>
> - <xsd:restriction base="xsd:anyType">
> - <xsd:choice maxOccurs="unbounded">
> <xsd:element name="information" type="informationType" />
> <xsd:element name="error" type="errorType" />
> <xsd:element name="post" type="postType" />
> </xsd:choice>
> <xsd:attribute name="machine" type="xsd:string" />
> <xsd:attribute name="timestamp" type="xsd:dateTime" />
> </xsd:restriction>
> </xsd:complexContent>
> </xsd:complexType>
> - <xsd:complexType name="postType">
> - <xsd:complexContent>
> - <xsd:restriction base="xsd:anyType">
> - <xsd:sequence>
> <xsd:element name="moreInformation" type="xsd:string" minOccurs="0" />
> </xsd:sequence>
> <xsd:attribute name="eventType" type="eventEnum" />
> </xsd:restriction>
> </xsd:complexContent>
> </xsd:complexType>
> - <xsd:simpleType name="eventEnum">
> - <xsd:restriction base="xsd:string">
> <xsd:enumeration value="appStart" />
> <xsd:enumeration value="appClose" />
> <xsd:enumeration value="logIn" />
> <xsd:enumeration value="logOut" />
> </xsd:restriction>
> </xsd:simpleType>
> - <xsd:simpleType name="flagEnum">
> - <xsd:restriction base="xsd:string">
> <xsd:enumeration value="warning" />
> <xsd:enumeration value="information" />
> <xsd:enumeration value="failure" />
> <xsd:enumeration value="custom" />
> </xsd:restriction>
> </xsd:simpleType>
> </xsd:schema>
>
> When I execute
> select top 1 * from Universallog For XML Auto
> Output I get is
> <Universallog ID="1" LogDateTime="2006-09-14T15:50:56.210"
> ApplicationName="SalesApp">
> <LogRecord>
> <logRecord machine="server1" timestamp="2000-01-12T12:13:14Z">
> <post eventType="appStart">
> <moreInformation>All Services starting</moreInformation>
> </post>
> </logRecord>
> </LogRecord>
> </Universallog>
>
> Question
> I do not understand where is the Element <LogRecord> is coming from as it
> is not defined in the XML Schema
> Any feed back provided is greatly appreciated.
> Thank you
> Regards
> Sanjay
>|||Your statement: 'select top 1 * from Universallog For XML Auto' does not
specify the schema anywhere. How were you expecting the schema to interact
with SQL Server? SQL Server 2005 allows you to define an XML datatype that
conforms to a particular schema but the closest SQL XML comes to this is
xpath queries using a mapping schema. For XML Explicit will allow you to
define what you want the output to look like but AUTO produces an XML
document based on the columns involved in the select statement.
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
"Sanjay Narayan" <SanjayNarayan@.discussions.microsoft.com> wrote in message
news:1B0F12C7-5100-4B8E-B547-09982E733E22@.microsoft.com...
> Hi All
> I am new to Sql Xml and currently working through some examples to
> understand XML processing in SQL2005 because we are planning to use SQL
> 2005
> with XML. If this is not the right place to ask this question then my
> apologies.
>
> Currently using a XML Schema. Schema is
> - <xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema">
> <xsd:element name="logRecord" type="logRecordType" />
> - <xsd:complexType name="errorType">
> - <xsd:complexContent>
> - <xsd:restriction base="xsd:anyType">
> - <xsd:sequence>
> <xsd:element name="message" type="xsd:string" />
> <xsd:element name="module" type="xsd:string" />
> </xsd:sequence>
> <xsd:attribute name="number" type="xsd:int" />
> </xsd:restriction>
> </xsd:complexContent>
> </xsd:complexType>
> - <xsd:complexType name="informationType">
> - <xsd:complexContent>
> - <xsd:restriction base="xsd:anyType">
> - <xsd:sequence>
> <xsd:element name="message" type="xsd:string" />
> </xsd:sequence>
> <xsd:attribute name="flag" type="flagEnum" />
> </xsd:restriction>
> </xsd:complexContent>
> </xsd:complexType>
> - <xsd:complexType name="logRecordType">
> - <xsd:complexContent>
> - <xsd:restriction base="xsd:anyType">
> - <xsd:choice maxOccurs="unbounded">
> <xsd:element name="information" type="informationType" />
> <xsd:element name="error" type="errorType" />
> <xsd:element name="post" type="postType" />
> </xsd:choice>
> <xsd:attribute name="machine" type="xsd:string" />
> <xsd:attribute name="timestamp" type="xsd:dateTime" />
> </xsd:restriction>
> </xsd:complexContent>
> </xsd:complexType>
> - <xsd:complexType name="postType">
> - <xsd:complexContent>
> - <xsd:restriction base="xsd:anyType">
> - <xsd:sequence>
> <xsd:element name="moreInformation" type="xsd:string" minOccurs="0" />
> </xsd:sequence>
> <xsd:attribute name="eventType" type="eventEnum" />
> </xsd:restriction>
> </xsd:complexContent>
> </xsd:complexType>
> - <xsd:simpleType name="eventEnum">
> - <xsd:restriction base="xsd:string">
> <xsd:enumeration value="appStart" />
> <xsd:enumeration value="appClose" />
> <xsd:enumeration value="logIn" />
> <xsd:enumeration value="logOut" />
> </xsd:restriction>
> </xsd:simpleType>
> - <xsd:simpleType name="flagEnum">
> - <xsd:restriction base="xsd:string">
> <xsd:enumeration value="warning" />
> <xsd:enumeration value="information" />
> <xsd:enumeration value="failure" />
> <xsd:enumeration value="custom" />
> </xsd:restriction>
> </xsd:simpleType>
> </xsd:schema>
>
> When I execute
> select top 1 * from Universallog For XML Auto
> Output I get is
> <Universallog ID="1" LogDateTime="2006-09-14T15:50:56.210"
> ApplicationName="SalesApp">
> <LogRecord>
> <logRecord machine="server1" timestamp="2000-01-12T12:13:14Z">
> <post eventType="appStart">
> <moreInformation>All Services starting</moreInformation>
> </post>
> </logRecord>
> </LogRecord>
> </Universallog>
>
> Question
> I do not understand where is the Element <LogRecord> is coming from as it
> is not defined in the XML Schema
> Any feed back provided is greatly appreciated.
> Thank you
> Regards
> Sanjay
>|||Thanks. It make sense now and I can now understand why the output is as such
.
"Roger Wolter[MSFT]" wrote:

> Your statement: 'select top 1 * from Universallog For XML Auto' does not
> specify the schema anywhere. How were you expecting the schema to interac
t
> with SQL Server? SQL Server 2005 allows you to define an XML datatype tha
t
> conforms to a particular schema but the closest SQL XML comes to this is
> xpath queries using a mapping schema. For XML Explicit will allow you to
> define what you want the output to look like but AUTO produces an XML
> document based on the columns involved in the select statement.
> --
> 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
> "Sanjay Narayan" <SanjayNarayan@.discussions.microsoft.com> wrote in messag
e
> news:1B0F12C7-5100-4B8E-B547-09982E733E22@.microsoft.com...
>
>