Question

Inserting XML data into SQL table

By danmc69 ·
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?
Thread display: Collapse - | Expand +

All Answers

Collapse -

Try this

by jessewalter375 In reply to Inserting XML data into S ...

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.

<?xml version="1.0" encoding="utf-8"?>
<Customers>
<Customer>
<Document>000 000 000</Document>
<Name>Mary Angel</Name>
<Address>Your City, YC 1212</Address>
<Profession>Systems Analyst</Profession>
</Customer>
<Customer>
<Document>000 000 001</Document>
<Name>John Lenon</Name>
<Address>Your City, YC 1212</Address>
<Profession>Driver</Profession>
</Customer>
<Customer>
<Document>000 000 002</Document>
<Name>Alice Freeman</Name>
<Address>Your City, YC 1212</Address>
<Profession>Architect</Profession>
</Customer>
<Customer>
<Document>000 000 003</Document>
<Name>George Sands</Name>
<Address>Your City, YC 1212</Address>
<Profession>Doctor</Profession>
</Customer>
<Customer>
<Document>000 000 004</Document>
<Name>Mark Oliver</Name>
<Address>Your City, YC 1212</Address>
<Profession>Writer</Profession>
</Customer>
</Customers>
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.

Related Discussions

Related Forums