Azure XML support

1
votes

I am looking at vendor page, and see contradicting information (top of each page with green or gray checkboxes) wheter XML column data type is supported in Azure SQL Server or not.

Could anybody with access to Azure SQL Server databases may tell me if XML is fully supported or not?

https://docs.microsoft.com/en-us/sql/connect/oledb/features/using-xml-data-types?view=sql-server-2017

https://docs.microsoft.com/en-us/sql/relational-databases/xml/xml-data-type-and-columns-sql-server?view=sql-server-2017

https://docs.microsoft.com/en-us/sql/t-sql/xml/xml-transact-sql?view=sql-server-2017

2
sql-serverxmlazureazure-sql-database
Not all functions are supported in Azure. For example OPENROWSET support to load XML file from disk was only recently added on Azure Managed instances, but not on regular databases. - Piotr
@Piotr but is the actual XML column type supported? Can I save XML fragments into fields? - Avi
Microsoft documentation has gotten inexcusably poor over the last ten years. This is a great example. I found this thread because of the exact same issue as described herein. - Pittsburgh DBA

2 Answers

4
votes

Just executed following script in Azure database, works fine:

create table x(t xml)
insert into x(t) values('<zz>aaaabbbb</zz>');
select t.value('/zz[1]','varchar(100)') from x;
2
votes

Azure SQL database supports XML Data Type,xml (Transact-SQL) , but not supports XML data. enter image description here

The documents which involve xml data all show don't support Azure SQL database.

I think XML is not fully supported in Azrue SQL Database. But we can create XML data type column in Azure SQL database, and do what Piotr showed for you. Not all functions are supported in Azure.

Here is blog I think you can reference: How to load local XML File to Azure SQL Database?

There is a xml file and needs to be load into a table in an Azure SQL Database. The table has a column as xml type.

And it also gives you the answer maybe can give you some ideas about save XML fragments into fields.

Hope this helps.