Post reply

Interrogate XML columns in table, however element names contain _x0020_ (space)

  • March 14, 2022 at 8:24 am

    #3998857

    Good day

    In my current contract, I have been tasked with writing queries to extract data from an audit trail table in a SQL server DB, with two XML columns.

    Upon creating a script to view the data, reflecting exactly what has been changed between Old & New, i have encountered an issue - the attributes contain the space hex (given that column names in the database have spaces therein).

    SELECT [System ID], TableName, Old.value('(./sd/Source_x0020_System/text())[1]','VARCHAR(1000)') OldValue, New.value('(./si/Source_x0020_System/text())[1]','VARCHAR(1000)') NewValue, ChangeDate FROM [Audit Trail]

    just to test, i had created the data in a sandbox database, with underscores in the names, instead of space, and the query works.

    I would appreciate any advice on how to interrogate the above.

    Thanks Stewart

     

    ____________________________________________
    Space, the final frontier? not any more...
    All limits henceforth are self-imposed.
    “libera tute vulgaris ex”

  • March 14, 2022 at 11:09 am

    #3998920

    Can you provide some sample code which we can cut & paste into SSMS?

    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

  • March 14, 2022 at 2:13 pm

    #3999082

    Here are a few records drawn from the audit trail table - i needed to mask some columns, due to sensitivity, but the detail is still usable...

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

    ____________________________________________
    Space, the final frontier? not any more...
    All limits henceforth are self-imposed.
    “libera tute vulgaris ex”

  • March 14, 2022 at 3:44 pm

    #3999177

    I assume you are familiar with the REPLACE function, with which one can easily change the _X0020_ back to a space?

    If you are asking about how to avoid the issue in the first place, please provide a means by which we can replicate it.

    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

  • March 15, 2022 at 6:38 am

    #3999555

    I finally figured how to resolve the issue - convert the XML fields to NVARCHAR(MAX), Replace the _x0020_ with an underscore, convert back to XML, run the shred using nodes(), then replace the underscore with space on final output.

    very slow, but at least the auditors have the data they require.

    ____________________________________________
    Space, the final frontier? not any more...
    All limits henceforth are self-imposed.
    “libera tute vulgaris ex”

  • March 15, 2022 at 6:49 am

    #3999560

    Well done.

    Now, how about fixing whatever it is that puts that garbage into your Audit Trail table in the first place? 🙂

    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

  • March 15, 2022 at 7:34 am

    #3999586

    I would love to. However, that would basically involve a total redesign of the entire database and it's application. Personally, i would recommend scrapping that platform and starting over... the entire platform is a mess.

    ____________________________________________
    Space, the final frontier? not any more...
    All limits henceforth are self-imposed.
    “libera tute vulgaris ex”

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply