asked on

import XML data into a SQL Database

Hello,

I fairly new using XML data. I need to import the attached xml data into a SQL server database. I want to store the info it 2 tables: an Orders table that will stored the all the orders information (including customer name, billing, and shipping info) and a Products one that will store the products for each order.

In this sample XML, there are 3 orders. The first one has 2 products, the second one has 1 and the third one has 3.

The main problem is that I have no idea how to make the each data part to go into their respective table.

I've tried to create a schema for this XLS in Visual Studio 2003, but I got this error:

Failed to create a schema for this data file because:
Although this XML document is well formed, it contains structure that Data View canno Display.

The same table (NameParts) cannot be the child table in two nested relations.

Any help will be tremendously appreciated it.

Thanks!
orders.xml.txt

tsqlguy

Here is a great article! Should help you out!

http://weblogs.sqlteam.com/mladenp/archive/2007/06/18/60235.aspx

This definitely helped me out...

Anthony Perkins

You have a couple of options:
1. Using OPENXML()
2. (with SQL Server 2005/2008) using the new Xml Data methods.

Post the structure of the two tables and how they are matched from the Xml if you are still struggling.

sureshchsahu

The below link will give u a clear idea how to import XML data to SQL Server

http://www.sqlservercurry.com/2008/09/how-to-import-xml-data-in-sql-server.html

TheUndecider

ASKER

Hello again,

I was able to create the xsd schema and a table (XMLData1) where I inserted the XML data in a XML column called XMLCol. Now I need to retrieve it and place it in the 2 tables the orders need to go to.

Here's the 2 table structure

Orders table:

CREATE TABLE [dbo].[Orders](
      [InacPrim] [int] IDENTITY(1,1) NOT NULL,
      [OrderID] [int] NULL,
      [FirstName] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
      [LastName] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
      [Email] [varchar](60) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
      [Address1] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
      [Address2] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
      [City] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
      [State] [varchar](6) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
      [ZipCode] [varchar](2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
      [GrandTotal] [money] NULL
) ON [PRIMARY]

Products table:

CREATE TABLE [dbo].[Products](
      [InacPrim] [int] IDENTITY(1,1) NOT NULL,
      [OrderID] [int] NULL,
      [ProductName] [varchar](300) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
      [Quantity] [int] NULL,
      [ItemPrice] [money] NULL,
      [Total] [money] NULL,
      [CustomerText] [varchar](200) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]

They have a 1 to many relationship and they are matched to the XML based on the field type.

How do I extract the data into these 2 tables?

Thanks again!

Anthony Perkins

This is how you could do it with the Orders table:

INSERT      Orders(FirstName, LastName, Email, Address1, Address2, City, State, ZIPCode, GrandTotal)
Select      c.value('(Billing/NameParts/FirstName)[1]', 'varchar(50)'),
            c.value('(Billing/NameParts/LastName)[1]', 'varchar(50)'),
            c.value('(Billing/Email)[1]', 'varchar(60)'),
            c.value('(Billing/Address/Street1)[1]', 'varchar(50)'),
            c.value('(Billing/Address/Street2)[1]', 'varchar(50)'),
            c.value('(Billing/Address/City)[1]', 'varchar(50)'),
            c.value('(Billing/Address/State)[1]', 'varchar(2)'),
            c.value('(Billing/Address/Code)[1]', 'varchar(6)'),
            c.value('(Totals/GrandTotal)[1]', 'money')
From      @MyXml.nodes('ShopSiteOrders/Order') T(c)

Incidentally I suspect you have this reversed:
[State] [varchar](6) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[ZipCode] [varchar](2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

It should be:
[State] [varchar](2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[ZipCode] [varchar](6) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

The Products table can the populated as follows:
INSERT      Products(ProductName, Quantity, ItemPrice, Total, CustomerText)
Select      c.value('(Name)[1]', 'varchar(300)'),
            c.value('(Quantity)[1]', 'int'),
            c.value('(ItemPrice)[1]', 'money'),
            c.value('(Total)[1]', 'money'),
            c.value('(CustomerText)[1]', 'varchar(200)')
From      @MyXml.nodes('ShopSiteOrders/Order/Shipping/Products/Product') T(c)

TheUndecider

ASKER

ACPerkins,

You're right, I switched the zip and state. Thanks for pointing that out.

I'm trying to use your example, but I get an error that's probably easy to solve.

error: Must declare the scalar variable "@MyXml".

Is that variable supposed to be either the table (XMLData1), the column (XMLCol) or the schema (it's called @MyOrders)?

Thanks again.

ASKER CERTIFIED SOLUTION

Anthony Perkins

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

TheUndecider

ASKER

Hi AC,

Thanks a lot for your input. I used your suggestion number 2 and it works! I am able to insert the Orders in the Orders table. However, when I try to do the same with the Products, I get stuck.

This is what I am trying with the Products right now. I need the Order ID there too because the Orders and Products table are joined by the Order ID.

INSERT Products(OrderID, ProductName, Quantity, ItemPrice, Total, CustomerText)

Select c.value('(OrderID)[1]', 'int'),
c.value('(Shipping/Products/Product/ProductName)[1]', 'varchar(300)'),
c.value('(Shipping/Products/Product/ProductQuantity)[1]', 'int'),
c.value('(Shipping/Products/Product/ProductItemPrice)[1]', 'money'),
c.value('(Shipping/Products/Product/ProductTotal)[1]', 'money'),
c.value('(Shipping/Products/Product/ProductCustomerText)[1]', 'varchar(200)')

FROM XMLData1
CROSS APPLY XMLCol.nodes('ShopSiteOrders/Order') T(c)

TheUndecider

ASKER

I meant OrderNumber in the select part. OrderID is in the tables.

INSERT Products(OrderID, ProductName, Quantity, ItemPrice, Total, CustomerText)

Select c.value('(OrderNumber)[1]', 'int'),
c.value('(Shipping/Products/Product/ProductName)[1]', 'varchar(300)'),
c.value('(Shipping/Products/Product/ProductQuantity)[1]', 'int'),
c.value('(Shipping/Products/Product/ProductItemPrice)[1]', 'money'),
c.value('(Shipping/Products/Product/ProductTotal)[1]', 'money'),
c.value('(Shipping/Products/Product/ProductCustomerText)[1]', 'varchar(200)')

FROM XMLData1
CROSS APPLY XMLCol.nodes('ShopSiteOrders/Order') T(c)

Anthony Perkins

You can certainly get the OrderNumber that way, but how is that going to help you? You are not saving that element in the Orders table.

TheUndecider

ASKER

Well.. yes, I am supposed to save it in both tables. I added it to my Orders insert and it worked.

I tried another way and I believe I got it. I can insert my products using this query

INSERT Products(OrderID, ProductName, Quantity, ItemPrice, Total, CustomerText)

Select c.query('../../../OrderNumber').value('.', 'int'),
c.value('(Name)[1]', 'varchar(300)'),
c.value('(Quantity)[1]', 'int'),
c.value('(ItemPrice)[1]', 'money'),
c.value('(Total)[1]', 'money'),
c.value('(CustomerText)[1]', 'varchar(200)')
From t1
CROSS APPLY XMLCol.nodes('ShopSiteOrders/Order/Shipping/Products/Product') T(c)

Before closing this question, do you see anything wrong with it? I can get what I want, but I am not sure whether this is 100% ok.

Anthony Perkins

It looks Ok.

TheUndecider

ASKER

Thanks a lot for your help! I really appreciate it.