Get back data from Reports

I have just started working with RS 2005 and was wondering if / how I can do the following.

I created an asp.net website that has a report viewer to show my reports on the web.
In addition there is a page that lists all of the reports a user has access to, which is pretty much a series of links:

Report 1
Report 2
Report 3
.....

Now in my reports I generally create a grand total of one of the column and was wondering if there was some way to get that information back into an asp page, ie:

Report 1 - $125,713
Report 2 - ($23,111)
Report 3 - $1,762,142

I was thinking if there was some way to send a parameter to the report like Summary=True which would tell it to just return a XML bit of data holding the total for that report (Anything will work, doesn't have to be XML). So the page with the report links would call each of the reports.

I realize that it would probably be possible to do this through a stored procedure, but that would just create another point of maintenace. Instead I could create some function in all of my reports and have a standardized way of doing this so my webserver or database wouldnt have to be updated all the time.

Thanks, and let me know if there is any other information I can provide to make this clearer.

Try using the XML renderer. You will get a "report" which is really just an XML stream with the data. You can then XPath into it to get the values you are interested in.|||

Sounds promising. Being rather new to reports, are there any pages you can think of off hand that provide examples or explanations?

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 SQL-Skripts

Hi,
i'm sitting in front of an obscure database containing round about 400
views. :-)
I would like to export these views into an working SQL-Skript to be able
to recreate these views on a second database with an identical
table-structure.
The problem: The views are dumped to the file in the wrong order. Do you
know
this problem? What can I do?
Thanks for any hints...
Jan
Hi,
Are you going to replicate the Table and View into the new database or just
the views.
1.
If you are going to create only the views in the new database then you have
to change the script for view creation. Because your real table will
be in the old database.
it should be some thing like
Create view V1_customer as select * from olddbname..table_name
2.
Incase if you are moving both Table and the view, then create 2 scripts. 1
for tables and the other for views.
Execute the Tables first after then run the script contains the view.
Did I answered your question completely?
Thanks
Hari
MCDBA
"Jan Schmitz" <schmitz@.face.de> wrote in message
news:Oz4KJGLGEHA.712@.tk2msftngp13.phx.gbl...
> Hi,
> i'm sitting in front of an obscure database containing round about 400
> views. :-)
> I would like to export these views into an working SQL-Skript to be able
> to recreate these views on a second database with an identical
> table-structure.
> The problem: The views are dumped to the file in the wrong order. Do you
> know
> this problem? What can I do?
> Thanks for any hints...
> Jan
>
|||check out www.dbghost.com
|||"Hari Prasad" <hari_prasad_k@.hotmail.com> schrieb im Newsbeitrag
news:%23W$dU5LGEHA.3940@.tk2msftngp13.phx.gbl...
> Hi,
> Incase if you are moving both Table and the view, then create 2 scripts. 1
> for tables and the other for views.
> Execute the Tables first after then run the script contains the view.
>
Hi,
this is what i tried. The problem ist, that the 400 Views in the
Views-Skript are dumped in the wrong order. View "B", selecting from View
"A" is created before View "A" ist created. So the generated skript simply
doesn't work. I think, what I need is a way to renew the sysdepends...
Greetings...Jan
|||"mark baekdal" <anonymous@.discussions.microsoft.com> schrieb im Newsbeitrag
news:BED6BA68-89A6-42AC-A075-FF67F35D86A5@.microsoft.com...
> check out www.dbghost.com
Hi,
at first look, this sounds great - I will take a closer look at that tool...
Thanks a lot,
Jan
sql

Generating reports on very large and "live" databases

