MS SQL XML - I need to produce 5 reports from SQL Server 2005 XML

Author

I need to produce 5 reports from SQL Server 2005 XML

Cloudfall

2005-12-06, 3:23 am

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/ BuyerChangeOrderNumb
er)[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 Malcolm

2005-12-06, 9:24 am

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@hotm
ail.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/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/ BuyerChangeOrderNumb
er)[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.
>

Cloudfall

2005-12-06, 8:25 pm

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.

Sponsored Links