Help with developing XSD schema for client wanting to deliver XML.

I am new to XML and need to deliver an XSD file for a client who will be providing us with an XML file. Essentially I want to map an XML document to a relational database but we have been currently getting the data in Excel files from clients which then gets injected into an Access database...after extensive error checking.

I used excel to build a map around the columns of data that we need. I then had excel create an XSD file. I have altered the XSD file to include restrictions for the elements (structured as the column headings in excel). Please find the file attached. Will this work or is there a better way to structure the XSD? I also want to add - xsd:pattern value="[0-9]{6} - to the  Royalty_Reporting_Period but not sure how.

Thanks.

Sales-Data-Delivery-Schema01.txt
Sales-Data-Delivery-Workbook.xls
jahwalkAsked:
Who is Participating?
 
Geert BormansConnect With a Mentor Information ArchitectCommented:
I think I also removed the form=unqualified. That is for when you use namespaces and yyou don't, so remove it

No, no, off course you can allow a minOccurs="0" for cases where the client does not have the information.
Tell him to leave the element out, rather than creating the element and leaving the content blank.
You have done the right thing
The nillable=true is something entirely different. Remove that please. It is there to allow a client to explicitely set the field to nill,
which makes sense from the DB point of view, but you only want to give the client one option for nills, not multiple
So choose between nillable=true or minOccurs=0, I would go for minOccurs=0
please remove all references to nillable=true, definitely there where you have a minOccusr=1, since they contradict
0
 
Geert BormansInformation ArchitectCommented:
Your schema is fine, nothing really fancy about it, I hope you thought well about the minOccurs to have some elements optional
You likely need to tell your filter how to handle missing elements, since there is no such thing as an optional field in a relational database.
I removed the nillable="true" from row and Root element, you don't want that.
I strongly doubt you want nilllable on the field elements, that is a clumsy way of making something nillable. You would better remove all the nillable="true" and just make sure that your customer knows that nill fields should be left out.
You need to make a decission here, to make your life easier. Your client either leaves the element out (and then you keep the minOccurs="0") or you make them all minOccurs="1" (default so leave the attribute out then) bt remove the nillable then

I added the pattern for you
<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema">
	<xsd:element name="Root">
		<xsd:complexType>
			<xsd:sequence minOccurs="0">
				<xsd:element minOccurs="0" maxOccurs="unbounded" name="Row">
					<xsd:complexType>
						<xsd:sequence minOccurs="0">
							<xsd:element minOccurs="0" nillable="true" type="xsd:normalizedString" name="StoreName" form="unqualified"/>
							<xsd:element minOccurs="1" nillable="true" type="xsd:normalizedString" name="Country" form="unqualified"/>
							<xsd:element minOccurs="0" nillable="true" name="Royalty_Reporting_Period" form="unqualified">
								<xsd:simpleType>
									<xsd:restriction base="xsd:string">
										<xsd:pattern value="[0-9]{6}"></xsd:pattern>
									</xsd:restriction>
								</xsd:simpleType>
							</xsd:element>
							<xsd:element minOccurs="1" nillable="true" type="xsd:normalizedString" name="Track_Title" form="unqualified"/>
							<xsd:element minOccurs="0" nillable="true" type="xsd:normalizedString" name="Label" form="unqualified"/>
							<xsd:element minOccurs="0" nillable="true" type="xsd:normalizedString" name="Track_Artist" form="unqualified"/>
							<xsd:element minOccurs="0" nillable="true" type="xsd:normalizedString" name="Album" form="unqualified"/>
							<xsd:element minOccurs="1" nillable="true" type="xsd:positiveInteger" name="CDNumber" form="unqualified"/>
							<xsd:element minOccurs="1" nillable="true" type="xsd:positiveInteger" name="TrackNumber" form="unqualified"/>
							<xsd:element minOccurs="1" nillable="true" type="xsd:positiveInteger" name="UPC" form="unqualified"/>
							<xsd:element minOccurs="0" nillable="true" type="xsd:string" name="ISRC" form="unqualified"/>
							<xsd:element minOccurs="1" nillable="true" type="xsd:string" name="DeliveryType" form="unqualified"/>
							<xsd:element minOccurs="0" nillable="true" type="xsd:string" name="ClientUniqueTrackID" form="unqualified"/>
							<xsd:element minOccurs="1" nillable="true" type="xsd:positiveInteger" name="NumberOfDownloads__Singles" form="unqualified"/>
							<xsd:element minOccurs="1" nillable="true" type="xsd:positiveInteger" name="NumberOfDownloads__Albums" form="unqualified"/>
						</xsd:sequence>
					</xsd:complexType>
				</xsd:element>
			</xsd:sequence>
		</xsd:complexType>
	</xsd:element>
</xsd:schema>

Open in new window

0
 
Geert BormansInformation ArchitectCommented:
and here is what I would do
<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema">
	<xsd:element name="Root">
		<xsd:complexType>
			<xsd:sequence minOccurs="0">
				<xsd:element minOccurs="0" maxOccurs="unbounded" name="Row">
					<xsd:complexType>
						<xsd:sequence minOccurs="0">
							<xsd:element minOccurs="0" type="xsd:normalizedString" name="StoreName" form="unqualified"/>
							<xsd:element minOccurs="1" type="xsd:normalizedString" name="Country" form="unqualified"/>
							<xsd:element minOccurs="0" name="Royalty_Reporting_Period" form="unqualified">
								<xsd:simpleType>
									<xsd:restriction base="xsd:string">
										<xsd:pattern value="[0-9]{6}"></xsd:pattern>
									</xsd:restriction>
								</xsd:simpleType>
							</xsd:element>
							<xsd:element minOccurs="1" type="xsd:normalizedString" name="Track_Title" form="unqualified"/>
							<xsd:element minOccurs="0" type="xsd:normalizedString" name="Label" form="unqualified"/>
							<xsd:element minOccurs="0" type="xsd:normalizedString" name="Track_Artist" form="unqualified"/>
							<xsd:element minOccurs="0" type="xsd:normalizedString" name="Album" form="unqualified"/>
							<xsd:element minOccurs="1" type="xsd:positiveInteger" name="CDNumber" form="unqualified"/>
							<xsd:element minOccurs="1" type="xsd:positiveInteger" name="TrackNumber" form="unqualified"/>
							<xsd:element minOccurs="1" type="xsd:positiveInteger" name="UPC" form="unqualified"/>
							<xsd:element minOccurs="0" type="xsd:string" name="ISRC" form="unqualified"/>
							<xsd:element minOccurs="1" type="xsd:string" name="DeliveryType" form="unqualified"/>
							<xsd:element minOccurs="0" type="xsd:string" name="ClientUniqueTrackID" form="unqualified"/>
							<xsd:element minOccurs="1" type="xsd:positiveInteger" name="NumberOfDownloads__Singles" form="unqualified"/>
							<xsd:element minOccurs="1" type="xsd:positiveInteger" name="NumberOfDownloads__Albums" form="unqualified"/>
						</xsd:sequence>
					</xsd:complexType>
				</xsd:element>
			</xsd:sequence>
		</xsd:complexType>
	</xsd:element>
</xsd:schema>

Open in new window

0
Cloud Class® Course: C++ 11 Fundamentals

This course will introduce you to C++ 11 and teach you about syntax fundamentals.

 
Geert BormansInformation ArchitectCommented:
I would assume that if you have a row, that it needs fields
so make this change
<xsd:element minOccurs="0" maxOccurs="unbounded" name="Row">
                              <xsd:complexType>
                                    <xsd:sequence minOccurs="0">
into
<xsd:element minOccurs="0" maxOccurs="unbounded" name="Row">
                              <xsd:complexType>
                                    <xsd:sequence >
0
 
jahwalkAuthor Commented:
Thanks Gertone! I really appreciate your help. I have a few other questions that I am hoping you can help me with.

What is form="unqualified" doing? Should I set this to be something?

You state that: 'Your client either leaves the element out (and then you keep the minOccurs="0") or you make them all minOccurs="1" (default so leave the attribute out then) bt remove the nillable then'

Sometimes clients don't have the data for certain elements that are not that important. I don't want to make it where the client cannot create the xml file for data that isn't crucial. On the other hand, some data is absolutely necessary and I would rather not have to go back to the client after getting a data file that is missing the necessary data. The necessary data is treated as xsd:element minOccurs="1". Is having different settings for elements not a good solution? Thanks again for your help. It is stress relieving.


0
 
jahwalkAuthor Commented:
Thanks Gertone. You gave me more than just a solution. I really appreciate all your advice and help. Outstanding!
0
 
Geert BormansInformation ArchitectCommented:
welcome
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.