XML from webservice to SQL Server using SSIS

Question

I have a web service task that saves the XML output to a variable.

The question now is what would the variable type be? Also how can I then transfer the XML variable to the SQL Server database? I can't seem to make the data flow work, XML source to ole db source? Am I on the right path? thanks

        <xs:element name="Table">
        <xs:complexType>
        <xs:sequence>
        <xs:element name="FlightDate" type="xs:dateTime" minOccurs="0"/>
        <xs:element name="FlightNo" type="xs:string" minOccurs="0"/>
        </xs:sequence>
        </xs:complexType>
        </xs:element>

i need to save flight date and flight no to a temporary table in sql. it must have its own column like flightdate and flight no

enter image description here

ram.bi · Accepted Answer · 2013-04-18 07:30:56Z

1

Change XML Source Data Acces Mode to XML Data From Variable,

Then you can give "User::FlightInfoXML" Variable.

answered Apr 18, 2013 at 7:30

ram.bi

2834 silver badges16 bronze badges

Sign up to request clarification or add additional context in comments.

10 Comments

ram.bi Over a year ago

Then where you exactly struck ?

anonymous1110 Over a year ago

added new ss, how can i then map the columns of xml? where do i go from here? do I have to create xsd file?

ram.bi Over a year ago

Yes, You have to create XSD. Easily you can generate it,By Clicking on "Generate XSD" Button. Just you have to specify path where you want to keep that XSD File on disk.

anonymous1110 Over a year ago

tried hitting the generate button it says that variable flightinfoxml does not contain xml

ram.bi Over a year ago

let us continue this discussion in chat

|

Collectives™ on Stack Overflow

XML from webservice to SQL Server using SSIS

1 Answer 1

10 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

10 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related