locked
How to load local XML File to Azure SQL Database? RRS feed

  • Question

  • I have a xml file and needs to load in a table in an Azure SQL Database. The table has a column as xml type. I tried linked server in SSMS to bulk load but it says distributed query does not work for xml column. Then I run sql query as below based on online search:

    INSERT INTO OPENQUERY(NuiAzureSQLDB,'SELECT name,date,cast(xmldata AS nvarchar(MAX)) as xmldata1 FROM mingxin.dbo.tblmxxml')
    (name,date,xmldata1)
    SELECT '','',cast(BulkColumn AS nvarchar(MAX)) as myfadexmlcol
    FROM 

    (select BulkColumn from OpenRowSet( bulk 'c:\temp\data\test.xml', single_blob ) as X) as Y

    But it still does not work. Can anyone suggest me a path to load xml files to a table in azure sql database?

    Thanks!



    Wednesday, June 1, 2016 5:01 AM

Answers

  • Hi,

    >>  can below methods work?
    1. Create a temp table with text string field in local sql server database,
    2. read the xml file as text string and insert it into the temp table
    3. use linked server to transfer the temp table to the azure sql database
    4. 
    with SSMS in azure sql database run sql to insert the xml field with like CONVERT

    As long as: step 1, you mean local temp table, which mean that during the use of the linked server you do not use XML type, and step 4 execute on direct connection to the Azure database and not with linked server, then the answer is yes... this should work (asssuming I understood you)

    >>  can below methods work?
    Use virtual machine with sql server installed, to ftp the xml to the vm,
    ftp the xml to the vmrun like OpenRowSet( bulk 'c:\temp\data\test.xml', single_blob )  to load the xml

    Execute query from SQL Server on VM is the same as execute it in SQL on your local machine. There are some exceptions were the VM uses a small tool to "talk" with the host, but for  most cases this is separate machine working the same as VM in your local network.

    ** I a totally agree with Mauricio Feijo. If you have the option to develop small external application, then this is should give you the best solution. If the question was not in Transact-SQL forum, then this was probably my first advice :-)

    ** Off-topic: Personally I recommend you to think about stop working with XML and more to use JSON. You will find out that there are lot of advantages without any disadvantages (one you organized the app architecture correctly). Moreover, SQL Server 2016 have full support for JSON, and Azure SQL Server already have as well for several months.


    signature   Ronen Ariely
     [Personal Site]    [Blog]    [Facebook]    [Linkedin]


    Thursday, June 9, 2016 4:22 AM

All replies

  • Seems like an extra layer of complexity to involve a local SQL Server instance. All you need is a program. I s'pose that SSIS can to the job, but I don't know SSIS. But here is a simple program to load LOB data that I culled from SQL Server Magazine many years ago:
    http://www.sommarskog.se/blobload.txt

    Wednesday, June 1, 2016 7:23 AM
  • Good day,

    >> I tried linked server in SSMS to bulk load but it says distributed query does not work for xml column.

    The issue is not related only to INSERT and can raise even in simple SELECT distributed query. This is well documented in the BOL: Tables that have xml columns cannot be queried, even if the query accesses non-xml columns of the table.

    For INSERT You can BULK INSERT the file content into temporary table as simple text, and then move it to the table with the XML type (using CONVERT from text to XML if needed). This mean that you execute the second step in the remote server and not from linked server, which might not fit your needs.

    For SELECT the workaround is to use simple convert. For example:
    SELECT Id, CONVERT(XML, column_name) column_name
    FROM OPENQUERY(linkedServer, 'SELECT Id, CONVERT(nvarchar(max), column_name) column_name FROM RemoteTableName') RemoteTableName;

    ** there is a active and open connect at Microsoft website regarding this issue. The connect is from 2008, and Microsoft response was "Since we are getting close to the end of the release we may consider this only for the next version of SQL Server". Today SQL Server 2016 release and the issue is still there according to the new BOL. What is new, is that it is now documented. You might want to vote for the Connect


    signature   Ronen Ariely
     [Personal Site]    [Blog]    [Facebook]    [Linkedin]



    • Edited by pituachMVP Wednesday, June 1, 2016 1:55 PM
    • Proposed as answer by Mauricio Feijo Monday, June 6, 2016 3:57 PM
    Wednesday, June 1, 2016 1:50 PM
  • Thanks for the reply. Upon the suggestion and further research, for the purpose of loading local xml to a xml column in an azure sql database table, if I use even manual work, can below methods work?

    1. Create a temp table with text string field in local sql server database, then read the xml file as text string and insert it into the temp table, then use linked server to transfer the temp table to the azure sql database, then with SSMS in azure sql database run sql to insert the xml field with like CONVERT(XML, column_name) column_name.

    2. Use virtual machine with sql server installed, to ftp the xml to the vm, then in vm run like OpenRowSet( bulk 'c:\temp\data\test.xml', single_blob )  to load the xml

    I just want to load a local xml file into an azure sql database table which has xml type field. As I am new on azure and with limited knowledge of sql database, any suggestions leads to right direction are appreciated!

    Monday, June 6, 2016 3:32 PM
  • The answer to this issue is very clear to me: Write custom C# code (or whatever is your language of choice).

    Shouldn't be more then 100 lines of code:

    • Read the XML file
    • Connect to SQL in azure
    • Insert in SQL DB 

    Anyone has a reason why this wouldn't work? :)



    My Technet Articles

    If you like this or another reply, vote it up!
    If you think this or another reply answers the original question, mark it or propose it as an answer.


    Mauricio Feijo
    www.mauriciofeijo.com


    Monday, June 6, 2016 4:05 PM
  • Thanks! I will try this solution then with c# coding.
    Monday, June 6, 2016 5:54 PM
  • Great! Please let us know if this answered your questions by marking the answer. Thanks.


    My Technet Articles

    If you like this or another reply, vote it up!
    If you think this or another reply answers the original question, mark it or propose it as an answer.


    Mauricio Feijo
    www.mauriciofeijo.com

    Tuesday, June 7, 2016 7:36 PM
  • Hi,

    >>  can below methods work?
    1. Create a temp table with text string field in local sql server database,
    2. read the xml file as text string and insert it into the temp table
    3. use linked server to transfer the temp table to the azure sql database
    4. 
    with SSMS in azure sql database run sql to insert the xml field with like CONVERT

    As long as: step 1, you mean local temp table, which mean that during the use of the linked server you do not use XML type, and step 4 execute on direct connection to the Azure database and not with linked server, then the answer is yes... this should work (asssuming I understood you)

    >>  can below methods work?
    Use virtual machine with sql server installed, to ftp the xml to the vm,
    ftp the xml to the vmrun like OpenRowSet( bulk 'c:\temp\data\test.xml', single_blob )  to load the xml

    Execute query from SQL Server on VM is the same as execute it in SQL on your local machine. There are some exceptions were the VM uses a small tool to "talk" with the host, but for  most cases this is separate machine working the same as VM in your local network.

    ** I a totally agree with Mauricio Feijo. If you have the option to develop small external application, then this is should give you the best solution. If the question was not in Transact-SQL forum, then this was probably my first advice :-)

    ** Off-topic: Personally I recommend you to think about stop working with XML and more to use JSON. You will find out that there are lot of advantages without any disadvantages (one you organized the app architecture correctly). Moreover, SQL Server 2016 have full support for JSON, and Azure SQL Server already have as well for several months.


    signature   Ronen Ariely
     [Personal Site]    [Blog]    [Facebook]    [Linkedin]


    Thursday, June 9, 2016 4:22 AM