MSSQL XPATH Xml Column Value Compare

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_CHECKSUM

The 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