title: How do I import XML files using an SSIS package?
body: <p>I’m trying to import a XML file into SQL server using SSIS. The XML file is structured like this</p>
  
  <pre><code>&lt;?xml version="1.0" encoding="utf-8"?&gt;
  &lt;dataset  xmlns="http://developer.cognos.com/schemas/xmldata/1/"  xmlns:xs="http://www.w3.org/2001/XMLSchema-instance"&gt;
  &lt;!--
  &lt;dataset
      xmlns="http://developer.cognos.com/schemas/xmldata/1/"
      xmlns:xs="http://www.w3.org/2001/XMLSchema-instance"
      xs:schemaLocation="http://developer.cognos.com/schemas/xmldata/1/ xmldata.xsd"
  &gt;
  --&gt;
      &lt;metadata&gt;
            &lt;item name="ORDERNUM" type="xs:string" length="26"/&gt;
            &lt;item name="Business Stream" type="xs:string" length="482"/&gt;
            &lt;item name="ORDERNO" type="xs:decimal" precision="8"/&gt;
            &lt;item name="ORDERTYPE" type="xs:string"/&gt;
            &lt;item name="ITEM_NO" type="xs:decimal" precision="8"/&gt;
            &lt;item name="ITEM_NO2" type="xs:string" length="52"/&gt;
            &lt;item name="PG" type="xs:int" precision="1"/&gt;
            &lt;item name="SG" type="xs:int" precision="1"/&gt;
            &lt;item name="LINEDESC" type="xs:string" length="122"/&gt;
            &lt;item name="CUSTNO" type="xs:decimal" precision="8"/&gt;
            &lt;item name="ORDERQNTY" type="xs:decimal" scale="3" precision="77"/&gt;
            &lt;item name="COST_VALUE" type="xs:decimal" scale="5" precision="77"/&gt;
            &lt;item name="EXTD_LIST" type="xs:decimal" scale="4" precision="77"/&gt;
            &lt;item name="EXTD_VALUE" type="xs:decimal" scale="4" precision="77"/&gt;
            &lt;item name="LINE_NO" type="xs:decimal" precision="9"/&gt;
            &lt;item name="TDATE" type="xs:date"/&gt;
            &lt;item name="TQUARTER" type="xs:decimal" precision="1"/&gt;
            &lt;item name="INVOICEDAT" type="xs:date"/&gt;
            &lt;item name="IQUARTER" type="xs:decimal" precision="1"/&gt;
            &lt;item name="ORIGNUM" type="xs:decimal" precision="10"/&gt;
            &lt;item name="ORIGTYPE" type="xs:string" length="22"/&gt;
            &lt;item name="TYPECALC" type="xs:string"/&gt;
            &lt;item name="SEQ" type="xs:int" precision="1"/&gt;
            &lt;item name="INC_IN_COU" type="xs:string"/&gt;
            &lt;item name="COSTMOD" type="xs:string" length="4"/&gt;
            &lt;item name="GROSSMOD" type="xs:string" length="4"/&gt;
            &lt;item name="CHFLOOR" type="xs:string" length="16"/&gt;
            &lt;item name="Group Customer Description" type="xs:string" length="482"/&gt;
            &lt;item name="Sales Area Description" type="xs:string" length="482"/&gt;
            &lt;item name="Sales Area" type="xs:string" length="8"/&gt;
            &lt;item name="Segment / Region Description" type="xs:string" length="482"/&gt;
            &lt;item name="Segment / Region" type="xs:string" length="8"/&gt;
            &lt;item name="ScheduledDespatchDate" type="xs:date"/&gt;
            &lt;item name="Status Sid" type="xs:int" precision="1"/&gt;
            &lt;item name="ShiptoCustomerNumber" type="xs:decimal" precision="8"/&gt;
            &lt;item name="CONT_TYPE" type="xs:string" length="122"/&gt;
            &lt;item name="EST_START" type="xs:date"/&gt;
            &lt;item name="EST_COMPLTN" type="xs:date"/&gt;
            &lt;item name="ACT_START" type="xs:date"/&gt;
            &lt;item name="ACT_COMPLTN" type="xs:date"/&gt;
            &lt;item name="Project_Status" type="xs:string" length="18"/&gt;
      &lt;/metadata&gt;
      &lt;data&gt;
          &lt;row&gt;
              &lt;value&gt;SO1897977&lt;/value&gt;
              &lt;value&gt;Products &amp;amp; Applications&lt;/value&gt;
              &lt;value&gt;1897977&lt;/value&gt;
              &lt;value&gt;SO&lt;/value&gt;
              &lt;value&gt;731305&lt;/value&gt;
              &lt;value&gt;0670800&lt;/value&gt;
              &lt;value&gt;67&lt;/value&gt;
              &lt;value&gt;3&lt;/value&gt;
              &lt;value&gt;3/4&amp;quot; HM10/8 CI Steam TrapBSP&lt;/value&gt;
              &lt;value&gt;20021&lt;/value&gt;
              &lt;value&gt;1&lt;/value&gt;
              &lt;value&gt;62.136&lt;/value&gt;
              &lt;value&gt;187.1&lt;/value&gt;
              &lt;value&gt;187.1&lt;/value&gt;
              &lt;value&gt;1000&lt;/value&gt;
              &lt;value&gt;2011-11-17&lt;/value&gt;
              &lt;value&gt;4&lt;/value&gt;
              &lt;value&gt;2011-11-17&lt;/value&gt;
              &lt;value&gt;4&lt;/value&gt;
              &lt;value xs:nil="true" /&gt;
              &lt;value&gt; &lt;/value&gt;
              &lt;value&gt;SO&lt;/value&gt;
              &lt;value&gt;1&lt;/value&gt;
              &lt;value&gt;Y&lt;/value&gt;
              &lt;value&gt;N&lt;/value&gt;
              &lt;value&gt;N&lt;/value&gt;
              &lt;value&gt;PDQ/KSP&lt;/value&gt;
              &lt;value&gt;Other Customers&lt;/value&gt;
              &lt;value&gt;Crumb Jonathan&lt;/value&gt;
              &lt;value&gt;917&lt;/value&gt;
              &lt;value&gt;Southern Division&lt;/value&gt;
              &lt;value&gt;STH&lt;/value&gt;
              &lt;value&gt;2011-11-17&lt;/value&gt;
              &lt;value&gt;70&lt;/value&gt;
              &lt;value&gt;60206&lt;/value&gt;
              &lt;value xs:nil="true" /&gt;
              &lt;value&gt;1900-01-01&lt;/value&gt;
              &lt;value&gt;1900-01-01&lt;/value&gt;
              &lt;value&gt;1900-01-01&lt;/value&gt;
              &lt;value&gt;1900-01-01&lt;/value&gt;
              &lt;value xs:nil="true" /&gt;
          &lt;/row&gt;
          &lt;row&gt;
              &lt;value&gt;SO1897977&lt;/value&gt;
              &lt;value&gt;Products &amp;amp; Applications&lt;/value&gt;
              &lt;value&gt;1897977&lt;/value&gt;
              &lt;value&gt;SO&lt;/value&gt;
              &lt;value&gt;799262&lt;/value&gt;
              &lt;value&gt;1643100&lt;/value&gt;
              &lt;value&gt;164&lt;/value&gt;
              &lt;value&gt;60&lt;/value&gt;
              &lt;value&gt;1/2&amp;quot; FIG12 Bronze Y Type BSP+ 0.8 SS&lt;/value&gt;
              &lt;value&gt;20021&lt;/value&gt;
              &lt;value&gt;5&lt;/value&gt;
              &lt;value&gt;54.051&lt;/value&gt;
              &lt;value&gt;130.2&lt;/value&gt;
              &lt;value&gt;130.2&lt;/value&gt;
              &lt;value&gt;2000&lt;/value&gt;
              &lt;value&gt;2011-11-17&lt;/value&gt;
              &lt;value&gt;4&lt;/value&gt;
              &lt;value&gt;2011-11-17&lt;/value&gt;
              &lt;value&gt;4&lt;/value&gt;
              &lt;value xs:nil="true" /&gt;
              &lt;value&gt; &lt;/value&gt;
              &lt;value&gt;SO&lt;/value&gt;
              &lt;value&gt;1&lt;/value&gt;
              &lt;value&gt;Y&lt;/value&gt;
              &lt;value&gt;N&lt;/value&gt;
              &lt;value&gt;N&lt;/value&gt;
              &lt;value&gt;PDQ/KSP&lt;/value&gt;
              &lt;value&gt;Other Customers&lt;/value&gt;
              &lt;value&gt;Crumb Jonathan&lt;/value&gt;
              &lt;value&gt;917&lt;/value&gt;
              &lt;value&gt;Southern Division&lt;/value&gt;
              &lt;value&gt;STH&lt;/value&gt;
              &lt;value&gt;2011-11-17&lt;/value&gt;
              &lt;value&gt;70&lt;/value&gt;
              &lt;value&gt;60206&lt;/value&gt;
              &lt;value xs:nil="true" /&gt;
              &lt;value&gt;1900-01-01&lt;/value&gt;
              &lt;value&gt;1900-01-01&lt;/value&gt;
              &lt;value&gt;1900-01-01&lt;/value&gt;
              &lt;value&gt;1900-01-01&lt;/value&gt;
              &lt;value xs:nil="true" /&gt;
          &lt;/row&gt;
  </code></pre>
  
  <p>I have made a XSD file that looks like this</p>
  
  <pre><code>&lt;?xml version="1.0"?&gt;
  &lt;schema xmlns:xs="http://www.w3.org/2001/XMLSchema-instance" attributeFormDefault="unqualified" elementFormDefault="qualified" targetNamespace="http://developer.cognos.com/schemas/xmldata/1/" xmlns="http://www.w3.org/2001/XMLSchema"&gt;
    &lt;element name="dataset"&gt;
      &lt;complexType&gt;
        &lt;sequence&gt;
          &lt;element minOccurs="0" name="data"&gt;
            &lt;complexType&gt;
              &lt;sequence&gt;
                &lt;element minOccurs="0" maxOccurs="1" name="row"&gt;
                  &lt;complexType&gt;
                    &lt;sequence&gt;
                        &lt;element name="ORDERNUM" type="string" /&gt;
                        &lt;element name="BusinessStream" type="string" /&gt;
                        &lt;element name="ORDERNO" type="decimal"/&gt;
                        &lt;element name="ORDERTYPE" type="string"/&gt;
                        &lt;element name="ITEM_NO" type="decimal" /&gt;
                        &lt;element name="ITEM_NO2" type="string" /&gt;
                        &lt;element name="PG" type="int" /&gt;
                        &lt;element name="SG" type="int" /&gt;
                        &lt;element name="LINEDESC" type="string" /&gt;
                        &lt;element name="CUSTNO" type="decimal" /&gt;
                        &lt;element name="ORDERQNTY" type="decimal" /&gt;
                        &lt;element name="COST_VALUE" type="decimal" /&gt;
                        &lt;element name="EXTD_LIST" type="decimal" /&gt;
                        &lt;element name="EXTD_VALUE" type="decimal"/&gt;
                        &lt;element name="LINE_NO" type="decimal" /&gt;
                        &lt;element name="TDATE" type="date"/&gt;
                        &lt;element name="TQUARTER" type="decimal" /&gt;
                        &lt;element name="INVOICEDAT" type="date"/&gt;
                        &lt;element name="IQUARTER" type="decimal" /&gt;
                        &lt;element name="ORIGNUM" type="decimal" /&gt;
                        &lt;element name="ORIGTYPE" type="string" /&gt;
                        &lt;element name="TYPECALC" type="string"/&gt;
                        &lt;element name="SEQ" type="int" /&gt;
                        &lt;element name="INC_IN_COU" type="string"/&gt;
                        &lt;element name="COSTMOD" type="string" /&gt;
                        &lt;element name="GROSSMOD" type="string" /&gt;
                        &lt;element name="CHFLOOR" type="string" /&gt;
                        &lt;element name="GroupCustomerDescription" type="string" /&gt;
                        &lt;element name="SalesAreaDescription" type="string" /&gt;
                        &lt;element name="SalesArea" type="string" /&gt;
                        &lt;element name="SegmentRegionDescription" type="string" /&gt;
                        &lt;element name="SegmentRegion" type="string" /&gt;
                        &lt;element name="ScheduledDespatchDate" type="date"/&gt;
                        &lt;element name="StatusSid" type="int" /&gt;
                        &lt;element name="ShiptoCustomerNumber" type="decimal" /&gt;
                        &lt;element name="CONT_TYPE" type="string" /&gt;
                        &lt;element name="EST_START" type="date"/&gt;
                        &lt;element name="EST_COMPLTN" type="date"/&gt;
                        &lt;element name="ACT_START" type="date"/&gt;
                        &lt;element name="ACT_COMPLTN" type="date"/&gt;
                        &lt;element name="Project_Status" type="string" /&gt;
                    &lt;/sequence&gt;
                  &lt;/complexType&gt;
                &lt;/element&gt;
              &lt;/sequence&gt;
            &lt;/complexType&gt;
          &lt;/element&gt;
        &lt;/sequence&gt;
      &lt;/complexType&gt;
    &lt;/element&gt;
  &lt;/schema&gt;
  </code></pre>
  
  <p>The problem is when I run the task I get the correct number of rows but all the values are null. I’m quite new to XML so I suspect it is something really silly, can anyone help?</p>