[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

import XML data into a SQL Database

Posted on 2009-12-30
13
Medium Priority
?
310 Views
Last Modified: 2012-05-08
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
0
Comment
Question by:TheUndecider
13 Comments
 
LVL 3

Expert Comment

by:tsqlguy
ID: 26149353
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...
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 26151583
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.
0
 
LVL 5

Expert Comment

by:sureshchsahu
ID: 26151956
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
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:TheUndecider
ID: 26154986
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!
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 26163215
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)

0
 

Author Comment

by:TheUndecider
ID: 26176111
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.
0
 
LVL 75

Accepted Solution

by:
Anthony Perkins earned 2000 total points
ID: 26176630
It depends if:
1. You are passing in the Xml document as a parameter to a Stored Procedure, then it is the name of the parameter.  

2. You are using a column called XMLCol which is Xml data type in a table called XMLData1 then it would look something like this:
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      XMLData1
            CROSS APPLY XMLCol.nodes('ShopSiteOrders/Order') T(c)
0
 

Author Comment

by:TheUndecider
ID: 26183383
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)

0
 

Author Comment

by:TheUndecider
ID: 26183669
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)
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 26184793
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.
0
 

Author Comment

by:TheUndecider
ID: 26185320
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.  
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 26186417
It looks Ok.
0
 

Author Closing Comment

by:TheUndecider
ID: 31671391
Thanks a lot for your help!   I really appreciate it.
0

Featured Post

Prep for the ITIL® Foundation Certification Exam

December’s Course of the Month is now available! Enroll to learn ITIL® Foundation best practices for delivering IT services effectively and efficiently.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I am showing a way to read/import the excel data in table using SQL server 2005... Suppose there is an Excel file "Book1" at location "C:\temp" with column "First Name" and "Last Name". Now to import this Excel data into the table, we will use…
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Loops Section Overview
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…

825 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question