Post reply

Converting XML to SQL

  • May 4, 2022 at 10:22 pm

    #4026913

    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!

    • This topic was modified 2 years, 7 months ago by  autoexcrement. Reason: re-trying file attachments

    Attachments:
    You must be logged in to view attached files.

    "If I had been drinking out of that toilet, I might have been killed." -Ace Ventura

  • May 4, 2022 at 10:45 pm

    This was removed by the editor as SPAM

  • May 5, 2022 at 8:53 am

    #4027189

    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.

    • This reply was modified 2 years, 7 months ago by  Phil Parkin.

    The absence of evidence is not evidence of absence.
    Martin Rees

    You can lead a horse to water, but a pencil must be lead.
    Stan Laurel

  • May 5, 2022 at 3:20 pm

    #4027392

    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?

    "If I had been drinking out of that toilet, I might have been killed." -Ace Ventura

  • May 5, 2022 at 3:55 pm

    #4027434

    autoexcrement wrote:

    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

    You can lead a horse to water, but a pencil must be lead.
    Stan Laurel

  • May 5, 2022 at 4:22 pm

    #4027486

    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. 🙂

    "If I had been drinking out of that toilet, I might have been killed." -Ace Ventura

  • May 5, 2022 at 6:31 pm

    #4027552

    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