This is part 2 of the article for creating custom SSMA report using SSIS and SSRS. In the previous post, we discussed how to use SSIS package to extract XML files containing SSMA assessment report information into SQL Server table. This week, we will discuss how to parse the XML data.
First, let's create tables to hold the parsed data:
tblSSMAReport_Object table contains the information about the database object and its overall status. tblSSMAReport_MessageDetails table stores conversion message .
We will need to parse text in order to extract information based on relative location. I created a function which we will use several times in this example:
The UDF returns segment of the string based on the following parameter value:
- Last: return an entire value of last segment (as identified by delimiter character) containing the keyword
- After: return the segment after the segment containing the keyword
- Before: return the segment before the segment containing the keyword
results in the following:
We can then use the user defined function to parse the XML and populate tblSSMAReport_Object:
The inner statement parse object path and XML values while the outer statement perform string aggregation to form the object path across multiple rows. The tblSSMAReport_Object table needs to be updated with additional attribute such as object category, name and conversion status. The object category can be parsed from the object path. I use the following UDF to help with the parsing:
The function locates the specified keyword from the path and returns the first value found based on the sequence specified in the function.
SELECT [dbo].[UDF_IdentifyObjectType]('Schemas > HR > Tables > LOCATIONS > Indexes > LOC_COUNTRY_IX')
returns 'Index' even though both 'Tables' and 'Indexes' keyword exists, but since index appears first in the sequence, the index keyword is returned. With UDF_IdentifyObjectType created, I can now update the object table and cross apply with the value from cat.xml values in the tblRawData to get the conversion status.
In the part 3 of this article, I will show an example of SSRS report which consume from the tables we just created.