SSIS 2005 XML Source Problem - No columns shownig
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
February 17th, 2011 1:08pm
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.aspxArthur My Blog
Free Windows Admin Tool Kit Click here and download it now
February 17th, 2011 1:21pm
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.aspxRussel Loski, MCT
February 17th, 2011 1:45pm
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
Free Windows Admin Tool Kit Click here and download it now
February 25th, 2011 1:59pm
This topic is archived. No further replies will be accepted.
Other recent topics
