Msg 40526, 'OPENXML' rowset provider not supported in this version of SQL Server

Today, I was trying to publish my database to Windows Azure SQL Database and found that OPENXML is not supported.

There are many others Transact-SQL Statements which are also not supported. You can get the list of them from below link.

http://msdn.microsoft.com/en-us/library/ee336253.aspx

Now, we will see how we can replace OPENXML using XQuery (or XML Data Type Methods) nodes() and value(). You can get more details regarding XQuery from below link.

http://technet.microsoft.com/en-us/library/ms190798.aspx

Below is logic which is used OPENXML and read column names from below XML.

Query:
Declare
@p_ColumnList XML = N'<ColumnList>
  <Column>
    <ColumnName>ID</ColumnName>
  </Column>
  <Column>
    <ColumnName>Name</ColumnName>
  </Column>
</ColumnList>', @vColumnListDoc int

Declare @ColumnListTable table(
ColumnName varchar(500)
)


-- Prepare XML Document
exec sp_xml_preparedocument @vColumnListDoc output, @p_ColumnList

Insert into @ColumnListTable(
ColumnName
)
  
Select
   ColumnName
From
   openxml(@vColumnListDoc,'/ColumnList/Column',2)
with(
   ColumnName varchar(500)
)
  
-- Remove XML Document 
exec sp_xml_removedocument @vColumnListDoc

Select * from @ColumnListTable
Output:

Now, we will see how we can implement same logic using XQuery (or XML Data Type Methods) nodes() and value().

Query:
Declare
   @p_ColumnList XML = N'<ColumnList>
  <Column>
    <ColumnName>ID</ColumnName>
  </Column>
  <Column>
    <ColumnName>Name</ColumnName>
  </Column>
</ColumnList>'

Declare @ColumnListTable table(
ColumnName varchar(500)
)
  
Insert into @ColumnListTable(
ColumnName
)Select
T.c.value('./ColumnName[1]','varchar(500)')
From
   @p_ColumnList.nodes('/ColumnList/Column') T(c)


Select *
From
   @ColumnListTable
Output:

It looks very similar as OPENXML with less steps. Hope this post will be useful for you also.

This post also can helpful for different type of questions as below.

1) How to use OPENXML to read data from XML?
2) How to read column names from XML?
3) How to use XQuery nodes() and value()?