Solved

Loading XML data into SQL Server 2008 using SSIS

Posted on 2012-03-10
2
529 Views
Last Modified: 2012-06-22
I'm experimenting with SQL Server 2008 and XML

I have a simple XML file I'd like to load.  Here it is:

<order>
  <location location_id="1">
    <item SKU="10001" Quantity="1" />
    <item SKU="10002" Quantity="3" />
  </location>
  <location location_id="2">
    <item SKU="10001" Quantity="5" />
    <item SKU="10005" Quantity="2" />
  </location>
</order>


I want to load that into a table with this definition:

CREATE TABLE [dbo].[ORDERS](
      [ORDER_ID] [int] IDENTITY(1,1) NOT NULL,
      [ORDER] [xml] NULL
) ON [PRIMARY]


Doing a simple update command in ssms worked:

INSERT ORDERS([ORDER]) VALUES
('<order>
<location location_id = "1">
<item SKU = "10001" Quantity = "1" />
<item SKU = "10002" Quantity = "3" />
</location>
<location location_id = "2">
<item SKU = "10001" Quantity = "5" />
<item SKU = "10005" Quantity = "2" />
</location>
</order>')

But I want to load these in from external files, so I'll use SSIS to do it.

I built a simple SSIS application, with a Data Flow task, then, an XML Source and OLE DB Destination task.  I could not get it to work.  I configured the XML task without error.  I was expecting the output of the XML task to be an Order, but no....it gave me a choice of item or location.  Strange, didn't know what to do with that.  Nothing I tried worked.  

BTW, this XLD was generated by the XML Source task

<?xml version="1.0"?>
<xs:schema attributeFormDefault="unqualified" elementFormDefault="qualified" xmlns:xs="http://www.w3.org/2001/XMLSchema">
  <xs:element name="order">
    <xs:complexType>
      <xs:sequence>
        <xs:element minOccurs="0" maxOccurs="unbounded" name="location">
          <xs:complexType>
            <xs:sequence>
              <xs:element minOccurs="0" maxOccurs="unbounded" name="item">
                <xs:complexType>
                  <xs:attribute name="SKU" type="xs:unsignedShort" use="optional" />
                  <xs:attribute name="Quantity" type="xs:unsignedByte" use="optional" />
                </xs:complexType>
              </xs:element>
            </xs:sequence>
            <xs:attribute name="location_id" type="xs:unsignedByte" use="optional" />
          </xs:complexType>
        </xs:element>
      </xs:sequence>
    </xs:complexType>
  </xs:element>
</xs:schema>
0
Comment
Question by:cipriano555
2 Comments
 
LVL 37

Accepted Solution

by:
ValentinoV earned 500 total points
ID: 37706710
0
 

Author Comment

by:cipriano555
ID: 37707572
Interesting.  The first (excellent) article explains why I saw what I saw.  The XML Source task doesn't load an XML document into an XML-typed column (that would be too obvious!).  It shreds the data making it ready to load into a collection of regular relational tables. That is actually kinda useful because I was going to end up shreding the document anyway.  Course, I'm still curious how you load an XML document into an XML-typed field, and maybe that is where the XML task comes to play.  Let me try a few things out and I'll get back.

Thanks
0

Featured Post

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

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

Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
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.
This Micro Tutorial will give you a basic overview how to record your screen with Microsoft Expression Encoder. This program is still free and open for the public to download. This will be demonstrated using Microsoft Expression Encoder 4.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

785 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