• For broadcastdate in header, you could simply query the source, which has the value stored as an nvarchar(max) like this to return the value.

    select CONVERT(XML, XmlDump, 2).value('(/Announcement/Headline/BroadcastDate)[1]','DATE')

    from XmlTestSource