Solved

Loading XML data into SQL Server 2008 using SSIS

Posted on 2012-03-10
2
533 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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

On Demand Webinar: Networking for the Cloud Era

Did you know SD-WANs can improve network connectivity? Check out this webinar to learn how an SD-WAN simplified, one-click tool can help you migrate and manage data in the cloud.

Question has a verified solution.

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

After restoring a Microsoft SQL Server database (.bak) from backup or attaching .mdf file, you may run into "Error '15023' User or role already exists in the current database" when you use the "User Mapping" SQL Management Studio functionality to al…
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
Monitoring a network: why having a policy is the best policy? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the enormous benefits of having a policy-based approach when monitoring medium and large networks. Software utilized in this v…
This tutorial will teach you the special effect of super speed similar to the fictional character Wally West aka "The Flash" After Shake : http://www.videocopilot.net/presets/after_shake/ All lightning effects with instructions : http://www.mediaf…

627 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