Any
5
TBD
Aug 13, 2022
The deliverable is a Microsoft Sql Server query that is fast as the database is big.
The query needs to take a column that contains XML data. The query needs to parse the XML, and search every byte of multiple text strings in the XML for extended ascii codes.
I am working on narrowing down the number of characters to look for, but we can start with a range of extended characters 224 - 237. ß-?
I have attached the XML that you can duplicate to make YOUR OWN TEST DATA. You will create a dummy Customer table to your liking and create an XML column to hold the test data.
The query will have the following requirements:
1. Create a Test Database in SQL SERVER
2. Create a Customer table to hold the test data and add as many columns as necessary. You'd need an auto generated primary key, a Customer Number, and XML column and a customer create date so we can narrow our search.
3. Create another table called 'Dealer' and use the same xml attached and similar columns as in step 2.
4. Accept a date range to find the customers. The same date range can be used for all tables. Accept a list of Table Names to Search. (As time goes on, we will probably add more table names so that's why we can't hard code the list.
5. Accept a list of extended ascii codes to be used for the comparison
6. In the attached file, I have added extended ascii codes as an example.
7. Search all the fields that I've added test data to.
8. Search for at least 5 different extended ascii codes. Include code 225 ß in your working query.
8. You can STOP searching the column when you find ANY of the codes in the list.
9. If you find a match go to STEP 12.
10. If you DON'T find a match, move on to the next FIELD.
11. REPEAT steps 9 and 10
12. If you find ANY of the SEARCH code in ANY of the xml elements, List the Select the Customer Number, Date and the column. It would be really cool to also show any field that a character was found but I can live without that 8-)
11. Move on to the next customer in your list.
12 Move on to the next table.
13. Rinse and repeat!