Return XML data from Nvarchar(MAX)

Question

I need some help pulling data from a nvarchar(max) column storing XML data.

Here is sample data.

<ArrayOfstring xmlns:i="http://www.w3.org/2001/XMLSchema-instance" xmlns="http://schemas.microsoft.com/2003/10/Serialization/Arrays">
  <string>B1-10</string>
  <string>B2-20</string>
  <string>B2-20</string>
</ArrayOfstring>

I have tried this and only return NULL's then errors out to "XML parsing: line 1, character 2, illegal qualified name character"

SELECT cast(COLUMNNAME AS xml).value('/ArrayOfString[1]/String[1]', 'VARCHAR(MAX)')
FROM dbo.TABLENAME 

I have tried numerous ideas off this awesome site and all seem to have the same result. What am I missing here? Thanks in advance for your help.

Answer 1

Try this:

;WITH XMLNAMESPACES(DEFAULT 'http://schemas.microsoft.com/2003/10/Serialization/Arrays', 
                    'http://www.w3.org/2001/XMLSchema-instance' AS i)
SELECT cast(COLUMNNAME AS xml).value('(/ArrayOfstring/string)[1]', 'VARCHAR(MAX)')
FROM dbo.TABLENAME