July 31, 2009 at 12:18 am
Hi All,
I was recommended here by a DBA friend who hates any sql involving 'for xml':).
To make this hard, i'm not super experienced with SQL, but get myself around most projects quite easily. I am currently writing a custom reporting tool, using fusioncharts to provide graphs of report data as required. Fusion charts only accepts XML as its data source, previously i just used to build the xml by iterating through the results of a standard SQL query - but i have since heard about the ability to return SQL results as XML! I have searched the internet and can't find anything that really explains it adequately for what i need - I have read all of the XML workshop posted on this website, and i guess i'm just too stupid to work it out. I'm hoping someone here has time to look at what i'm working with, and advise me on how i could generate the required xml format. With one working example, i should be able to learn from there myself:) So here goes - any help is greatly appreciated.
Sample Query:
SELECT tblConsolidatedSales.TRDate, tblStores.StoreName AS label, SUM(tblConsolidatedSales.QuantityUnits) AS value
FROM tblConsolidatedSales INNER JOIN
tblStores ON tblConsolidatedSales.IDStore = tblStores.StoreID
WHERE (tblConsolidatedSales.TRDate > CONVERT(DATETIME, '2009-07-15 00:00:00', 102)) AND (tblStores.StoreName = N'adelaide' OR tblStores.StoreName = N'Robina')
GROUP BY tblConsolidatedSales.TRDate, tblStores.StoreName
Example output of this query
TRDate StoreName Value
2009-07-28 00:00:00.000Adelaide8650
2009-07-29 00:00:00.000Adelaide7880
2009-07-30 00:00:00.000Adelaide7805
2009-07-28 00:00:00.000Robina11998
2009-07-29 00:00:00.000Robina6870
2009-07-30 00:00:00.000Robina21255
XML Formatting Requirements:
This xml is the rquirement for a multiseries bar chart.
I assume the category label thing is quite difficult, i could easily generate that in asp. Is it possible to get the required formatting? Thanks again!
Charles
July 31, 2009 at 12:23 am
Not doing this much, what I'd suggest is that you start by reading about FOR XML in Books Online, the SQL Server help System.
This may make what you are doing much easier, and hopefully someone reading this suggestion will be able to provide you with additional guidance as well.
July 31, 2009 at 12:35 am
Hi,
I have read it all - i'm not an experienced DBA to start with, so my lack of familiarity with half the code used in examples for outputting data as XML makes it an even steeper uphill battle. I guess i could read between the lines of what you've said, and understand this to be a fairly in depth process - perhaps i'm better sticking with building the XML manually in the code-behind. I was just excited about the potential performance gains of being able to omit that step, which often requires multiple loops through the dataset returned 🙂
Thanks for the advice:)
Charles
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply