While migrating my stored procedures to SQL Azure, I received errors in some because of lack of support of some of the functionality. I had to find a workaround to successfully export them to SQL Azure.
Working with XML
It reads the XML string within the SP, parses it using MSXML parser and provides a handle to the parsed document, which is stored locally by the SQL Server. This parsed document is a tree representation of the various nodes in the XML document. It is not supported by SQL Azure.
It removes the parsed xml document created as a result of sp_xml_preparedocument. It is not supported by SQL Azure.
It provides a rowset view over an xml document. It is not supported by SQL Azure.
The workaround is to use nodes(..) and value(..) functions
As you can see in the highlighted portion, all the details of the WITH in the previous example have been passed within value(..) function, which is used in the SELECT statement. Also, the sp_xml_preparedocument and sp_removedocument have been removed. This method directly uses the xmlString to get the rows.