Generic Stored procedure

Hi All!

i want to retrieve reults from table "tblCategory" by passing

search string as parameter and column name as parameter as well.

CREATE Procedure uspSearchCategory

(

@.Search nvarchar(255),

@.column varchar(100)

)

AS

SELECT

*

FROM

tblCategory

WHERE

@.column LIKE '%' + @.Search+'%'

This doesn't work as @.column in last line is incorrect. Can anybody tell me how can i achieve that.

If i write

name LIKE '%' + @.Search+'%' or

ID LIKE '%' + @.Search+'%'

it works.But can it works as general for ant column name i pass as @.column.

thanx

You need to turn your query into a string, then run the string using code like this:

exec sp_executesql @.query

where @.query is the nvarchar that contains the SQL to run

Generating SQL Script

Hi All
I have SQL Data Base . I am using asp application to access this Data Base.
I want to create a script to generate Data Base on other machines. I have
some data in that Data Base, which I required to run my application.
So the final SCRIPT should contain all relations, default values ,
Identities and Data.
Which method you advise to achieve these.
Kind Regards
Here is an article I wrote about generating SQL Server scripts. Hopefully
this will provide you with enough information to help you determine the best
way to generate your script.
http://www.dbazine.com/larsen4.shtml
----
Need SQL Server Examples check out my website at
http://www.geocities.com/sqlserverexamples
"F@.yy@.Z" <fayyaz.ahmed@.mvwebmaker.com> wrote in message
news:%23%23hi0yCaEHA.3112@.tk2msftngp13.phx.gbl...
> Hi All
> I have SQL Data Base . I am using asp application to access this Data
Base.
> I want to create a script to generate Data Base on other machines. I have
> some data in that Data Base, which I required to run my application.
> So the final SCRIPT should contain all relations, default values ,
> Identities and Data.
> Which method you advise to achieve these.
>
> Kind Regards
>
>
>
|||Thanks for your reply.
I tried using enterprise manager but its not creating script for data in the
table, only creating script for table.
Kind Regards
"Gregory A. Larsen" <greg.larsen@.netzero.com> wrote in message
news:ubhT35CaEHA.3888@.TK2MSFTNGP10.phx.gbl...
> Here is an article I wrote about generating SQL Server scripts. Hopefully
> this will provide you with enough information to help you determine the
best
> way to generate your script.
> http://www.dbazine.com/larsen4.shtml
> --
> ----
--
> ----
--[vbcol=seagreen]
> --
> Need SQL Server Examples check out my website at
> http://www.geocities.com/sqlserverexamples
> "F@.yy@.Z" <fayyaz.ahmed@.mvwebmaker.com> wrote in message
> news:%23%23hi0yCaEHA.3112@.tk2msftngp13.phx.gbl...
> Base.
have
>
|||Gregory's article shows you how to generate scripts for objects. One thing
to remember is SQL Server Enterprise Manager or DMO have no capabilities for
scripting the data in the form of INSERT statements.
I wrote a procedure to script data as insert statements, and it can be found
at: http://vyaskn.tripod.com/code.htm#inserts
Alternatively, to move entire database, look up BACKUP/RESTORE and attaching
and detaching databases in SQL Server Books Online.
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
"F@.yy@.Z" <fayyaz.ahmed@.mvwebmaker.com> wrote in message
news:%23%23hi0yCaEHA.3112@.tk2msftngp13.phx.gbl...
> Hi All
> I have SQL Data Base . I am using asp application to access this Data
Base.
> I want to create a script to generate Data Base on other machines. I have
> some data in that Data Base, which I required to run my application.
> So the final SCRIPT should contain all relations, default values ,
> Identities and Data.
> Which method you advise to achieve these.
>
> Kind Regards
>
>
>
|||Sorry, my methods don't copy the data.
----
Need SQL Server Examples check out my website at
http://www.geocities.com/sqlserverexamples
"F@.yy@.Z" <fayyaz.ahmed@.mvwebmaker.com> wrote in message
news:Oa6i3HDaEHA.2016@.TK2MSFTNGP09.phx.gbl...
> Thanks for your reply.
> I tried using enterprise manager but its not creating script for data in
the[vbcol=seagreen]
> table, only creating script for table.
> Kind Regards
>
>
> "Gregory A. Larsen" <greg.larsen@.netzero.com> wrote in message
> news:ubhT35CaEHA.3888@.TK2MSFTNGP10.phx.gbl...
Hopefully
> best
> ----
> --
> ----
> --
> have
>
|||Check out the free data and schema scripter from Innovartis - the makers of DB Ghost. www.dbghost.com
"F@.yy@.Z" wrote:

