Extract data from a field that looks like XML but the data type is actually nvarchar
We're currently upgrading/migrating a DB where data is polled every hour. The old DB had individual columns (ie. DateTime, Pressure, Temp, etc.), however, for the new one, we found out they captured everything in one column, and formatted as XML.
I tried using SELECT col.value('(/Data/Fields[Name="XXX"]/Value)', 'int'), however after digging around the FieldData column is actually nvarchar, so that didn't work.
I'm trying to figure out how to extract parts of the data within the FieldData column (see highlighted).
Any questions or further info, please let me know...
but that didn't do the trick either.
I would like to select the RV's that are highlighted, but I don't know how to create the syntax...first time working with XML.