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?
 
ValentinoVConnect With a Mentor BI ConsultantCommented:
0
 
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.