Solved

SSIS XML xsd with same named elements

Posted on 2011-03-07
5
1,898 Views
Last Modified: 2012-05-11
Hi -
(This isn't under the SSIS zone because I can't find it)

I have a XML file. Well a bunch of them but they all have the same schema. A few types/sequences/etc. And they need to go into my database. One table for each sequence - so nothing too troublesome.

I have the XSD for it..and practically it's this
<?xml version="1.0"?>
<xs:schema attributeFormDefault="unqualified" elementFormDefault="qualified" xmlns:xs="http://www.w3.org/2001/XMLSchema">
  <xs:element name="Records">
    <xs:complexType>
      <xs:sequence>
        <xs:element minOccurs="0" name="Date" type="xs:string" />
        <xs:element minOccurs="0" name="Time" type="xs:string" />
        <xs:element minOccurs="0" maxOccurs="unbounded" name="Entity">
          <xs:complexType>
            <xs:sequence>
              <xs:choice maxOccurs="unbounded">
                <xs:element minOccurs="0" name="EID" type="xs:unsignedLong" />
                <xs:element minOccurs="0" name="State" type="xs:unsignedByte" />
                <xs:element minOccurs="0" name="County" type="xs:unsignedShort" />
                <xs:element minOccurs="0" name="LeaseName" type="xs:string" />
                <xs:element minOccurs="0" name="OperatorName" type="xs:string" />
                <xs:element minOccurs="0" maxOccurs="unbounded" name="Location1">
                  <xs:complexType>
                    <xs:sequence>
                      <xs:choice maxOccurs="unbounded">
                        <xs:element minOccurs="0" name="EID" type="xs:unsignedLong" />
                        <xs:element minOccurs="0" name="State" type="xs:unsignedByte" />
                        <xs:element minOccurs="0" name="County" type="xs:unsignedByte" />
                        <xs:element minOccurs="0" name="Latitude" type="xs:decimal" />
                        <xs:element minOccurs="0" name="Longitude" type="xs:decimal" />
                        </xs:choice>
                    </xs:sequence>
                  </xs:complexType>
                </xs:element>
              </xs:sequence>
          </xs:complexType>
        </xs:element>
      </xs:sequence>
    </xs:complexType>
  </xs:element>
</xs:schema>

Open in new window


So my initial thought for this was to put it in SSIS and have that work it's magic. Well when I do - i get this error  
Error: 0xC02092AF at Data Flow Task, XML Source [1]: The component "XML Source" (1) was unable to process the XML data. The Xml source document contains multiple "State" elements and maxOccurs=1 was specified in the Xml schema.

Open in new window


I haven't done a  lot with xml but I'm assuming it's because there's multiple elements of the same name, regardless of their parent?

Is there a way to get around this or should I just start writing something in code?

Or would xquery be a better way to process these?

Thoughts? Tips?

Thanks!
0
Comment
Question by:rmm2001
  • 2
  • 2
5 Comments
 
LVL 75

Expert Comment

by:käµfm³d 👽
ID: 35063226
I don't see it listed in the above schema, but the problem is that at some level in your XML, you have multiple State elements

e.g.

...
<SomeParent>
    <State>some data</State>
    <State>some data</State>
    <State>some data</State>
</SomeParent>

Open in new window


but your schema says you can only have one. Having State nodes at different levels (think indentation) is not the problem; it's only at this one particular level that having multiple state nodes is bad. You need to track down the schema that defines this particular State node and inspect its definition in order to correct you XML.
0
 
LVL 75

Accepted Solution

by:
käµfm³d   👽 earned 500 total points
ID: 35063255
P.S.

Think of XML like you think of some folder on your computer--a folder has a parent folder (excluding the root folder). A folder has a name. Naming the folder the same name as the parent folder doesn't cause a conflict, does it? Each time you indent in XML, think of it like opening up a sub-folder. You can repeat a folder name on a sub-folder indefinitely, without conflict. It's only when you try to name two folders in the same directory with the same name that a conflict would arise. This is not to say XML behaves exactly like that, because you can repeat elements with the same name under the same parent, so long as the defining schema allows it, which in this case it does not.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 35063649
>>This isn't under the SSIS zone because I can't find it<<
http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/
SSIS is child node of the parent MS-SQL-Server :)
0
 
LVL 7

Author Comment

by:rmm2001
ID: 35070743
What you're saying makes sense...but I been looking through a few of the files and I don't see double state elements. Is there another reason that could be causing this? Or do you think I should look again?
0
 
LVL 7

Author Comment

by:rmm2001
ID: 35071823
Hmm - I think you're right. I think it is bad XML. When I say "generate new XSD" it gives me something with all of my elements under no parent node. So it makes perfectly good sense...now on to figuring out how to handle this.

Thanks!

And thanks @acperkins for telling me where SSIS is!
0

Featured Post

Revamp Your Training Process

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Introduction: When running hybrid database environments, you often need to query some data from a remote db of any type, while being connected to your MS SQL Server database. Problems start when you try to combine that with some "user input" pass…
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

726 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question