SQL query on xml stored as nvarchar(max)

Question

I've been trying work through this query, but I cannot seem to find an answer specific to my question. There is a lot of information out there surrounding my question, but I cannot find a post that seems to answer it. I'm sure it is simple, but I just can't get it right. Here's what I have:

I have a column that stores XML data as nvarchar(max) and I trying to query the tags and not having much luck. Looking at the picture, <VU> is the root and I'm trying to get the value of <CPort> (80), but I cannot get the correct syntax. I have attached a screenshot for more clarity.

So far what I have is:

SELECT CAST(directory.dbo.unit.data AS XML) AS info
FROM Directory.dbo.unit

This lets me cast my column to XML, but I cannot query my newly cast column.

I tried:

SELECT CAST(directory.dbo.unit.data AS XML).query('(/VU/)') AS info
FROM Directory.dbo.unit

but this results as NULL. I also tried:

SELECT CAST(directory.dbo.unit.data AS XML).value('(/VU/CPort)[1]', 'nvarchar(max)') AS info
FROM Directory.dbo.unit

This also results in NULL

I just don't know what I'm doing wrong. Am I on the right track? Is casting the best way to query this. I tried sub-string, but that didn't work either. I do appreciate everyone's help with this. Let me know if you need more info or have more questions.

Answer 1

I want to say thanks everyone for your help and direction. I was able to finally get what I was looking for. I had to cast a couple of columns that contained xml data stored as nvarchar(max) and then tie in some additional columns, but I think this is going to work. Here is my final solution below.

    With test as (Select Guid as guid, FirmwareVersion as FV, CAST(Directory.dbo.Unit.data as xml) as new, CAST(Directory.dbo.Unit.Location as xml) as loc from Directory.dbo.Unit)

        select UnitName1, Manufacturer, model, FV, IP, TimeZone, PhysicalName, [user], CameraName, LogicalID
        from (
            select distinct
            test.guid
            ,ue.name UnitName1
            ,test.new.value('(./VU/Mfct)[1]','varchar(100)') as Manufacturer
            ,test.new.value('(./VU/Model)[1]','varchar(100)') as Model
            ,test.new.value('(./VU/CAddr)[1]','varchar(100)') as IP
            ,test.loc.value('(./EntityLocation/TimeZoneId)[1]','varchar(100)') as TimeZone
            ,d.physicalname
            ,test.FV
            ,test.new.value('(./VU/User)[1]','varchar(100)') as [User]
            ,CE.NAME CameraName
            ,CE.LogicalID
            from test 
            inner join Directory.dbo.Device d on test.guid = d.unit
            INNER JOIN Directory.dbo.Entity UE ON test.Guid = uE.Guid
            INNER JOIN Directory.dbo.Stream S ON S.Device = D.GUID 
            INNER JOIN Directory.dbo.Camera C ON S.Camera = C.GUID 
            INNER JOIN Directory.dbo.Entity CE ON C.Guid = CE.Guid
            where charindex('Camera',d.PhysicalName,0) > 0
        ) x
        order by IP desc, guid, UnitName1, cameraname, LogicalID

It might not be the prettiest or most efficient way to write this, but it works for me! Thanks again everyone! techstudent01