xmltocursor() failing

[EzLogic]

lcFile = "somefile.xml"
xmltocursor(lcFile,"_MyCursor",512)

I get error:
xml parse error: Field name is duplicate or invalid. node "message" xml="<message>
<message-header>
<message-id>28423</message-id>

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE message SYSTEM "[URL unfurl="true"]http://teknopcs:7800/best/dtd/SaleOrder.dtd">[/URL]
<message>
	<message-header>
		<message-id>28423</message-id>
		<transaction-name>sales-order-submission</transaction-name>
		<partner-name>Apple</partner-name>
		<partner-password>our password as assigned by you</partner-password>
		<source-url>[URL unfurl="true"]http://teknopcs/SprintStore/ws/logisticsgateway</source-url>[/URL]
		<create-timestamp>20090126074512</create-timestamp>
		<response-request>1</response-request>
	</message-header>
	<sales-order-submission>
		<header>
		<customer-id>283705</customer-id>
		<business-name>ABC Company</business-name>
		<carrier-name>Sprint</carrier-name>
		<customer-information>
			<customer-first-name>John</customer-first-name>
			<customer-last-name>Doe</customer-last-name>
			<customer-middle-initial>A</customer-middle-initial>
			<customer-address1>93 Elm Street</customer-address1>
			<customer-address2></customer-address2>
			<customer-address3></customer-address3>
			<customer-city>Novi</customer-city>
			<customer-state>MI</customer-state>
			<customer-post-code>48375</customer-post-code>
			<customer-country-code>US</customer-country-code>
			<customer-phone1>2485551212</customer-phone1>
			<customer-phone2></customer-phone2>
			<customer-fax></customer-fax>
			<customer-email>somecustomer@hotmail.com</customer-email>
		</customer-information>
		<shipment-information>
			<ship-first-name>John</ship-first-name>
			<ship-last-name>Doe</ship-last-name>
			<ship-middle-initial>A</ship-middle-initial>
			<ship-address1>93 Elm Street</ship-address1>
			<ship-address2></ship-address2>
			<ship-address3></ship-address3>
			<ship-city>Novi</ship-city>
			<ship-state>MI</ship-state>
			<ship-post-code>48375</ship-post-code>
			<ship-country-code>US</ship-country-code>
			<ship-phone1>2485551212</ship-phone1>
			<ship-phone2></ship-phone2>
			<ship-fax></ship-fax>
			<ship-email>somecustomer@hotmail.com</ship-email>
			<ship-via>G2DAYP</ship-via>
			<ship-request-date></ship-request-date>
			<ship-request-from></ship-request-from>
			<ship-request-warehouse>KJT1</ship-request-warehouse>
		</shipment-information>
		<purchase-order-information>
			<purchase-order-number>28603</purchase-order-number>
			<account-description></account-description>
			<purchase-order-amount>110.94</purchase-order-amount>
			<currency-code>USD</currency-code>
			<comments></comments>
		</purchase-order-information>
		<order-header>
			<customer-order-number>28603</customer-order-number>
			<customer-order-date>20090125</customer-order-date>
			<order-sub-total>99.99</order-sub-total>
			<order-discount>0</order-discount>
			<order-tax1>0</order-tax1>
			<order-tax2></order-tax2>
			<order-tax3></order-tax3>
			<order-shipment-charge>10.95</order-shipment-charge>
			<order-total-net>110.94</order-total-net>
			<order-status>SUBMITTED</order-status>
			<order-type>WEB SALES</order-type>
			<gift-flag>0</gift-flag>
		</order-header>
		</header>
		<detail>
		<line-item>
			<line-no>30208</line-no>
			<item-code>KAJKATANA2RETAILPNK</item-code>
			<universal-product-code></universal-product-code>
			<product-name>Sanyo Katana II Pink - Sanyo Katana II Pink DTC KIt</product-name>
			<comments></comments>
			<quantity>1</quantity>
			<unit-of-measure>EACH</unit-of-measure>
			<sid></sid>
			<esn></esn>
			<min></min>
			<mdn></mdn>
			<irdb></irdb>
			<imei></imei>
			<market-id></market-id>
			<line-status>IN STOCK</line-status>
			<base-price>99.99</base-price>
			<line-discount>0</line-discount>
			<line-tax1>0</line-tax1>
			<line-tax2></line-tax2>
			<line-tax3></line-tax3>
		</line-item>
		</detail>
	</sales-order-submission>
</message>

Ali Koumaiha
TeknoPCS Inc.
Dearborn heights, MI 48127

 

[TamarGranor]

I think XMLToCursor() works only with XML data inside a <VFPTable> tag. Try using the XMLAdapter class instead; it's much more flexible.

Tamar

 

[Olaf Doschke]

Tamar,

the root node doesn't matter to XMLToCursor. But that XML is not a flat table, it's at least two tables.

The problem is, foxpro doesn't allow minus in names:

Create cursor curTest (message-id I)

Minus is for expression, not for names. Or how would you ever be able to know if a-b means a minus b or a field or variable named "a-b". It's impossible.

Bad design of that XML, I wonder if XMLAdatper does any better with this.

Bye, Olaf.

 

[dbMark]

XMLtoCursor and CursortoXML are generally only suitable for simple, single level XML. When multi-level XML is read, rather than it creating one cursor with one record and many fields (limited to 254 fields of course), you get multiple records. Every time the XML changes levels, a new record is created, giving a stair-step effect. Very messy to decode programatically. Even looking at it visually is awkward and cumbersome.

A better solution is to read XML files with the XmlAdapter object which comes with VFP version 9 and perhaps earlier versions too. Here is a sample of code to read the XML string, hopefully without too many errors.

If it is an XML containing nested tags, then you would have to extract those sections out and handle them each separately. Here is a simple example that reads an XML:

oXml = CREATEOBJECT("XmlAdapter")
oXml.loadXml(IncomingXML)
iXml = oXml.IXMLDOMElement
IF oXml.isLoaded
   * simple tags, not nested or duplicated
   cDate = VAL(iXml.getElementsByTagName("EventDate").item(0).text)
   cInfo = VAL(iXml.getElementsByTagName("EventDescription").item(0).text)
   * now get nested or duplicated tags
   nXmlItemCnt = IIF(TYPE("iXml")="O",iXml.getElementsByTagName("EventDetail").LENGTH,0)
   FOR nCnt = 1 TO nXmlItemCnt
      iXmlGrpItem = ixml.getElementsByTagName("EventDetail").ITEM(nCnt-1)
      aItem1[nCnt] = IIF(iXmlGrpItem.getElementsByTagName("Data1").LENGTH<>0,iXmlGrpItem.getElementsByTagName("Data1").ITEM(0).TEXT,"")
      aItem2[nCnt] = IIF(iXmlGrupItem.getElementsByTagName("Data2").LENGTH<>0,iXmlGrpItem.getElementsByTagName("Data2").ITEM(0).TEXT,"")
   ENDFOR
ENDIF