> Hi All
> I have SQL Data Base . I am using asp application to access this Data Base.
> I want to create a script to generate Data Base on other machines. I have
> some data in that Data Base, which I required to run my application.
> So the final SCRIPT should contain all relations, default values ,
> Identities and Data.
> Which method you advise to achieve these.
>
> Kind Regards
>
>
>
|||Try http://www.sqlscripter.com to script your data.
All types are supported: Insert, Update, Delete + Combinations.
It's free.
Thomas
"F@.yy@.Z" wrote:

> Hi All
> I have SQL Data Base . I am using asp application to access this Data Base.
> I want to create a script to generate Data Base on other machines. I have
> some data in that Data Base, which I required to run my application.
> So the final SCRIPT should contain all relations, default values ,
> Identities and Data.
> Which method you advise to achieve these.
>
> Kind Regards
>
>
>
sql

Generating SQL Script

Hi All
I have SQL Data Base . I am using asp application to access this Data Base.
I want to create a script to generate Data Base on other machines. I have
some data in that Data Base, which I required to run my application.
So the final SCRIPT should contain all relations, default values ,
Identities and Data.
Which method you advise to achieve these.
Kind RegardsHere is an article I wrote about generating SQL Server scripts. Hopefully
this will provide you with enough information to help you determine the best
way to generate your script.
http://www.dbazine.com/larsen4.shtml
----
----
--
Need SQL Server Examples check out my website at
http://www.geocities.com/sqlserverexamples
"F@.yy@.Z" <fayyaz.ahmed@.mvwebmaker.com> wrote in message
news:%23%23hi0yCaEHA.3112@.tk2msftngp13.phx.gbl...
> Hi All
> I have SQL Data Base . I am using asp application to access this Data
Base.
> I want to create a script to generate Data Base on other machines. I have
> some data in that Data Base, which I required to run my application.
> So the final SCRIPT should contain all relations, default values ,
> Identities and Data.
> Which method you advise to achieve these.
>
> Kind Regards
>
>
>|||Thanks for your reply.
I tried using enterprise manager but its not creating script for data in the
table, only creating script for table.
Kind Regards
"Gregory A. Larsen" <greg.larsen@.netzero.com> wrote in message
news:ubhT35CaEHA.3888@.TK2MSFTNGP10.phx.gbl...
> Here is an article I wrote about generating SQL Server scripts. Hopefully
> this will provide you with enough information to help you determine the
best
> way to generate your script.
> http://www.dbazine.com/larsen4.shtml
> --
> ----
--
> ----
--
> --
> Need SQL Server Examples check out my website at
> http://www.geocities.com/sqlserverexamples
> "F@.yy@.Z" <fayyaz.ahmed@.mvwebmaker.com> wrote in message
> news:%23%23hi0yCaEHA.3112@.tk2msftngp13.phx.gbl...
> Base.
have[vbcol=seagreen]
>|||Gregory's article shows you how to generate scripts for objects. One thing
to remember is SQL Server Enterprise Manager or DMO have no capabilities for
scripting the data in the form of INSERT statements.
I wrote a procedure to script data as insert statements, and it can be found
at: http://vyaskn.tripod.com/code.htm#inserts
Alternatively, to move entire database, look up BACKUP/RESTORE and attaching
and detaching databases in SQL Server Books Online.
--
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
"F@.yy@.Z" <fayyaz.ahmed@.mvwebmaker.com> wrote in message
news:%23%23hi0yCaEHA.3112@.tk2msftngp13.phx.gbl...
> Hi All
> I have SQL Data Base . I am using asp application to access this Data
Base.
> I want to create a script to generate Data Base on other machines. I have
> some data in that Data Base, which I required to run my application.
> So the final SCRIPT should contain all relations, default values ,
> Identities and Data.
> Which method you advise to achieve these.
>
> Kind Regards
>
>
>|||Sorry, my methods don't copy the data.
----
----
--
Need SQL Server Examples check out my website at
http://www.geocities.com/sqlserverexamples
"F@.yy@.Z" <fayyaz.ahmed@.mvwebmaker.com> wrote in message
news:Oa6i3HDaEHA.2016@.TK2MSFTNGP09.phx.gbl...
> Thanks for your reply.
> I tried using enterprise manager but its not creating script for data in
the
> table, only creating script for table.
> Kind Regards
>
>
> "Gregory A. Larsen" <greg.larsen@.netzero.com> wrote in message
> news:ubhT35CaEHA.3888@.TK2MSFTNGP10.phx.gbl...
Hopefully[vbcol=seagreen]
> best
> ----
> --
> ----
> --
> have
>|||Check out the free data and schema scripter from Innovartis - the makers of DB Ghost. [
url]www.dbghost.com[/url]
"F@.yy@.Z" wrote:

