CODEWITHSUNDEEP
sql-serverxmlt-sqlazure-sql-database
Bob Horn
Bob Horn

Reputation: 34297

Load XML file into SQL Azure

I've just tested a TSQL script, locally, that imports data from an XML file into my SQL table. I just had an "oh crap" moment because I'm not sure where to put my XML file so that I can get this data into my SQL Azure DB. I've done some googling but couldn't find how to do this.

Is there a way I can run this SQL against my Azure DB and have it load the contents of my XML file?

Note: Notice this line below:

BULK 'c:\Temp\abook.20130407.1912.xml',

It's on my local PC. I'm not sure where to put this file so I can get this data to Azure.

SET IDENTITY_INSERT Contact ON

INSERT INTO Contact (ContactId, EntityId, Type, Value, Notes)
SELECT X.row.query('field[@name="id"]').value('.', 'INT'),
       X.row.query('field[@name="recordid"]').value('.', 'INT'),
       X.row.query('field[@name="type"]').value('.', 'VARCHAR(25)'),
       X.row.query('field[@name="contact"]').value('.', 'VARCHAR(100)'),
       X.row.query('field[@name="notes"]').value('.', 'NVARCHAR(255)')
FROM ( 
SELECT CAST(x AS XML)
FROM OPENROWSET(
     BULK 'c:\Temp\abook.20130407.1912.xml',
     SINGLE_BLOB) AS T(x)
     ) AS T(x)
CROSS APPLY x.nodes('/mysqldump/database/table_data[@name="contact"]/row') AS X(row);

SET IDENTITY_INSERT Contact OFF

Upvotes: 3

Views: 2924

Answers (1)

Bob Horn
Bob Horn

Reputation: 34297

After looking into some seemingly painful ways to do this, I found a fairly quick and clean approach.

Since I was able to import the XML file into my local DB, I just generated scripts that I could use to execute on my SQL Azure instance.

Local DB:

  1. Import the XML file like shown in the question above
  2. Right-click on DB -> Tasks -> Generate Scripts
  3. Work through the wizard, specifying the tables I want to script
  4. On the Set Scripting Options tab, click the Advanced button and set the options as shown below.
  5. Finish the wizard

In SSMS, connected to SQL Azure:
1. Use the output/scripts from the above steps to import the data

This link showed some settings to use for the advanced options of generating scripts.

In Advanced Scripting Options
set “Script for the database engine type” option as “SQL Azure Database”
set “Convert UDDTs to Base Types” option as “True”
and set “Types of data to script” option as “Data only”
Click OK.

Upvotes: 1

Related Questions