Hello All,
I am working on a application which has a table with 100 million
records.
A background process keeps inserting 5 rows per second into this
table.
My client is generating reports from that table. They are interested
in seeing reports with real time data. This means that as soon as a
record is inserted into the table, the reports should process that
record as well.
At the max around 20K records are processed to generate a report. When
this happens it takes simply 30 min to generate a report. Upon
analysis I find that since the table is so huge, the indexes on those
table is also very huge.
What can I do to fix this, I have thought about denormalizing the
table. But the some programmers say that picking up data from one
table is better because doing joins on multiple tables will be even
slower.
Another approach is to bring in a data warehouse but I don't know much
about this except what I learnt in MSDN session about creating of data
cubes. But I suppose cube can be created only when data is static. but
in my case new records are inserted every second and they are to be
included in the report.
The 3rd approach is that I create report specific tables and create a
trigger (or a C programm which polls for changes in main table) and
every time new records are inserted into the main table, I preprocess
them. Then when the users make a request for the report I generate my
report from the preprossed table.
But I feel the trigger will be fired to many times and if the number
of reports are significant (> 35) then trigger/C program could become
a bottleneck itself.
What should I do? it is such a tricky problem.
Please help me and give me some advice. Thank you for your help.
regards,
Abhishektable size (or row count) is irrelevent unless a
table/index scan is involved.
5 rows/sec insert is a negligible load on the system.
however, a report that involves 20k rows with good indexes
should not take 30min to run.
1) what is the query,
2) what are the indexes on this table
3) what does the execution plan show?
(indexes used, type of operation, rows involved, costs)
>--Original Message--
>Hello All,
>I am working on a application which has a table with 100
million
>records.
>A background process keeps inserting 5 rows per second
into this
>table.
>My client is generating reports from that table. They are
interested
>in seeing reports with real time data. This means that as
soon as a
>record is inserted into the table, the reports should
process that
>record as well.
>At the max around 20K records are processed to generate a
report. When
>this happens it takes simply 30 min to generate a report.
Upon
>analysis I find that since the table is so huge, the
indexes on those
>table is also very huge.
>What can I do to fix this, I have thought about
denormalizing the
>table. But the some programmers say that picking up data
from one
>table is better because doing joins on multiple tables
will be even
>slower.
>Another approach is to bring in a data warehouse but I
don't know much
>about this except what I learnt in MSDN session about
creating of data
>cubes. But I suppose cube can be created only when data
is static. but
>in my case new records are inserted every second and they
are to be
>included in the report.
>The 3rd approach is that I create report specific tables
and create a
>trigger (or a C programm which polls for changes in main
table) and
>every time new records are inserted into the main table,
I preprocess
>them. Then when the users make a request for the report
I generate my
>report from the preprossed table.
>But I feel the trigger will be fired to many times and if
the number
>of reports are significant (> 35) then trigger/C program
could become
>a bottleneck itself.
>What should I do? it is such a tricky problem.
>Please help me and give me some advice. Thank you for
your help.
>regards,
>Abhishek
>.
>

Generated Report Model

