asked on

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

Gertone (Geert Bormans)

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

Gertone (Geert Bormans)

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

Gertone (Geert Bormans)

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 >

jahwalk

ASKER

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.

ASKER CERTIFIED SOLUTION

Gertone (Geert Bormans)

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

jahwalk

ASKER

Thanks Gertone. You gave me more than just a solution. I really appreciate all your advice and help. Outstanding!

Gertone (Geert Bormans)

welcome