[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

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

Posted on 2009-02-12
7
Medium Priority
?
540 Views
Last Modified: 2012-05-06
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
0
Comment
Question by:jahwalk
  • 5
  • 2
7 Comments
 
LVL 60

Expert Comment

by:Geert Bormans
ID: 23626116
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
 
LVL 60

Expert Comment

by:Geert Bormans
ID: 23626131
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
 
LVL 60

Expert Comment

by:Geert Bormans
ID: 23626144
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
Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

 

Author Comment

by:jahwalk
ID: 23626688
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
 
LVL 60

Accepted Solution

by:
Geert Bormans earned 2000 total points
ID: 23626759
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
 

Author Closing Comment

by:jahwalk
ID: 31546223
Thanks Gertone. You gave me more than just a solution. I really appreciate all your advice and help. Outstanding!
0
 
LVL 60

Expert Comment

by:Geert Bormans
ID: 23627672
welcome
0

Featured Post

Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

Question has a verified solution.

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

This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
Explore the ways to Unlock VBA Project Password Excel 2010 & 2013 documents. Go through the article and perform the steps carefully to remove VBA Excel .xls file.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

834 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