Two XML columns were required for checking so that the checksums stored in these xml files plus the stored db checksum value could be compared in order to detect those rows with mismatched data.
A Sample Adi file is shown here and from this it is easy to understand the Xpath values used in MSSQL in order to retrieve the correct data.
<?xml version="1.0" encoding="UTF-8"?>
<ADI xmlns="somevendornamespace">
<Metadata>
<AMS Asset_Class="package" Asset_ID="" Asset_Name="" Creation_Date="2020-02-20" Description="" Product="" Provider="" Provider_ID="" Verb="" Version_Major="1" Version_Minor="0" />
<App_Data App="VOD" Name="Metadata_Spec_Version" Value="CableLabsVOD1.1" />
<App_Data App="VOD" Name="Provider_Content_Tier" Value="VU" />
</Metadata>
<Asset>
<Metadata>
<AMS Asset_Class="title" Asset_ID="" Asset_Name="" Creation_Date="2020-02-20" Description="" Product="" Provider="" Provider_ID="" Verb="" Version_Major="1" Version_Minor="0" />
<App_Data App="VOD" Name="SomeField" Value="" />
<App_Data App="VOD" Name="SomeField" Value="" />
<App_Data App="VOD" Name="SomeField" Value="" />
<App_Data App="VOD" Name="SomeField" Value="" />
<App_Data App="VOD" Name="SomeField" Value="" />
<App_Data App="VOD" Name="SomeField" Value="" />
</Metadata>
<Asset>
<Metadata>
<AMS Asset_Class="movie" Asset_ID="" Asset_Name="" Version_Minor="0" Version_Major="1" Creation_Date="2020-02-20" Description="" Provider_ID="VMM" Provider="" Product="" Verb="" />
<App_Data App="VOD" Name="Type" Value="movie" />
<App_Data App="VOD" Name="Audio_Type" Value="Dolby Digital" />
<App_Data App="VOD" Name="HDContent" Value="Y" />
<App_Data App="VOD" Name="Copy_Protection" Value="Y" />
<App_Data App="VOD" Name="Languages" Value="en" />
<App_Data App="VOD" Name="Screen_Format" Value="Widescreen" />
<App_Data App="VOD" Name="Encoding_Type" Value="H264-HD" />
<App_Data App="VOD" Name="CGMS_A" Value="3" />
<App_Data App="VOD" Name="Content_FileSize" Value="5682735032" />
<App_Data App="VOD" Name="Content_CheckSum" Value="45D4855D573C3A585FAA254A1129C7FE" />
</Metadata>
</Asset>
</Asset>
</ADI>
The /* in the below XPath expression states we wish to ignore any namespace declarations in the xml.
Asset[2] states we will navigate to the second asset declaration in the xml file, as often there are multiple for images and preview data.
Finally we navigate into the metadata fields and look at the App_data nodes for a attribute called “Content_Checksum” in the following format: [@attribute_name=”Attribute_Value”].
Finally to obtain the required value from the “Value” Attribute the sql calls the @Value attribute inside the App_Data node and takes the value [1].
/*** Check of #TempTable exists if it does drop it ***/
If(OBJECT_ID('tempdb..#TempTable') Is Not Null)
Begin DROP TABLE #TempTable
End
/*** Obtain the required data and xml data using xpath query. ***/
select
TITLPAID,
EnrichedADI.value('(/*:ADI/*:Asset/*:Asset[2]/*:Metadata//*:App_Data[@Name="Content_CheckSum"]/@Value)[1]', 'nvarchar(max)') as ENRICHED_CHECKSUM,
PreviewFileChecksum as DBROW_CHECKSUM, UpdateAdi.value('(/*:ADI/*:Asset/*:Asset[2]/*:Metadata//*:App_Data[@Name="Content_CheckSum"]/@Value)[1]', 'nvarchar(max)') as UPDATE_CHECKSUM
INTO
#TempTable
FROM
Adi_Data
WHERE
PreviewFileChecksum != 'NULL'
/*** Select the Data from the temptable and compare the results ***/
SELECT
*
FROM
#TempTable
WHERE
DBROW_CHECKSUM != ENRICHED_CHECKSUM
OR
DBROW_CHECKSUM != UPDATE_CHECKSUM
OR
ENRICHED_CHECKSUM != UPDATE_CHECKSUMThe resulting output were the mismatched rows, from here it was easy to identify and update the affected rows.
A great tool that will help speed up xpath generation can be found at XmlToolBox:
https://xmltoolbox.appspot.com/xpath_generator.html
