Software·43,928 discussions

Inserting XML data into SQL table

Tags: Microsoft

I have 2 tables in MS SQL which I want to insert XML data:Address and Merchant.Address already contains inserted data, the other one(Merchant) has a foreign key (IdAddress), to link the tables.My wish is to insert data into the table Merchant, so when I insert the XML data, the record could be linked with the already inserted record from table Address.Any ideas how to do it?

Topic

Try this

In reply to Inserting XML data into SQL table

Step 1 – Create table to store imported data
Let’s create a simple table that’ll store the data of our customers.

USE mssqltips_db
GO

CREATE TABLE [CUSTOMERS_TABLE](
[ID] [int] IDENTITY(1,1) NOT NULL,
[DOCUMENT] [varchar](20) NOT NULL,
[NAME] [varchar](50) NOT NULL,
[ADDRESS] [varchar](50) NOT NULL,
[PROFESSION] [varchar](50) NOT NULL,
CONSTRAINT [CUSTOMERS_PK] PRIMARY KEY ([Id])
)
GO
Step 2 – Create Sample XML File
Below is sample XML data. You can use this as is or modify for your own tests. I copied this data and stored in a file named MSSQLTIPS_XML.xml.

000 000 000
Mary Angel

Your City, YC 1212

Systems Analyst

000 000 001
John Lenon

Your City, YC 1212

Driver

000 000 002
Alice Freeman

Your City, YC 1212

Architect

000 000 003
George Sands

Your City, YC 1212

Doctor

000 000 004
Mark Oliver

Your City, YC 1212

Writer

Step 3 – Importing the XML data file into a SQL Server Table
Now all we need is to make SQL Server read the XML file and import the data via the OPENROWSET function. This function is native to T-SQL and allows us to read data from many different file types through the BULK import feature, which allows the import from lots of file types, like XML.

[Author]

Replies