Is there some way of storing XML in SQL Server 2005 as "pure" XML as,
say, an XML order per record (unshredded to extract the original data )
and then querying the XML to get aggregate information on individual
orders (e.g. sum of cost of all items in order) and then further
aggregating the information to get, say, quarterly results (e.g. sum of
cost of all items ordered from January 2005 to March 2005)? If the
answer to this is yes, and I don't expect it to be, how is this done
(i.e. can someone point me to information on how I could implement such
a thing)? Believe it or not, we can only get this information in XML
format. We would have to pay someone to design a relational database to
take this information, write a script to unshred the data from the XML
and populate the database tables, and then write queries to extract
aggregate data from the database.
You can store the data as XML by using the new xml data type. This data
type has various methods that you can use to query the data. The methods
include some XQuery based functions to extract information from an xml
column or variable (the "query", "value", and "exist" methods), which might
help if your aggergated data also needs to be in XML format. Alternatively,
theres a "nodes" method that you can use to extract a rowset from an xml
value (or you could use OPENXML). You can even create indexes on the xml
columns to improve XQuery performance.
All of this is described in SQL Server Books Online, and
http://msdn.microsoft.com/sql/learn/...l/default.aspx is a good place to
start.
-
Cheers,
Graeme
_____________________
Graeme Malcolm
Principal Technologist
Content Master
- a member of CM Group
www.contentmaster.com
"Cloudfall" <SydneyCloudfall@.hotmail.com> wrote in message
news:1132716530.406874.269410@.f14g2000cwb.googlegr oups.com...
> Is there some way of storing XML in SQL Server 2005 as "pure" XML as,
> say, an XML order per record (unshredded to extract the original data )
> and then querying the XML to get aggregate information on individual
> orders (e.g. sum of cost of all items in order) and then further
> aggregating the information to get, say, quarterly results (e.g. sum of
> cost of all items ordered from January 2005 to March 2005)? If the
> answer to this is yes, and I don't expect it to be, how is this done
> (i.e. can someone point me to information on how I could implement such
> a thing)? Believe it or not, we can only get this information in XML
> format. We would have to pay someone to design a relational database to
> take this information, write a script to unshred the data from the XML
> and populate the database tables, and then write queries to extract
> aggregate data from the database.
>
|||"Cloudfall" <SydneyCloudfall@.hotmail.com> wrote in
news:1132716530.406874.269410@.f14g2000cwb.googlegr oups.com:
> Is there some way of storing XML in SQL Server 2005 as "pure" XML as,
> say, an XML order per record (unshredded to extract the original data
> ) and then querying the XML to get aggregate information on individual
> orders (e.g. sum of cost of all items in order)
If the XML you store in a column in a row have order items nodes, then
sure no prob. You use the built-in xquery functionality on the xml
datatype to do these aggregations.
>and then further
> aggregating the information to get, say, quarterly results (e.g. sum
> of cost of all items ordered from January 2005 to March 2005)?
I assume you with this means you want to do querying/aggregation over
several rows? If so, this is not supported "out of the box", SQL Server
does not support composition on the xml data-type. However it can be
done by selecting in all the data into an xml variable and then do the
query/aggregation against that.
>If the
> answer to this is yes, and I don't expect it to be, how is this done
> (i.e. can someone point me to information on how I could implement
> such a thing)?
Read Books Online about the xml data type and XQuery.
Niels
**************************************************
* Niels Berglund
* http://staff.develop.com/nielsb
* nielsb@.no-spam.develop.com
* "A First Look at SQL Server 2005 for Developers"
* http://www.awprofessional.com/title/0321180593
**************************************************
|||Niels and Graeme, I sincerely thank you for the trouble you have gone
to in addressing my issue and pointing me in the right direction.
|||To clarify, you can do the both aggregations with methods on XML data type.
To calculate prices of all orders when an order is an XML instance with
LineItem-s as XML elements you can write something like:
SELECT
sum(
xml_order.value('sum(/Order/LineItem/@.Price)','FLOAT')
)
FROM Orders
The inner XQuery "sum" would aggregate all price attribute values in an XML
instance and the outer T-SQL "sum" will aggregate across multiple XML
instances.
Best regards,
Eugene
This posting is provided "AS IS" with no warranties, and confers no rights.
"Cloudfall" <SydneyCloudfall@.hotmail.com> wrote in message
news:1132796074.018648.122550@.o13g2000cwo.googlegr oups.com...
> Niels and Graeme, I sincerely thank you for the trouble you have gone
> to in addressing my issue and pointing me in the right direction.
>
|||Hi Eugene,
Sorry to take so long to get back to you (I had to finalise and finally
finish a VBA for Excel project I've been working on for some months and
that's done now). This is my next project.
Thank you for feedback on the nested sums in the Select statement. I
expect my next project will be to develop a(n) SQL Server 2005 database
for storing XML orders which can be queried to produce a known set of
management reports. Consequently, I expect to be a regular visitor to
this group with my newbie questions.
Thank you again for your help.
Terry R.
