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
Solved

Loading XML data into SQL Server 2008 using SSIS

Posted on 2012-03-10
2
530 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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Long way back, we had to take help from third party tools in order to encrypt and decrypt data.  Gradually Microsoft understood the need for this feature and started to implement it by building functionality into SQL Server. Finally, with SQL 2008, …
Hi all, It is important and often overlooked to understand “Database properties”. Often we see questions about "log files" or "where is the database" and one of the easiest ways to get general information about your database is to use “Database p…
Microsoft Active Directory, the widely used IT infrastructure, is known for its high risk of credential theft. The best way to test your Active Directory’s vulnerabilities to pass-the-ticket, pass-the-hash, privilege escalation, and malware attacks …
In a recent question (https://www.experts-exchange.com/questions/29004105/Run-AutoHotkey-script-directly-from-Notepad.html) here at Experts Exchange, a member asked how to run an AutoHotkey script (.AHK) directly from Notepad++ (aka NPP). This video…

856 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