Trouble with SSIS XML Data Source - SQL Server 2008 R2

Question

I'm having some issues with an XML Data source. Basically I want to hit the xml document, then pull back the two values (status and description) in the document and log them to a table. When I try to look at the available columns in the document I don't see any available in the XML Source Editor SSIS screen, I receive no errors when clicking on the columns tab, just no columns appear to be available. I'm guessing that the xml data isn't complex enough in order to consume it from reading the following sites:

http://resquel.com/ssb/CommentView,guid,4fac4c46-b1e1-48a5-9434-4fa5e3eac48f.aspx http://blogs.msdn.com/b/mattm/archive/2007/12/11/using-xml-source.aspx

However I can't find any documentation that tells me definitively one way or the other. Any help is greatly appreciated!

XML doc here:

<?xml version='1.0' encoding='ISO-8859-1'?>
<Response>
    <Status>Error</Status>
    <Description>Transaction Already Approved</Description>
</Response>

XSD format I'm using here:

<?xml version="1.0"?>
<xs:schema attributeFormDefault="unqualified" elementFormDefault="qualified" xmlns:xs="http://www.w3.org/2001/XMLSchema">
  <xs:element name="Response">
    <xs:complexType>
      <xs:sequence>
        <xs:element minOccurs="0" name="Status" type="xs:string" />
        <xs:element minOccurs="0" name="Description" type="xs:string" />
      </xs:sequence>
    </xs:complexType>
  </xs:element>
</xs:schema>

Amine Chafai · Accepted Answer · 2012-10-04 23:05:54Z

1

modify the generated XSD to add maxOccurs="unbounded attribute as following:

<?xml version="1.0"?>
<xs:schema attributeFormDefault="unqualified" elementFormDefault="qualified" xmlns:xs="http://www.w3.org/2001/XMLSchema">
  <xs:element name="Response">
    <xs:complexType>
      <xs:sequence>
        <xs:element minOccurs="0" maxOccurs="unbounded" name="Status" type="xs:string" />
        <xs:element minOccurs="0" maxOccurs="unbounded" name="Description" type="xs:string" />
      </xs:sequence>
    </xs:complexType>
  </xs:element>
</xs:schema>

Accept the dialog box then you will get your two fields in the combolist in the columns view of the wizard.

I Hope it helps.

answered Oct 4, 2012 at 23:05

Amine Chafai

1882 silver badges10 bronze badges

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

1 Comment

crosan Over a year ago

Thanks Amine! That gets me closer, however now the two elements appear as group names and I'm not able to pull the actual element values out of the document. It almost looks like if the elements had sub elements it would work.

Amine Chafai · Accepted Answer · 2012-10-03 22:16:58Z

0

Your XML document looks simple... did you try to use the "Generate XSD" button of the "XML Source" to generate a simple XSD that could be manageable by SSIS?

answered Oct 3, 2012 at 22:16

Amine Chafai

1882 silver badges10 bronze badges

1 Comment

crosan Over a year ago

Yes, that's where I got the XSD document I attached, I've not altered it at all.

Collectives™ on Stack Overflow

Trouble with SSIS XML Data Source - SQL Server 2008 R2

2 Answers 2

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related