Partager via

Restrictions of Stored Procedures in SQL Azure

  • Article

 

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

 

sp_xml_preparedocument

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.

 

sp_xml_removedocument

It removes the parsed xml document created as a result of sp_xml_preparedocument. It is not supported by SQL Azure.

 

OpenXML

It provides a rowset view over an xml document. It is not supported by SQL Azure.

 

Example

 

 

Output

 

 

 

Workaround

 

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.

Comments

  • Anonymous
    June 02, 2014
    Can you please tell that what is alternative, if I have used meta property (i.e. @mp:id, @mp:parentid) with OpenXML? What changes are required in my stored procedure for that? Thanks in advance..!

  • Anonymous
    September 16, 2015
    Declare @XDoc xml set @XDoc = '<rws> <rw> <Customer>Janine</Customer> <Order>O1</Order>   </rw>   <rw> <Customer>Petru</Customer> <Order>O3</Order>   </rw> </rws>' How do I get : Customer Order

Janine 01 Petru 02 With an expath query in SQL