I need to produce 5 reports from SQL Server 2005 XML

Hi. I have been given the job of producing 5 reports from a SQL Server
2005 (beta) XML database. I barely have an idea what to do. There are
existing reports that don't work properly, produced by an employee who
will no longer be working for his manager (for various reasons, these
reports being one), the same manager who has tasked me to do these
reports for him. I thought I might start with the existing reports and
see if I can make them work. The existing reports are some kind of SQL
but I don't know what sort. Can someone identify what sort of SQL the
code is and point me to where I might learn about it, please? Following
is an example of one of the reports copied from Visual Studio:
--Purchase Orders--
SELECT
--document type
[Type],
--document id
[Message].value('(/Order/OrderHeader/OrderNumber/BuyerOrderNumber)[1]',
'nvarchar(50)') AS id,
--issue date
[Message].value('(/Order/OrderHeader/OrderIssueDate)[1]',
'nvarchar(50)') AS issue_date,
--agency name
[Message].value('(/Order/OrderHeader/OrderParty/BuyerParty/Party/NameAddress/Name1)[1]',
'nvarchar(50)') AS agency_name,
--department name
[Message].value('(/Order/OrderHeader/OrderParty/ShipToParty/Party/NameAddress/Name2)[1]',
'nvarchar(50)') AS department_name,
--buyer name
[Message].value('(/Order/OrderHeader/OrderParty/BuyerParty/Party/OrderContact/Contact/ContactName)[1]',
'nvarchar(50)') AS buyer_name,
--seller name
[Message].value('(/Order/OrderHeader/OrderParty/SellerParty/Party/NameAddress/Name1)[1]',
'nvarchar(50)') AS seller_name,
--total
[Message].value('(/Order/OrderSummary/TotalAmount/MonetaryValue/MonetaryAmount)[1]',
'decimal(18,2)') AS total
FROM [test].[dbo].[TableXML]
WHERE Type='Purchase Order'
UNION ALL
--Purchase Orders Changes--
SELECT
--document type
[Type],
--document id
[Message].value('(/ChangeOrder/ChangeOrderHeader/ChangeOrderNumber/BuyerChangeOrderNumber)[1]',
'nvarchar(50)') AS id,
--issue date
[Message].value('(/ChangeOrder/ChangeOrderHeader/ChangeOrderIssueDate)[1]',
'nvarchar(50)') AS issue_date,
--agency name
[Message].value('(/ChangeOrder/ChangeOrderHeader/BuyerParty/Party/NameAddress/Name1)[1]',
'nvarchar(50)') AS agency_name,
--department name
[Message].value('(/ChangeOrder/ChangeOrderHeader/ShipToParty/Party/NameAddress/Name2)[1]',
'nvarchar(50)') AS department_name,
--buyer name
[Message].value('(/ChangeOrder/ChangeOrderHeader/BuyerParty/Party/OrderContact/Contact/ContactName)[1]',
'nvarchar(50)') AS buyer_name,
--seller name
[Message].value('(/ChangeOrder/ChangeOrderHeader/SellerParty/Party/NameAddress/Name1)[1]',
'nvarchar(50)') AS seller_name,
--total
[Message].value('(/ChangeOrder/ChangeOrderSummary/RevisedOrderSummary/OrderSummary/TotalAmount/MonetaryValue/MonetaryAmount)[1]',
'decimal(18,2)') AS total
FROM [test].[dbo].[TableXML]
WHERE Type='Purchase Order Change'
Thanking you now for your responses.
Regards,
Clueless.
The T-SQL here uses the various methods supported by the new xml data type
in SQL Server 2005 (your "Message" column is obviously a column of type
"xml". Try http://msdn.microsoft.com/SQL/learn/...l/default.aspx as a
starting point for learning about this. It's also covered quite well in SQL
Server Books Online.
Cheers,
G
Cheers,
Graeme
_____________________
Graeme Malcolm
Principal Technologist
Content Master
- a member of CM Group
www.contentmaster.com
"Cloudfall" <SydneyCloudfall@.hotmail.com> wrote in message
news:1133836493.347432.246890@.f14g2000cwb.googlegr oups.com...
> Hi. I have been given the job of producing 5 reports from a SQL Server
> 2005 (beta) XML database. I barely have an idea what to do. There are
> existing reports that don't work properly, produced by an employee who
> will no longer be working for his manager (for various reasons, these
> reports being one), the same manager who has tasked me to do these
> reports for him. I thought I might start with the existing reports and
> see if I can make them work. The existing reports are some kind of SQL
> but I don't know what sort. Can someone identify what sort of SQL the
> code is and point me to where I might learn about it, please? Following
> is an example of one of the reports copied from Visual Studio:
> --
> --Purchase Orders--
> --
> SELECT
> --document type
> [Type],
> --document id
> [Message].value('(/Order/OrderHeader/OrderNumber/BuyerOrderNumber)[1]',
> 'nvarchar(50)') AS id,
> --issue date
> [Message].value('(/Order/OrderHeader/OrderIssueDate)[1]',
> 'nvarchar(50)') AS issue_date,
> --agency name
> [Message].value('(/Order/OrderHeader/OrderParty/BuyerParty/Party/NameAddress/Name1)[1]',
> 'nvarchar(50)') AS agency_name,
> --department name
> [Message].value('(/Order/OrderHeader/OrderParty/ShipToParty/Party/NameAddress/Name2)[1]',
> 'nvarchar(50)') AS department_name,
> --buyer name
> [Message].value('(/Order/OrderHeader/OrderParty/BuyerParty/Party/OrderContact/Contact/ContactName)[1]',
> 'nvarchar(50)') AS buyer_name,
> --seller name
> [Message].value('(/Order/OrderHeader/OrderParty/SellerParty/Party/NameAddress/Name1)[1]',
> 'nvarchar(50)') AS seller_name,
> --total
> [Message].value('(/Order/OrderSummary/TotalAmount/MonetaryValue/MonetaryAmount)[1]',
> 'decimal(18,2)') AS total
> FROM [test].[dbo].[TableXML]
> WHERE Type='Purchase Order'
> UNION ALL
> --
> --Purchase Orders Changes--
> --
> SELECT
> --document type
> [Type],
> --document id
> [Message].value('(/ChangeOrder/ChangeOrderHeader/ChangeOrderNumber/BuyerChangeOrderNumber)[1]',
> 'nvarchar(50)') AS id,
> --issue date
> [Message].value('(/ChangeOrder/ChangeOrderHeader/ChangeOrderIssueDate)[1]',
> 'nvarchar(50)') AS issue_date,
> --agency name
> [Message].value('(/ChangeOrder/ChangeOrderHeader/BuyerParty/Party/NameAddress/Name1)[1]',
> 'nvarchar(50)') AS agency_name,
> --department name
> [Message].value('(/ChangeOrder/ChangeOrderHeader/ShipToParty/Party/NameAddress/Name2)[1]',
> 'nvarchar(50)') AS department_name,
> --buyer name
> [Message].value('(/ChangeOrder/ChangeOrderHeader/BuyerParty/Party/OrderContact/Contact/ContactName)[1]',
> 'nvarchar(50)') AS buyer_name,
> --seller name
> [Message].value('(/ChangeOrder/ChangeOrderHeader/SellerParty/Party/NameAddress/Name1)[1]',
> 'nvarchar(50)') AS seller_name,
> --total
> [Message].value('(/ChangeOrder/ChangeOrderSummary/RevisedOrderSummary/OrderSummary/TotalAmount/MonetaryValue/MonetaryAmount)[1]',
> 'decimal(18,2)') AS total
> FROM [test].[dbo].[TableXML]
> WHERE Type='Purchase Order Change'
> Thanking you now for your responses.
> Regards,
> Clueless.
>
|||Graeme,
Thank you for your response. Yes, you are completely correct, the
"Message" column is a column of type "xml". Actually, I am in "SQL
Server Management Studio" and in the "Object Explorer" I see
"Databases", and under this there are "AdventureWorks (Offline)",
"AdventureWorksDW (Offline)", and "test", among others. Under "test" I
have one familiar thing and a whole bunch of (to me) unfamiliar things:
Familiar- Tables, Unfamiliar- Database Diagrams, Views, Synonyms,
Programmability, Service Broker, Storage and Security. Under "Tables" I
have "System Tables" and "dbo.TableXML". Under "dbo.TableXML" I have
Columns, Keys, Constraints, Triggers, Indexes and Statistics. And,
finally, under "Columns" I have "Type (char(50), null)" and
"Message(XML(.), null)". Most of this is an alien world to me. At this
stage, I still don't know if I will be able to produce the reports I
require.
Thank you again. At least now I know this is T-SQL.
Regards,
Clueless.

I need to produce 5 reports from SQL Server 2005 XML

Hi. I have been given the job of producing 5 reports from a SQL Server
2005 (beta) XML database. I barely have an idea what to do. There are
existing reports that don't work properly, produced by an employee who
will no longer be working for his manager (for various reasons, these
reports being one), the same manager who has tasked me to do these
reports for him. I thought I might start with the existing reports and
see if I can make them work. The existing reports are some kind of SQL
but I don't know what sort. Can someone identify what sort of SQL the
code is and point me to where I might learn about it, please? Following
is an example of one of the reports copied from Visual Studio:
--Purchase Orders--
--
SELECT
--document type
[Type],
--document id
[Message].value('(/Order/OrderHeader/OrderNumber/BuyerOrderNumber)[1]',
'nvarchar(50)') AS id,
--issue date
[Message].value('(/Order/OrderHeader/OrderIssueDate)[1]',
'nvarchar(50)') AS issue_date,
--agency name
[Message].value('(/Order/OrderHeader/OrderParty/BuyerParty/Party/NameAddress/Name1)[1
]',
'nvarchar(50)') AS agency_name,
--department name
[Message].value('(/Order/OrderHeader/OrderParty/ShipToParty/Party/NameAddress/Name2)[
1]',
'nvarchar(50)') AS department_name,
--buyer name
[Message].value('(/Order/OrderHeader/OrderParty/BuyerParty/Party/OrderContact/Contact
/ContactName)[1]',
'nvarchar(50)') AS buyer_name,
--seller name
[Message].value('(/Order/OrderHeader/OrderParty/SellerParty/Party/NameAddress/Name1)[
1]',
'nvarchar(50)') AS seller_name,
--total
[Message].value('(/Order/OrderSummary/TotalAmount/MonetaryValue/MonetaryAmount)[1]',
'decimal(18,2)') AS total
FROM [test].[dbo].[TableXML]
WHERE Type='Purchase Order'
UNION ALL
--Purchase Orders Changes--
--
SELECT
--document type
[Type],
--document id
[Message].value('(/ChangeOrder/ChangeOrderHeader/ChangeOrderNumber/BuyerChangeOrderNu
mber)[1]',
'nvarchar(50)') AS id,
--issue date
[Message].value('(/ChangeOrder/ChangeOrderHeader/ChangeOrderIssueDate)[1]',
'nvarchar(50)') AS issue_date,
--agency name
[Message].value('(/ChangeOrder/ChangeOrderHeader/BuyerParty/Party/NameAddress/Name1)[
1]',
'nvarchar(50)') AS agency_name,
--department name
[Message].value('(/ChangeOrder/ChangeOrderHeader/ShipToParty/Party/NameAddress/Name2)
[1]',
'nvarchar(50)') AS department_name,
--buyer name
[Message].value('(/ChangeOrder/ChangeOrderHeader/BuyerParty/Party/OrderContact/Contac
t/ContactName)[1]',
'nvarchar(50)') AS buyer_name,
--seller name
[Message].value('(/ChangeOrder/ChangeOrderHeader/SellerParty/Party/NameAddress/Name1)
[1]',
'nvarchar(50)') AS seller_name,
--total
[Message].value('(/ChangeOrder/ChangeOrderSummary/RevisedOrderSummary/OrderSummary/To
talAmount/MonetaryValue/MonetaryAmount)[1]',
'decimal(18,2)') AS total
FROM [test].[dbo].[TableXML]
WHERE Type='Purchase Order Change'
Thanking you now for your responses.
Regards,
Clueless.The T-SQL here uses the various methods supported by the new xml data type
in SQL Server 2005 (your "Message" column is obviously a column of type
"xml". Try http://msdn.microsoft.com/SQL/learn...ml/default.aspx as a
starting point for learning about this. It's also covered quite well in SQL
Server Books Online.
Cheers,
G
--
Cheers,
Graeme
_____________________
Graeme Malcolm
Principal Technologist
Content Master
- a member of CM Group
www.contentmaster.com
"Cloudfall" <SydneyCloudfall@.hotmail.com> wrote in message
news:1133836493.347432.246890@.f14g2000cwb.googlegroups.com...
> Hi. I have been given the job of producing 5 reports from a SQL Server
> 2005 (beta) XML database. I barely have an idea what to do. There are
> existing reports that don't work properly, produced by an employee who
> will no longer be working for his manager (for various reasons, these
> reports being one), the same manager who has tasked me to do these
> reports for him. I thought I might start with the existing reports and
> see if I can make them work. The existing reports are some kind of SQL
> but I don't know what sort. Can someone identify what sort of SQL the
> code is and point me to where I might learn about it, please? Following
> is an example of one of the reports copied from Visual Studio:
> --
> --Purchase Orders--
> --
> SELECT
> --document type
> [Type],
> --document id
> [Message].value('(/Order/OrderHeader/OrderNumber/BuyerOrderNumber)[1]',
> 'nvarchar(50)') AS id,
> --issue date
> [Message].value('(/Order/OrderHeader/OrderIssueDate)[1]',
> 'nvarchar(50)') AS issue_date,
> --agency name
> [Message].value('(/Order/OrderHeader/OrderParty/BuyerParty/Party/NameAddress/Name1)
[1]',
> 'nvarchar(50)') AS agency_name,
> --department name
> [Message].value('(/Order/OrderHeader/OrderParty/ShipToParty/Party/NameAddress/Name2
)[1]',
> 'nvarchar(50)') AS department_name,
> --buyer name
> [Message].value('(/Order/OrderHeader/OrderParty/BuyerParty/Party/OrderContact/Conta
ct/ContactName)[1]',
> 'nvarchar(50)') AS buyer_name,
> --seller name
> [Message].value('(/Order/OrderHeader/OrderParty/SellerParty/Party/NameAddress/Name1
)[1]',
> 'nvarchar(50)') AS seller_name,
> --total
> [Message].value('(/Order/OrderSummary/TotalAmount/MonetaryValue/MonetaryAmount)[1]'
,
> 'decimal(18,2)') AS total
> FROM [test].[dbo].[TableXML]
> WHERE Type='Purchase Order'
> UNION ALL
> --
> --Purchase Orders Changes--
> --
> SELECT
> --document type
> [Type],
> --document id
> [Message].value('(/ChangeOrder/ChangeOrderHeader/ChangeOrderNumber/BuyerChangeOrder
Number)[1]',
> 'nvarchar(50)') AS id,
> --issue date
> [Message].value('(/ChangeOrder/ChangeOrderHeader/ChangeOrderIssueDate)[1]',
> 'nvarchar(50)') AS issue_date,
> --agency name
> [Message].value('(/ChangeOrder/ChangeOrderHeader/BuyerParty/Party/NameAddress/Name1
)[1]',
> 'nvarchar(50)') AS agency_name,
> --department name
> [Message].value('(/ChangeOrder/ChangeOrderHeader/ShipToParty/Party/NameAddress/Name
2)[1]',
> 'nvarchar(50)') AS department_name,
> --buyer name
> [Message].value('(/ChangeOrder/ChangeOrderHeader/BuyerParty/Party/OrderContact/Cont
act/ContactName)[1]',
> 'nvarchar(50)') AS buyer_name,
> --seller name
> [Message].value('(/ChangeOrder/ChangeOrderHeader/SellerParty/Party/NameAddress/Name
1)[1]',
> 'nvarchar(50)') AS seller_name,
> --total
> [Message].value('(/ChangeOrder/ChangeOrderSummary/RevisedOrderSummary/OrderSummary/
TotalAmount/MonetaryValue/MonetaryAmount)[1]',
> 'decimal(18,2)') AS total
> FROM [test].[dbo].[TableXML]
> WHERE Type='Purchase Order Change'
> Thanking you now for your responses.
> Regards,
> Clueless.
>|||Graeme,
Thank you for your response. Yes, you are completely correct, the
"Message" column is a column of type "xml". Actually, I am in "SQL
Server Management Studio" and in the "Object Explorer" I see
"Databases", and under this there are "AdventureWorks (Offline)",
"AdventureWorksDW (Offline)", and "test", among others. Under "test" I
have one familiar thing and a whole bunch of (to me) unfamiliar things:
Familiar- Tables, Unfamiliar- Database Diagrams, Views, Synonyms,
Programmability, Service Broker, Storage and Security. Under "Tables" I
have "System Tables" and "dbo.TableXML". Under "dbo.TableXML" I have
Columns, Keys, Constraints, Triggers, Indexes and Statistics. And,
finally, under "Columns" I have "Type (char(50), null)" and
"Message(XML(.), null)". Most of this is an alien world to me. At this
stage, I still don't know if I will be able to produce the reports I
require.
Thank you again. At least now I know this is T-SQL.
Regards,
Clueless.

