May 4, 2022 at 10:22 pm
My company uses a reporting tool that basically sits on top of SQL Server (and generally makes my life miserable, but that's another post...).
Anyway, one thing this software does is provides a GUI for "business users" (non-SQL-savvy users) to create SQL queries. But internally it actually stores those queries as XML, which it then uses to generate SQL "under the hood" whenever the query needs to be run.
I'm attaching a sample of the XML code it saves along with the SQL equivalent (which I've manually reconstructed here for this example).
My question is: is there a practical way for me to shred this XML and turn it back into SQL, using SQL? Or is the complexity such that this is better suited to some other procedural programming language of some sort?
Thanks!
May 4, 2022 at 10:45 pm
This was removed by the editor as SPAM
May 5, 2022 at 8:53 am
Wow, I can see why your life is miserable! 🙂
I believe that I could do it in SQL, but it would take a significant amount of effort.
If you were to provide the XML version of something dead simple, say
SELECT c1, c2
from t
where t.c3 = 'XML bloatfest'
I would happily have a go at that and then leave you to have a go at adding the complexity.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
May 5, 2022 at 3:20 pm
Thanks for the reply, Phil! I think I could handle a simple query like that as well. Where it starts getting rough for me is when there are [x] number of joins to iterate through, each with [y] number of columns, etc. I wouldn't even know how to do that in SQL. So do you think that, realistically, it would be better to attempt this in another way?
May 5, 2022 at 3:55 pm
Thanks for the reply, Phil! I think I could handle a simple query like that as well. Where it starts getting rough for me is when there are [x] number of joins to iterate through, each with [y] number of columns, etc. I wouldn't even know how to do that in SQL. So do you think that, realistically, it would be better to attempt this in another way?
C# handles XML way better than SQL, but it's still going to take some significant work. What are you planning to do with these queries, once they have been 'reconstituted'?
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
May 5, 2022 at 4:22 pm
We really just need a way to view/export the results of the reconstituted queries. Thanks for your thoughts on this project though, I think I will pursue a non-SQL approach and see how far I can get. 🙂
May 5, 2022 at 6:31 pm
Check to see if the tool has some type of api or other interface that will let you get the SQL from the tool itself.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply