SSIS 2005 XML Source Problem - No columns shownig

Question

Problem: XML Source shows no columns

I have a file that is sent to me once a day. Need to automate the import of file into SQL Server 2005 table. I'm trying to load this xml file source "SampleOne.xml":

<?xml version="1.0"?>
<SampleOne xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns="http://www.whatever.com/schema/2009-01/motor/xdr" >
    <AsOfDate>02/14/2011 16:00:00</AsOfDate>
    <SampleOne>  24325.55</SampleOne>
</SampleOne>

I've created a project with a data flow and within the data flow I've added an XML source component. Referencing my SampleOne.xml file I've used the "Generate XSD..." to create SampleOne.xsd. Clicking on columns displays nothing:

<?xml version="1.0"?>
<xs:schema xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" attributeFormDefault="unqualified" elementFormDefault="qualified" targetNamespace="http://www.whatever.com/schema/2009-01/motor/xdr" xmlns:xs="http://www.w3.org/2001/XMLSchema">
 <xs:element name="SampleOne">
  <xs:complexType>
   <xs:sequence>
    <xs:element minOccurs="0" name="AsOfDate" type="xs:string" />
    <xs:element minOccurs="0" name="SampleOne" type="xs:decimal" />
   </xs:sequence>
  </xs:complexType>
 </xs:element>
</xs:schema>

What is wrong here? What are my work arounds?

---

Brett L. Brown

Answer 1

You need additional external nodes around your XML, otherwise you must see just a single picked up.

This is a good post in general on how to: http://blogs.msdn.com/b/mattm/archive/2007/12/11/using-xml-source.aspx

---

Arthur My Blog

Answer 2

This is the problem of the Root node not showing.  It treats the root node as a kind table with rows.  I suggest looking at Matt Masson's blog about using XSLT to add a root outside of your root.  http://blogs.msdn.com/b/mattm/archive/2007/12/15/xml-source-making-things-easier-with-xslt.aspx

---

Russel Loski, MCT

Answer 3

XSLT is NOT a simple solution. I ended up using the "Execute Process Task" and crafting my own C# specific console app executable to parse the file and add the root node.

// Source: Article ID: 307548 - Last Review: March 20, 2008 - Revision: 4.1 Microsoft Support

using System;
using System.Collections.Generic;
using System.IO;
using System.Text;
using System.Xml;

namespace XmlFileReader
{
  class Program
  {
    static void Main(string[] arg)
    {
      string setPathToFilename = parseArgs(arg);

      if (setPathToFilename.Length > 0)
      {
        Console.WriteLine("Past Length check...");
        if (File.Exists(setPathToFilename))
        {
          XmlTextReader reader = new XmlTextReader(setPathToFilename);
          reader.WhitespaceHandling = WhitespaceHandling.None;

          List<string> xmlValues = new List<string>();

          while (reader.Read())
          {
            switch (reader.NodeType)
            {
              case XmlNodeType.Text:
                xmlValues.Add(reader.Value.Trim());
                break;
            }
          }

          string customTextFile = setPathToFilename.Replace(".xml", ".txt");

          if (File.Exists(customTextFile))
          {
            File.Delete(customTextFile);
          }

          if (!File.Exists(customTextFile))
          {
            using (StreamWriter sw = File.CreateText(customTextFile))
            {
              sw.Write("\"");
              sw.Write(xmlValues[0]);
              sw.Write("\",\"");
              sw.Write(xmlValues[1]);
              sw.WriteLine("\"");
            }
          }
        }
      }
    }

    private static string parseArgs(string[] arg)
    {
      try
      {
        if (arg.Length == 0)
        {
          Console.WriteLine("XmlFileReader syntax: ");
          Console.WriteLine();

          Console.WriteLine("XmlFileReader [Path_And_Filename]");
          Console.WriteLine(" Path_And_Filename");
          Console.WriteLine();

          Console.WriteLine("The path to the Ercot XML file must be specified.");
          Console.WriteLine();
          return "";
        }

        string userParameter = arg[0];
        return userParameter;
      }
      catch (Exception ex)
      {
        Console.WriteLine(ex.Message);
        return "";
      }
    }
  }
}

 

---

Brett L. Brown