• Creator
  • #2141100

    Inserting XML data into SQL table

    by danmc69 ·


    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?

You are posting a reply to: Inserting XML data into SQL table

The posting of advertisements, profanity, or personal attacks is prohibited. Please refer to our Community FAQs for details. All submitted content is subject to our Terms of Use.

All Answers

  • Author
    • #2416017

      Try this

      by jessewalter375 ·

      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

      [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,
      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

      000 000 002
      Alice Freeman

      Your City, YC 1212

      000 000 003
      George Sands

      Your City, YC 1212

      000 000 004
      Mark Oliver

      Your City, YC 1212

      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.

Viewing 0 reply threads