i need to migrate from sql 2005 express to sql workgroup edition

hello,

i have no idea what i am doing here and could use some help from someone who knows what they are doing.

i have a proliant ml350 g3 server running windows 2003 server with sql express 2005 software.

all the databases are running in compatibility sql 7 mode.

I would like them to be in sql 2005 mode if possible. Is there a way to upgrade the databases in the process?

here's a plan I came up with:

take sql workgroup 2005 edition evaluation, install it on a machine.

Mount all databases on the machine.

Wipe out the sql express 2005 machine, reinstall workgroup edition on the new server.

but here is my question

is there a way to migrate the data from the workgroup edition over to the new server through a DTS copy process, and if so, will it create a new version of the databases with the proper collation or the proper english?

please let me know. if this works, i will be thrilled!

First, you can upgrade in place by installing workgroup over Express on the same machine. Make sure you indicate you want to upgrade by selecting the Express instance name at the appropriate point during the install.

If you do want to switch machines as part of the upgrade, you can either copy the database files (both .mdf and .ldf) manually or use DTS. Either way, I suspect you're likely to lose logins and some other info - but I'm not a DTS expert so I may be misspeaking. You should post a question in that forum if you are going to consider using DTS.

Books On Line (BOL) has a good description of how to go from one compatibility mode to another. Use the sp_dbcmptlevel stored procedure, either before or after the upgrade.

