Solved

SSIS XML xsd with same named elements

Posted on 2011-03-07
5
1,801 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 74

Expert Comment

by:käµfm³d 👽
Comment Utility
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 74

Accepted Solution

by:
käµfm³d   👽 earned 500 total points
Comment Utility
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
Comment Utility
>>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
Comment Utility
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
Comment Utility
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

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

INTRODUCTION: While tying your database objects into builds and your enterprise source control system takes a third-party product (like Visual Studio Database Edition or Red-Gate's SQL Source Control), you can achieve some protection using a sing…
Naughty Me. While I was changing the database name from DB1 to DB_PROD1 (yep it's not real database name ^v^), I changed the database name and notified my application fellows that I did it. They turn on the application, and everything is working. A …
When you create an app prototype with Adobe XD, you can insert system screens -- sharing or Control Center, for example -- with just a few clicks. This video shows you how. You can take the full course on Experts Exchange at http://bit.ly/XDcourse.
This video explains how to create simple products associated to Magento configurable product and offers fast way of their generation with Store Manager for Magento tool.

762 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

Need Help in Real-Time?

Connect with top rated Experts

7 Experts available now in Live!

Get 1:1 Help Now