John,

>Customer master table contains customer#. The other info such as addresses, order history are stored as nested table. How could I import them using VFP?

You can do what you want with XMLAdapter (VFP9 only) by configuring its internal metadata to properly drill into the XML to pull out the fields you need. Even with an XSD schema, the layout of your XML document is too complex to be handled by XMLAdpater without giving it some additional hints.

The trickiest part of your XML (if you intend to extract the info into separate tables, such as customers and orders), is how to relate each record in orderhistory to the correct customer_master record. Customer_master has the custid field, but orderhistory does not -- it is implied by the nesting, but you will have to configure an XMLField object that uses an XPath expression to point to its related customer_master custid.

I was going to point you to another similar message I posted a while back, but when I started tinkering with it to get you the correct XPath for the order foreign key, I ended up just modifying my original sample to work with your XML. Here it is -- enjoy!

* jjones.prg -- read in jjones.xml and
* output 2 cursors via XMLAdapter

* SAVE this XML into jjones.xml:
*!*	<IDOC>
*!*	  <CUSTOMER_MASTER>
*!*	    <CUSTID>0001</CUSTID>
*!*	    <ADDRESSES>
*!*	      <ADDR1>No 12, Off Road</ADDR1>
*!*	      <POSTCODE>123364</POSTCODE>
*!*	    </ADDRESSES>
*!*	    <ORDERHISTORY>
*!*	      <ORDERID>000001</ORDERID>
*!*	      <AMOUNT>10</AMOUNT>
*!*	    </ORDERHISTORY>
*!*	  </CUSTOMER_MASTER>
*!*	  <CUSTOMER_MASTER>
*!*	    <CUSTID>0002</CUSTID>
*!*	    <ADDRESSES>
*!*	      <ADDR1>No 14, Off WAY</ADDR1>
*!*	      <POSTCODE>898986</POSTCODE>
*!*	    </ADDRESSES>
*!*	    <ORDERHISTORY>
*!*	      <ORDERID>000002</ORDERID>
*!*	      <AMOUNT>100</AMOUNT>
*!*	    </ORDERHISTORY>
*!*	    <ORDERHISTORY>
*!*	      <ORDERID>000003</ORDERID>
*!*	      <AMOUNT>30</AMOUNT>
*!*	    </ORDERHISTORY>
*!*	</CUSTOMER_MASTER>
*!*	</IDOC>

* clean environment
CLOSE DATABASES ALL

* enable Intellisense
LOCAL oxa AS xmladapter
LOCAL oTable as XMLTable
LOCAL ofield as XMLField

oxa=CREATEOBJECT("xmladapter")

* tell XMLAdapter we don't have a schema so it will
* go ahead and load the XML anyway
oxa.XMLSchemaLocation=""

* load the XML
oxa.LoadXML("jjones.xml",.T.)

* now set up your own internal schema to tell
* XMLAdapter where to find the tables and fields
*
* NOTE: Tag names are case-sensitive
*
* NOTE: All XPath statements are relative to the
*   next-higher hierarchical level. The starting 
*   point for tables is the "root" tag, which in this case
*   is <IDOC>

* customer_master table
oTable = CREATEOBJECT("XMLTable")
* all XMLName properties must be Unicode, so use STRCONV
oTable.XMLName = STRCONV("CUSTOMER_MASTER",5)
oTable.XMLNameIsXpath = .T.
* the cursor's alias (can be different from the XML tag)
oTable.Alias = "customers"

oField = CREATEOBJECT("XMLField")
oField.XMLName = STRCONV("CUSTID",5)
oField.XMLNameIsXpath = .T.
* field.alias means the field name you want
* in your cursor
oField.Alias = "id"
oField.DataType = "C"
oField.MaxLength = 4
oTable.Fields.Add(oField, oField.XMLName)

oField = CREATEOBJECT("XMLField")
* drill in past the ADDRESSES tag to get to the ADDR1 tag
oField.XMLName = STRCONV("ADDRESSES/ADDR1",5)
oField.XMLNameIsXpath = .T.
oField.Alias = "addr1"
* change data types if you like -- in this case,
* why not Varchar(50)?
oField.DataType = "V"
oField.MaxLength = 50
oTable.Fields.Add(oField, oField.XMLName)

oField = CREATEOBJECT("XMLField")
* drill in past the ADDRESSES tag to get to the POSTCODE tag
oField.XMLName = STRCONV("ADDRESSES/POSTCODE",5)
oField.XMLNameIsXpath = .T.
oField.Alias = "postcode"
oField.DataType = "C"
oField.MaxLength = 10
oTable.Fields.Add(oField, oField.XMLName)

* add the first table to the tables collection
oxa.Tables.Add(oTable, oTable.XMLName)

* ORDERHISTORY table
oTable = CREATEOBJECT("XMLTable")
oTable.XMLName = STRCONV("CUSTOMER_MASTER/ORDERHISTORY",5)
oTable.XMLNameIsXpath = .T.
oTable.Alias = "orders"

* Note that now that we have pointed this XMLTable to the ORDERHISTORY
* node, the fields are simple (relative to the table node)
oField = CREATEOBJECT("XMLField")
oField.XMLName = STRCONV("ORDERID",5)
oField.XMLNameIsXpath = .T.
oField.Alias = "id"
oField.DataType = "C"
oField.MaxLength = 6
oTable.Fields.Add(oField, oField.XMLName)

********** cool trick ********
* create a new field that is not in the XML to act as a
* foreign key in the orders table pointing to the customers record id
* NOTE: This is done with a relative XPath statement 
* (relative to the orders table, which is the context for evaluating the XMLField xpath)
oField = CREATEOBJECT("XMLField")
* go to the node that the ORDERHISTORY node is contained in (CUSTOMER_MASTER) 
* and point to its CUSTID node
oField.XMLName = STRCONV("../CUSTID",5)
* NOTE: The commented line below would also work, although it doesn't seem that it would.
* In this example, parent:: refers to the parent node of the orders table
* and XMLAdapter considers the parent of all table objects
* added to the adapter to be the "root" node of the XML 
* (I like the previous example better)
*oField.XMLName = STRCONV("parent::CUSTOMER_MASTER/CUSTID",5)
oField.XMLNameIsXpath = .T.
oField.Alias = "fk_cust"
oField.DataType = "C"
oField.MaxLength = 4
oTable.Fields.Add(oField, oField.XMLName)

oField = CREATEOBJECT("XMLField")
oField.XMLName = STRCONV("AMOUNT",5)
oField.XMLNameIsXpath = .T.
oField.Alias = "amount"
oField.DataType = "N"
oField.MaxLength = 10
oField.FractionDigits = 2
oTable.Fields.Add(oField, oField.XMLName)

oxa.Tables.Add(oTable, oTable.XMLName)

* now output and browse the 2 cursors
oxa.tables(1).toCursor()
BROWSE
oxa.tables(2).toCursor()
BROWSE