Paul

|||

one more question Paul if you don't mind.

Does the BOL Books on Line article show how to change from the existing collation? I apparently have a "conflict"

when I start the SQL Server Management Studio and right click on any of my databases, they show this error:

"Cannont Show requested dialog.

ADDITIONAL INFORMATION:

Cannot show requested dialog. (Microsoft.SqlServer.Express.SqlMgmt)

-> an exception occured while executing a Transact-SQL statement or batch.

(Microsoft.SqlServer.Express.ConnectionInfo)

--> Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CI_AS" and "Compatibility_52_409_30003" in the equal to operation. (Microsoft SQL Server, Error: 468)

I have no idea what this means, which is why I had the idea to transform the packages using DTS?

please let me know if you have an answer. If this is for another forum, please let me know that. Thanks for your help.

|||

I would post the question about collation in the tools forum. Are Management Studio and Express on the same box? I have no idea what would cause such an error.

Paul

|||

thanks this has helped tremendeously.

I still have problems with collation but it looks like the SQL Server default collation and the master/temp/model db are different and this is causing problems.

I need to find a tool to rebuild the master and temp and model db.

One came with SQL 2000 but I do not have one for SQL 2005.

I am still searching for rbuild.exe to use.

hopefully I'll find it!

thanks,

j

i need idea for bus transport databse

