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.

How to preview large string

Often you need to debug dynamic SQL statement. Simplest way to do this is to print statement. Because of 8000 char limit (400 for unicode), it is hard to do in "standard way".

here is an example:

DECLARE @Very_big_string NVARCHAR(MAX)= '' ;
WITH    c AS ( SELECT   ROW_NUMBER() OVER ( ORDER BY o1.object_id, o2.object_id ) RN
               FROM     sys.objects o1
                        CROSS JOIN sys.objects o2
             )
    SELECT TOP 9999
            @Very_big_string = @Very_big_string + 
    REPLICATE(0,4 - LEN(CAST(rn AS NVARCHAR)))
            + CAST(rn AS NVARCHAR) + CHAR(13)
    FROM    c    

PRINT @Very_big_string 

Last result will be 0800 - 4 chars  for digits and one for end line - char(13) (4+1)*5=4000

There are some methods to print string by UDF etc. I propose simpler method: print XML which will contain your string (xml is not limited by char, by size in SSMS settings only - default is 2MB
So here is an example for this string:

DECLARE @Very_big_string NVARCHAR(MAX)= '' ;
WITH    c AS ( SELECT   ROW_NUMBER() OVER ( ORDER BY o1.object_id, o2.object_id ) RN
               FROM     sys.objects o1
                        CROSS JOIN sys.objects o2
             )
    SELECT TOP 9999
            @Very_big_string = @Very_big_string + 
    REPLICATE(0,4 - LEN(CAST(rn AS NVARCHAR)))
            + CAST(rn AS NVARCHAR) + CHAR(13)
    FROM    c    

SELECT CAST(''+@Very_big_string+'' as XML)

Here you can preview your string in XML viewer.

Disadvantage of this method is that all standard XML symbols like <,>,! etc are changed to ampersand sequences (eg > is changed to &dt;)

How to capture changed data without schema modification : Part 2

In previous part I showed how to store changed rows. Now we will store changes in database in separate table 

Main idea how to get columns names is to use XML: at first, we create an XML from joined together old and new rows, and then we extract both old and new value as well as column name and write this data to audit table. To detect NULL value, we should use XSINIL argument of FOR XML clause.

Now we have in our audit table all necessary information: id, changed column name and both new and old values.

Conclusion

In this two-part article I tried to explain how is possible to capture data change even without data scheme modification. You can download full script from link below and change tables names to real. If you have any useful suggestion, please tell me.


How_to_capture_changed_columns.sql (4Kb)