x

Using XML Source File with SSIS

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
more ▼

asked Apr 12 '12 at 11:21 AM in Default

Mrs_Fatherjack gravatar image

Mrs_Fatherjack
4.7k 57 62 66

(comments are locked)
10|1200 characters needed characters left

3 answers: sort voted first

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.
more ▼

answered Apr 12 '12 at 01:08 PM

Usman Butt gravatar image

Usman Butt
13.9k 6 8 14

That's done it, many thanks.
Apr 12 '12 at 03:38 PM Mrs_Fatherjack
(comments are locked)
10|1200 characters needed characters left
Have you checked the exact case? XML is case sensitive.
more ▼

answered Apr 12 '12 at 11:24 AM

Kev Riley gravatar image

Kev Riley ♦♦
50.6k 43 49 76

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)
10|1200 characters needed characters left
Do you still get the issue if you get SSIS to generate the XSD?
more ▼

answered Apr 12 '12 at 12:43 PM

Dave_Green gravatar image

Dave_Green ♦
3.6k 4 7

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x897
x136
x4

asked: Apr 12 '12 at 11:21 AM

Seen: 881 times

Last Updated: Apr 12 '12 at 03:38 PM