August 23, 2007 at 12:08 pm
All,
I'm diving into the XML aspect of SQL server for the first time as we are considering using it in our data warehouse. I was thinking we could use an XML datatype to store a string of financial buckets in (per customer) and then I got to wondering how difficult it would be to sum up the totals.
Example. We have Customers A, B and C. Each customer has a bucket in which their total dollars owed reside. Bucket:Current, Bucket:30-60, Bucket:61-89, and Bucket:90Days+. Now these are not literal bucket names. I'm just using them as an example for financial purposes.
Now, if I store all the values in one XML datatype called Buckets, instead of individual columns, how easy is it to break the different values out of Buckets for a financial report and get a sum of Current, 30-60, 61-89 and 90Days+? (If my math is off in the below table, ignore it. I'm just presenting an example of what I'm talking about.)
Customer Current 30-60 61-89 90Days+
A 0.00 5.00 15.00 30.00
B 10.00 0.00 1.00 0.00
C 2.00 10.00 12.00 1.50
TOTALS 12.00 15.00 28.00 31.50
In the books I'm looking at, I see all sorts of examples to retrieve data, but the examples are showing the results as an XML result, so that's why I'm not sure if using an XML datatype is the way to go here. Any thoughts would be greatly appreicated.
August 24, 2007 at 6:38 am
Well - it all seems to depend on how much you plan on using it and/or how big of a dataset we're talking about. In defining the XML data source, I remember Microsoft advising that this be used specifically for items that are:
They then followed that up with something akin to "if your data is highly structured and will be used a lot - then put it in traditional data tables and fields", with an implication that perf. is better (you'd hope it would be).
So it all depends on what you were thinking of storing. Were you thinking of "pre-processing" the summary amount of the buckets into an XML format, or was it more to keep all of the detail leading up to the bucket? I could see the summaries in there, but I'd lean to keeping the detail in regular tables, and whipping out a SELECT...Group by....PIVOT.... construct when someone wants the stuff.
Finally - easy is in the eye of the beholder. I've had to write a few of those queries, and although it feels strange, it's really not "hard" to get the data you need (at least the stuff I was pulling). The challenges usually have to deal with oddly formatted XML files I had got from others - you seem to control this part of the file - so it should be easy enough to organize it "your way".
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
August 24, 2007 at 6:45 am
It would be the pre-summary details kept in that data type, which is why I was concerned about using mathmatical operations on it.
I see your point about everything else. I guess I'm going to have to do more research and play with it on a dev server before I make the decision.
Thanks, Matt.
August 24, 2007 at 12:37 pm
AHA! I think I finally found the the way to turn XML datatype data into data you can operate on.
Does the Nodes() method sound correct to anyone?
August 24, 2007 at 1:10 pm
yes, but don't forget the CROSS APPLY predicate. That takes the contents of the XML field in each row and treats them as if they were multiple rows (which conceptually they should be).
if the contents of the XML field (which I will call xdata) in table MyTable was structured as such:
<buckets>
<bucket type="current" value="123"/>
<bucket type="31-60" value="75.25"/>
<bucket type="61-90" value="13"/>
<bucket type="91+" value="1"/>
</buckets>
then the SQL to extract that could look like:
Select
xd.value('@type','nvarchar(50)') type, xd.value('@value','money') amount
from mytable CROSS APPLY xdata.nodes('/buckets/bucket') xd
once you have this as a basis you can do anything else SQL to it you wish. Pivot, sum, groupby, etc....
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply