Load XML file into SQL Azure

Question

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

Bob Horn · Accepted Answer · 2013-04-08 02:30:18Z

1

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:

Import the XML file like shown in the question above
Right-click on DB -> Tasks -> Generate Scripts
Work through the wizard, specifying the tables I want to script
On the Set Scripting Options tab, click the Advanced button and set the options as shown below.
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.

answered Apr 8, 2013 at 2:30

Bob Horn

34.5k36 gold badges122 silver badges238 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Vic Over a year ago

Hi Bob. I know some time has passed, but could you tell me where is "the question above"? In how to import the XML file?

Bob Horn Over a year ago

It's the question that's on this page.

Collectives™ on Stack Overflow

Load XML file into SQL Azure

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related