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

 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, 2012 at 11:21 AM in Default

avatar image

Mrs_Fatherjack
5.1k 64 66 75

(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, 2012 at 01:08 PM

avatar image

Usman Butt
13.9k 6 13 21

That's done it, many thanks.

Apr 12, 2012 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, 2012 at 11:24 AM

avatar image

Kev Riley ♦♦
64k 48 61 81

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, 2012 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, 2012 at 12:43 PM

avatar image

Dave_Green ♦
5.5k 4 5 10

(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.

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:

x1147
x186
x4

asked: Apr 12, 2012 at 11:21 AM

Seen: 1962 times

Last Updated: Apr 12, 2012 at 03:38 PM

Copyright 2016 Redgate Software. Privacy Policy