I'm working on a software project that requires a reporting solution.
Of all the solutions we have looked at RS2005 seems to fit best with
our product, however, I had a few queries.
1. Our data is stored in a non-relation format but for reporting the
end user can select what data the wish to report on and a relational
table model is created. Because the table structure is not known until
this point, is it possible to create a Report Data Model in code so
that we can use the end user report designer.
2. Because of the JIT generation of the relational tables, the table
names are not known until the report is requested, is it possible to
pass the table names into the report as parameters? Is it possible to
use these parameters in th Report Data Model?
3. Is it possible to get column totals and group sub-totals in reports
designed in the Report Builder end user designer?
4. Is it (going to be) possible to design free flow reports in the
Report Builder rather than just table, matrix or chart reports?
Many thanks,
Adrian RitchieHi Adrian,
not knowing anything at all about your project...have you considered
using a separate data warehouse for the reporting solution and
propagating changes from the operational system to the BI System so
that you have access to a relational database for reporting...also,
your reporting and analysis needs are very likely to grow past simple
reporting, and I would think you are building in rather sever
limitations trying to write a JIT style application to generate tables
for reporting....I do not see how it could possibly scale as you must
JIT push data into tables when a report is requested rather than just
reading a table that is already there...'
Peter
www.peternolan.com|||Hi Peter,
I realise that my explaination of our system is rather vague but that
was as much as I could really write without breaching my NDA :(
Having a separate data warehouse sounds like a good idea and I will run
it past some of the other guys here but I fear may not be practical.
The reason the relational model is not generated until reports are
requested is that:
1. The data storage structure is flexible. We ship out a standard
structure but clients can modify and add to it as they see fit (with
appropriate security rights).
2. The data in the system is temporal. The end user can request a
report for any point in time for which there is data in the system.
The point in time that the user selects will determine what database
structure they get (as the structure may change over time).
The second problem may not be a problem at all, it's just that I
haven't thought of another solution for it, so any suggestions will be
greatfully received.
Adrian Ritchie|||Hi Adrian,
I understand the world of NDAs...no problems...
1. Changes to source data structures for applications rolled out is
always a problem but not a hard one to solve...
2. All data structures are 'temporal' it's just that most people don't
know it and think they don't change. They do and the analysis system
must take care of that...
I've done systems easily handling both 1 and 2......so I still think
that unless you have some really good reasons for building the
limitations into your reporting system that you very likely will do
taking a look at something separate would be a good idea...Like I said,
I know nothing about what you are doing...so these are just my
thoughts...
Peter
www.peternolan.com

Generate sql script?

The web site I am building is working fine locally, but I am hitting some problems with setting it up on a remote hosting server.

First off, how can I generate the sql script to populate the SQL db on the remote server?
I am using VS 2005 Standard. Do I need to d/l the SQL Server Express?

Once I get that going, I should be able to figure out the rest...but I'll prolly have another question or two.

Thanks

Hi

To generate sql script ,you can take a look at this :How to: Generate a Script (SQL Server Management Studio)

|||

I suggest you install Management Studio Express to mange SQL Express databases if you haven't installed it yet, you can download it here:

http://msdn.microsoft.com/vstudio/express/sql/download/

generate attributelist from tables

hi there!
I've been working on a project in ASP.NET and SQLserver 2000.
Since this is actually something for school, I must give a report on my work done here.
What I want to do is make an attributelist of all the tables that are in a database, or one at the time would already be a great help!
So for each table I would need in text format all its column names, types en 'allow null' property, but I just can't find out how or with what to do this.

thanks for your help!
Tombaselect * from INFORMATION_SCHEMA.COLUMNS

Generate a GUID in MS SQL (t/SQL)

Hello,
I'm writing a MS SQL script to roll-up an Event List in Sharepoint, and
I have it working except I haven't found a way to generate a GUID using
t/SQL in MS SQL. Is there a function or script or something I can use
to generate a GUID value? I can do this in ASP or ColdFusion or a few
other ways, but keeping this totally in MS SQL as a stored procedure I
can schedule to run periodically would be the simplest.
Thanks for any suggestions or ideas on this.
Also, from a Sharepoint standpoint, SPS nor WSS supports roll-ups for
Events or any list, which is why I'm writing this on my own. Also
there are third party plugins, but most are around $1600 or more, which
is crazy since this can be done in MS SQL if I can only figure out the
GUID bit.
Thanks for any help or assistance --
SamSam
Try newid(), I don't have access to the query analyzer tonight, but
from memory it should a litlte like:
<sql snippet>
DECLARE @.MyUUID AS uniqueidentifier
SET @.MyUUID = newid()
SELECT @.MyUUID
</sql:snippet>
HTH,
Duncan.|||Have you tried this method?
SELECT NEWID()
Keith Kratochvil
"Alex" <samalex@.gmail.com> wrote in message
news:1141685000.839522.40640@.i39g2000cwa.googlegroups.com...
> Hello,
> I'm writing a MS SQL script to roll-up an Event List in Sharepoint, and
> I have it working except I haven't found a way to generate a GUID using
> t/SQL in MS SQL. Is there a function or script or something I can use
> to generate a GUID value? I can do this in ASP or ColdFusion or a few
> other ways, but keeping this totally in MS SQL as a stored procedure I
> can schedule to run periodically would be the simplest.
> Thanks for any suggestions or ideas on this.
> Also, from a Sharepoint standpoint, SPS nor WSS supports roll-ups for
> Events or any list, which is why I'm writing this on my own. Also
> there are third party plugins, but most are around $1600 or more, which
> is crazy since this can be done in MS SQL if I can only figure out the
> GUID bit.
> Thanks for any help or assistance --
> Sam
>

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