question

bdaoust avatar image
bdaoust asked

Query XML attribute returns null

Trying to retrieve value of attribute within a field in my database that has a datatype of XML. I have this select MIBRequestMessage.value('(/ToFileMIB)[1]', 'varchar(50)') from dbo.MIBRequestResponse ORDER BY MIBRequestMessageDT DESC But it returns NULL. Here is my data f577a67c-66b3-4dcd-9133-71ecad8cee41 TRUE MIB Inquiry 2013-11-01 14:16:24-04:00 Original Thanks
sqlxquery
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

· Write an Answer
Mister Magoo avatar image
Mister Magoo answered
You haven't posted valid XML, but I think we can infer from what you have posted that you have valid XML, which includes an XML Namespace "NS1". To query it you will need to reference that namespace. Also note the use of the text() function, which generally is a quicker way to access the text value of an xml element. I have taken the liberty of putting a root on your xml and including a dummy xmlns declaration to help highlight the missing pieces of your puzzle. declare @value nvarchar(max)=' f577a67c-66b3-4dcd-9133-71ecad8cee41 TRUE MIB Inquiry 2013-11-01 14:16:24-04:00 Original ' ;with xmlnamespaces ( 'whatever' as NS1 ) select data.value('(/root/NS1:ToFileMIB/text())[1]','varchar(50)') as ToFileMIB from (select convert(xml,@value)) x(data)
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.