Loading XML data into SQL Server 2008 using SSIS

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>
cipriano555Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

ValentinoVBI ConsultantCommented:
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
cipriano555Author Commented:
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.