Solved

SSIS XML xsd with same named elements

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

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

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

In this article I will describe the Detach & Attach 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.
In this article I will describe the Backup & Restore 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.
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…
Video by: Mark
This lesson goes over how to construct ordered and unordered lists and how to create hyperlinks.

895 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

11 Experts available now in Live!

Get 1:1 Help Now