Keyword or statement option 'XMLNAMESPACES' is not supported in this version of SQL Server.

When you will prepare database migration from SQL Server standalone edition to SQL Azure, there will be many compatibility issues. One of them is unsupported namespaces inside of XMLs.

You can find a list of unsupported Transact-SQL statements here.

As long as Microsoft does not planning to include namespace support in SQL Azure, we still need to manage this issue.

So assume we have an Excel XML looking like this:

DECLARE @XML xml='


 
       1     SomeText1     SomeText2     SomeText4     SomeText5    

'

One of possible ways is to convert XML to NVARCHAR(MAX) type, then remove namespaces and prefixes using REPLACE function, and then convert it back to XML type.
In this case your code looks like this:

DECLARE @XML_WithNoNamespace_Char XML = 

REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(CAST(@XML AS NVARCHAR(MAX)),
                                                              'xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet"',
                                                              ''), 'ss:', ''),
                                                              ' xmlns:o="urn:schemas-microsoft-com:office:office"',
                                                              ''), 'o:', ''),
                                                              'xmlns:html="http://www.w3.org/TR/REC-html40"',
                                                              ''), 'html:', ''),
                                                           'xmlns="urn:schemas-microsoft-com:office:spreadsheet"',
                                                           ''),
                                                   'xmlns:x="urn:schemas-microsoft-com:office:excel"',
                                                   ''), 'x:', '')

Let us see test result:

SELECT 
       @XML.exist('/Workbook/Worksheet/Table/Row/Cell/Data[text()="SomeText1"]') AS [Is_Found_In_Old_XML],
       @XML_WithNoNamespace_Char.exist('/Workbook/Worksheet/Table/Row/Cell/Data[text()="SomeText1"]') AS [Is_Found_In_New_XML]

As expected, in first case row was not found, but in second case required row was found.

Another way is to extract data using XQuery namespace declaration predicate. So we can get all required data - Row number,Cell number and Cell value using numbers table which should be populated from 1 to XML`s row count. I used cross joined sys.objects table for this but definitely you can find some other sequence generating algorithms (or if you have SQL Server 2012 version, you can use built-in sequence generator).

Code looks like this:

DECLARE @NumbersOnTheFly TABLE (Number INT PRIMARY KEY);

WITH Numbers
AS (
 SELECT ROW_NUMBER() OVER (
   ORDER BY O1.[object_id]
    , o2.[object_id]
   ) AS Number
 FROM sys.[objects] O1
 CROSS JOIN sys.[objects] O2
 )
INSERT INTO @NumbersOnTheFly
SELECT Number
FROM Numbers
WHERE Number <= @XMl.query('
  declare namespace ss="urn:schemas-microsoft-com:office:spreadsheet";
   count(/ss:Workbook/ss:Worksheet/ss:Table/ss:Row)').value('.', 'bigint');

WITH C
AS (
 SELECT N.Number
  , @XMl.query('
     declare namespace ss="urn:schemas-microsoft-com:office:spreadsheet";
      for $row in /ss:Workbook/ss:Worksheet/ss:Table/ss:Row[position()=sql:column("N.Number")]/ss:Cell
      return  {$row/ss:Data[1]/text()} 
      ') Cell
 FROM @NumbersOnTheFly N

 )
SELECT C.Number AS RowNumber
 , ROW_NUMBER() OVER (
  PARTITION BY C.Number ORDER BY a.b
  ) AS CellNumber
 , b.value('.', 'NVARCHAR(1000)') AS CellValue
FROM C
CROSS APPLY C.Cell.nodes('Cell') a(b)

Which way to choose to process XML with namespaces on SQL Azure? You should check performance to see which one is best for your XMLs.
Anyway, we are still waiting for XML namespaces support in SQL Azure.