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

Question

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

Stewart "Arturius" Campbell

SSC Guru

Points: 72497
More actions
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
- This topic was modified 2 years, 1 month ago by Stewart "Arturius" Campbell.
____________________________________________
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

Phil Parkin SSC Guru Points: 246693 More actions · Answer 1

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

If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

Stewart "Arturius" Campbell SSC Guru Points: 72497 More actions · Answer 2

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”

Phil Parkin SSC Guru Points: 246693 More actions · Answer 3

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.

If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

Stewart "Arturius" Campbell SSC Guru Points: 72497 More actions · Answer 4

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”

Phil Parkin SSC Guru Points: 246693 More actions · Answer 5

Well done.

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

If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

Stewart "Arturius" Campbell SSC Guru Points: 72497 More actions · Answer 6

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”