> Hi All
> I have SQL Data Base . I am using asp application to access this Data Base
.
> I want to create a script to generate Data Base on other machines. I have
> some data in that Data Base, which I required to run my application.
> So the final SCRIPT should contain all relations, default values ,
> Identities and Data.
> Which method you advise to achieve these.
>
> Kind Regards
>
>
>|||Try http://www.sqlscripter.com to script your data.
All types are supported: Insert, Update, Delete + Combinations.
It's free.
Thomas
"F@.yy@.Z" wrote:

> Hi All
> I have SQL Data Base . I am using asp application to access this Data Base
.
> I want to create a script to generate Data Base on other machines. I have
> some data in that Data Base, which I required to run my application.
> So the final SCRIPT should contain all relations, default values ,
> Identities and Data.
> Which method you advise to achieve these.
>
> Kind Regards
>
>
>

Generating GRANT EXECUTE Scripts

Hi All
I have a set of users and roles in a database permissions for various stored
procedures. I want to script these permissions but the default scripting
options don't give me what I want. Is there any way to script permissions
without having to write:
GRANT EXECUTE ON sproc TO user/role
for each stored proc for each user myself.
DaleUse sp_helptext to check out how MS coded the sp_helplogins, sp_helpsrvrole,
sp_helpsrvrolemember, sp_helpuser, sp_helprole, sp_helprolemember, and
sp_helprotect.
I'm sure you will want some variant combination of all of these.
Best of luck.
Sincerely,
Anthony Thomas
"Dale" <Dale@.discussions.microsoft.com> wrote in message
news:CFF9A6DA-98C4-4FCD-AC63-4BB67B522299@.microsoft.com...
Hi All
I have a set of users and roles in a database permissions for various stored
procedures. I want to script these permissions but the default scripting
options don't give me what I want. Is there any way to script permissions
without having to write:
GRANT EXECUTE ON sproc TO user/role
for each stored proc for each user myself.
Dale

Generating GRANT EXECUTE Scripts

Hi All
I have a set of users and roles in a database permissions for various stored
procedures. I want to script these permissions but the default scripting
options don't give me what I want. Is there any way to script permissions
without having to write:
GRANT EXECUTE ON sproc TO user/role
for each stored proc for each user myself.
Dale
Use sp_helptext to check out how MS coded the sp_helplogins, sp_helpsrvrole,
sp_helpsrvrolemember, sp_helpuser, sp_helprole, sp_helprolemember, and
sp_helprotect.
I'm sure you will want some variant combination of all of these.
Best of luck.
Sincerely,
Anthony Thomas

"Dale" <Dale@.discussions.microsoft.com> wrote in message
news:CFF9A6DA-98C4-4FCD-AC63-4BB67B522299@.microsoft.com...
Hi All
I have a set of users and roles in a database permissions for various stored
procedures. I want to script these permissions but the default scripting
options don't give me what I want. Is there any way to script permissions
without having to write:
GRANT EXECUTE ON sproc TO user/role
for each stored proc for each user myself.
Dale

Generate SQL Script

Hi All
I have SQL Data Base . I am using asp application to access this Data Base.
I want to create a script to generate Data Base on other machines. I have
some data in that Data Base, which I required to run my application.
So the final SCRIPT should contain all relations, default values ,
Identities and Data.
Which method you advise to achieve these.
Kind Regards
What version of SQL Server?
BACKUP and RESTORE
or
sp_detach_db and sp_attach_db
are the easiest methods to move a database
If you truly want to script the database you can use Enterprise Manager or
Query Analyzer to generate the appropriate scripts (to create the tables,
views, stored procedures, and so on). Scripting the data is a bit more
challenging, as you will need to use a tool that will script the data for
you. Some such tools are (in no particular order):
Largo SQL Tools -- http://www.largosqltools.com/
ObjectScripter -- http://www.rac4sql.net/
QALite -- http://www.rac4sql.net/
Lockwood Tech -- http://www.lockwoodtech.com/
Keith
"F@.yy@.Z" <fayyaz.ahmed@.mvwebmaker.com> wrote in message
news:uXQYlzCaEHA.3944@.tk2msftngp13.phx.gbl...
> Hi All
> I have SQL Data Base . I am using asp application to access this Data
Base.
> I want to create a script to generate Data Base on other machines. I have
> some data in that Data Base, which I required to run my application.
> So the final SCRIPT should contain all relations, default values ,
> Identities and Data.
> Which method you advise to achieve these.
>
> Kind Regards
>
>
|||I think sp_detach_db and sp_attach_db are best.
Just one more query can I use sp_attach_db in installshield as if I have
..ldf and .mdf files in my pakcge.
Kind Regards
"Keith Kratochvil" <sqlguy.back2u@.comcast.net> wrote in message
news:u0itCKDaEHA.1764@.TK2MSFTNGP10.phx.gbl...[vbcol=seagreen]
> What version of SQL Server?
> BACKUP and RESTORE
> or
> sp_detach_db and sp_attach_db
> are the easiest methods to move a database
> If you truly want to script the database you can use Enterprise Manager or
> Query Analyzer to generate the appropriate scripts (to create the tables,
> views, stored procedures, and so on). Scripting the data is a bit more
> challenging, as you will need to use a tool that will script the data for
> you. Some such tools are (in no particular order):
> Largo SQL Tools -- http://www.largosqltools.com/
> ObjectScripter -- http://www.rac4sql.net/
> QALite -- http://www.rac4sql.net/
> Lockwood Tech -- http://www.lockwoodtech.com/
> --
> Keith
>
> "F@.yy@.Z" <fayyaz.ahmed@.mvwebmaker.com> wrote in message
> news:uXQYlzCaEHA.3944@.tk2msftngp13.phx.gbl...
> Base.
have
>
|||I don't see why not.
Keith
"F@.yy@.Z" <fayyaz.ahmed@.mvwebmaker.com> wrote in message
news:e4VjsvLaEHA.2388@.TK2MSFTNGP09.phx.gbl...[vbcol=seagreen]
> I think sp_detach_db and sp_attach_db are best.
> Just one more query can I use sp_attach_db in installshield as if I have
> .ldf and .mdf files in my pakcge.
>
> Kind Regards
>
>
>
> "Keith Kratochvil" <sqlguy.back2u@.comcast.net> wrote in message
> news:u0itCKDaEHA.1764@.TK2MSFTNGP10.phx.gbl...
or[vbcol=seagreen]
tables,[vbcol=seagreen]
for
> have
>

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...
>
>