Solved

SSIS XML xsd with same named elements

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

NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Ssis not sending failure message 2 26
SQL Server (2008) Declare Variable (Date) and Set value 6 32
Trying to get a Linked Server to Oracle DB working 21 60
SQL Help 27 45
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 …
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
Established in 1997, Technology Architects has become one of the most reputable technology solutions companies in the country. TA have been providing businesses with cost effective state-of-the-art solutions and unparalleled service that is designed…
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

809 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