Using XML Source File with SSIS

Question

I wonder if anyone can advise, I have an xml file as below:

<?xml version="1.0"?>
       <Control>
         <sRulesToRun>11</sRulesToRun>
         <dPointInTime>2011-01-02 00:00:01</dPointInTime>
       </Control>

Using this XSD file

<?xml version="1.0"?> <xsd:schema attributeFormDefault="unqualified" elementFormDefault="qualified"

version="1.0" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
  <xsd:element name="Control">
    <xsd:complexType>
      <xsd:sequence>
        <xsd:element name="sRulesToRun" type="xsd:int" />
        <xsd:element name="dPointInTime" type="xsd:dateTime" />
      </xsd:sequence>
    </xsd:complexType>
  </xsd:element>
</xsd:schema>

But in the SSIS package it doesn't recognise the field names. I'm new to using XML and any help would be very greatfully received.

Many thanks

Answer 1

Try after adding <root></root> in your xml. I guess your xml would be something like

<?xml version="1.0"?>
<root>       
<Control>
         <sRulesToRun>11</sRulesToRun>
         <dPointInTime>2011-01-02 00:00:01</dPointInTime>
       </Control>
 </root>

and your xsd could be

<?xml version="1.0"?>
<xsd:schema attributeFormDefault="unqualified" elementFormDefault="qualified" version="1.0" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
<xsd:element name="root">
    <xsd:complexType>
      <xsd:sequence>
  <xsd:element name="Control">
    <xsd:complexType>
      <xsd:sequence>
        <xsd:element name="sRulesToRun" type="xsd:int" />
        <xsd:element name="dPointInTime" type="xsd:dateTime" />
      </xsd:sequence>
    </xsd:complexType>
  </xsd:element>
</xsd:sequence>
    </xsd:complexType>
  </xsd:element>
</xsd:schema>

Basically, the root tag is missing in the xml file. You can name it anything. Hope it helps.

Answer 2

0

Have you checked the exact case? XML is case sensitive.

answered Apr 12 '12 at 11:24 AM

Kev Riley ♦♦
46k ● 38 ● 43 ● 69

As far as I can tell it's all the same, our database uses case sensitive collation so I'm used to checking it.

Apr 12 '12 at 12:31 PM Mrs_Fatherjack

(comments are locked)

Answer 3

0

Do you still get the issue if you get SSIS to generate the XSD?

answered Apr 12 '12 at 12:43 PM

Dave_Green
1.7k ● 1 ● 4

(comments are locked)

Using XML Source File with SSIS

Follow this question

Related Questions