troubleshooting Question

Extract data from a field that looks like XML but the data type is actually nvarchar

Avatar of Classic1
Classic1Flag for Canada asked on
Microsoft SQL ServerSQL
6 Comments3 Solutions184 ViewsLast Modified:
Hi there,
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)[1]', '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...

Much appreciated,
Classic
Output.jpg
ASKER CERTIFIED SOLUTION
Barry Cunney

Our community of experts have been thoroughly vetted for their expertise and industry experience.

Join our community to see this answer!
Unlock 3 Answers and 6 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 3 Answers and 6 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros