Solved

SSIS XML xsd with same named elements

Posted on 2011-03-07
5
1,925 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

Question has a verified solution.

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

Data architecture is an important aspect in Software as a Service (SaaS) delivery model. This article is a study on the database of a single-tenant application that could be extended to support multiple tenants. The application is web-based develope…
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 …
Come and listen to Percona CEO Peter Zaitsev discuss what’s new in Percona open source software, including Percona Server for MySQL (https://www.percona.com/software/mysql-database/percona-server) and MongoDB (https://www.percona.com/software/mongo-…
Michael from AdRem Software explains how to view the most utilized and worst performing nodes in your network, by accessing the Top Charts view in NetCrunch network monitor (https://www.adremsoft.com/). Top Charts is a view in which you can set seve…

690 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