i have many buses that drive between many cities. some of the buses connect
more than 2 cities in one line...Homework assignment?
Adam Machanic
SQL Server MVP
http://www.datamanipulation.net
--
"TomislaW" <tomislav147@.hotmail.com> wrote in message
news:%23WxwyI7LFHA.2384@.tk2msftngp13.phx.gbl...
> i have many buses that drive between many cities. some of the buses
connect
> more than 2 cities in one line...
>|||Homework or do you want to hire someone?
Take a look at:
http://www.databaseanswers.org/data_models/index.htm
David Portas
SQL Server MVP
--|||Hi
<http://www.databaseanswers.com/data_models/index.htm> -- examples
database design
"TomislaW" <tomislav147@.hotmail.com> wrote in message
news:%23WxwyI7LFHA.2384@.tk2msftngp13.phx.gbl...
> i have many buses that drive between many cities. some of the buses
connect
> more than 2 cities in one line...
>|||Without knowing more (and really understanding your need) it will be
difficult to make recommendations. Look at the things that you need to
track. Group them logically. Some things that come to mind are:
Bus (one row per bus)
Location (stops)
Route (itinerary -- stops that make up a trip)
Passenger
Keith
"TomislaW" <tomislav147@.hotmail.com> wrote in message
news:%23WxwyI7LFHA.2384@.tk2msftngp13.phx.gbl...
> i have many buses that drive between many cities. some of the buses
connect
> more than 2 cities in one line...
>|||Check out the 6th installment of this MSDN article on data structures;
particularly about Graphs...
http://msdn.microsoft.com/vcsharp/d...uctures20_1.asp
"Keith Kratochvil" wrote:

> Without knowing more (and really understanding your need) it will be
> difficult to make recommendations. Look at the things that you need to
> track. Group them logically. Some things that come to mind are:
> Bus (one row per bus)
> Location (stops)
> Route (itinerary -- stops that make up a trip)
> Passenger
> --
> Keith
>
> "TomislaW" <tomislav147@.hotmail.com> wrote in message
> news:%23WxwyI7LFHA.2384@.tk2msftngp13.phx.gbl...
> connect
>|||>> Homework assignment? <<
This is one I used to give students myself! The gimmick was adding the
temporal parts to the graph.