Results 1 to 7 of 7
  1. #1
    Join Date
    Oct 2007
    Posts
    22

    Unanswered: Parsing SQL Objects into XML

    Hello everyone.

    This is a somewhat strange question, so bear with me. Is anyone familiar with a way to, via command line (or not - I'd be curious to know either way), parse a stand-alone (not actually applied to database) SQL script into some form of XML?

    For instance, say I have a SQL script: my_procedure.sql

    CREATE PROCEDURE dbo.my_procedure
    AS
    SELECT full_name
    FROM that_guy
    GO

    Using some method that is preferably accessible by .NET code, parse that into an XML file that looks something like this:

    <sqlobject>
    <object_type>procedure</object_type>
    <objecT_name>my_procedure</object_name>
    </sqlobject>

    Any suggestions would be very much appreciated!

    Thank you in advance.

    Joe
    www.socialec.com - Social Engineering/NLP

  2. #2
    Join Date
    Feb 2004
    Posts
    492
    I guess I would write a small batch using find or findstr if the sample covers it. Perhaps look into http://www.microsoft.com/technet/scriptcenter/tools/logparser/default.mspx
    I have seen some quirky behaviour with logparser though.

  3. #3
    Join Date
    Oct 2007
    Posts
    22
    Kaiowas, thanks for the response!

    I have already written a series of fairly complex regex statements that parse through the scripts. However, I've found it increasingly difficult to parse scripts that are written by people who write sloppy code.

    That's why I was wondering if something existed that already does this (better than my current implementation) that could easily extract this information into XML format. Something else that would actually work would be some sort of callable utility that reformats SQL scripts (without having to apply them to the database) so that I could use the regex searching I already have to get definitive answers. Know of anything like this?

    Thanks again!

    Joe
    www.socialec.com - Social Engineering/NLP

  4. #4
    Join Date
    May 2004
    Location
    Seattle
    Posts
    1,313
    SqlSpec uses xml as an intermediate result, which you may be able to use.

    However, SqlSpec runs against an existing database, not a script.

  5. #5
    Join Date
    Oct 2007
    Posts
    22
    Does you happen to know if SqlSpec queries system tables to get the information to create the XML output? I guess, depending on whether or not you were involved in its development, you probably wouldn't even know that answer!

    Given that, A. you do know how it creates the XML and B. it doesn't query system tables, do you have any idea how this information is collected?

    Thank you!

    Joe
    www.socialec.com - Social Engineering/NLP

  6. #6
    Join Date
    Oct 2007
    Posts
    22
    Not that anyone probably cares, but I believe I've found almost exactly what I'm looking for!

    http://www.sqlparser.com/index.php

    Also, in case anyone does care, here is why I'm looking for this:

    My company currently has a fairly formalized process for SQL script reviews by our DBA team before they are allowed into production. We have a .NET application that mediates this interaction. However, because of standards that we enforce, many times, scripts are rejected because of simple mistakes made by the developer. I'm trying to find a way to add an automated 'pre-check' to be used to eliminate the unecessary rejections, stream-line the production reviews and save everyone a whole lot of time.

    Thank you everyone for your comments; after I have integrated and tested it in our application, I will let you know how it goes!

    Joe
    www.socialec.com - Social Engineering/NLP

  7. #7
    Join Date
    May 2004
    Location
    Seattle
    Posts
    1,313
    Quote Originally Posted by Joe_F
    Does you happen to know if SqlSpec queries system tables to get the information to create the XML output? I guess, depending on whether or not you were involved in its development, you probably wouldn't even know that answer!

    Given that, A. you do know how it creates the XML and B. it doesn't query system tables, do you have any idea how this information is collected?

    Thank you!

    Joe
    sorry, I should have mentioned that I am the developer for SqlSpec.

    So I can tell you with relative certainty that it does query the system tables such as sys.objects and friends to create its output.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •