Visual FoxPro 8.0 introduces a whole new way to work with eXtensible Markup Language (XML). The XMLAdapter class works with hierarchical XML, provides an object-oriented approach to working with XML data, and leverages your familiarity with tables and fields in the way it exposes the XML contents.
by Cathi Gero
May 20, 2003
Page 3 of 4
XMLAdapter Class with SQLXML
The XMLAdapter class supports SQL Server 2000 queries that output to XML using the SQLXML SDK (see sidebar, "The SQLXML SDK"). The following is an example of a T-SQL query that generates XML for the result set:
Inner Join Orders
On Customers.CustomerID = Orders.CustomerID
Inner Join [Order Details] Order_details
On Orders.OrderID = Order_details.OrderID
WHERE Customers.CustomerID = 'CACTU' AND
Orders.OrderID = 10521
FOR XML AUTO, ELEMENTS, XMLDATA
The XML generated pulls data from three different tables and the XML data maintains the relationships between these tables. This allows an XMLAdapter object to maintain the parent-child relationships by populating the XMLTable.ParentTable and XMLTable.ChildTable properties. The following Visual FoxPro code uses an XML file to display the relationships between the different XMLTable objects:
When the ToCursor() method is called, only one cursor (called Customers) will be generated, representing the join of all three tables. One note to keep in mind: The XML created above does not generate a schema which sets the maxlength for string fields. Therefore, Visual FoxPro will convert the data type to memos, since that is the default data type for unlimited string lengths. To work around this, you will need to modify the schema before calling the ToCursor() method. Here is the code:
A use for SQLXML is to publish SQL Server data for Intranet and Internet-based applications using the HTTP publishing functionality. The ability to publish data over HTTP allows you to build highly data-centric Web sites. In addition, the XMLAdapter class can retrieve data as XML by simply making an HTTP request. The request can be a select statement or a call to a XML template (see sidebar, "XML Templates").
Working with Windows.NET Framework
In Microsoft .NET Framework classes, the primary class used for manipulation of data is the Windows .NET DataSet. The DataSet is a class that encapsulates data as XML. XML may be returned from DataSets in several ways:
Return the entire Windows .NET DataSet to the calling application, which returns all rows in DiffGram format with inline schema, having Updates, Inserts, and Deletes indicated.
Return Windows .NET DataSet changes only which returns only the rows that have been modified, added, or deleted in Diffgram format with inline schema.
Windows .NET DataSet class supports the DataSet.GetXml and GetXmlSchema methods which return XML to a .NET string type.
When working with Windows .NET DataSets, you must set the property MissingSchemaAction of the DataAdapter class prior to filling the DataSet
Windows .NET DataSet class supports the DataSet.WriteXml and WriteXmlSchema methods which write the DataSet as XML with Inline Schema, without Schema, or with Schema separately.
A great amount of focus went into making the XMLAdapter class compatible with Windows .NET DataSets. The XMLAdapter class supports hierarchical XML format, which improves Visual FoxPro's ability to interoperate with XML produced from and written to Windows .NET DataSets. Separate Visual FoxPro cursors will be created for each DataTable contained in the DataSet.
Listing 1shows sample Visual Basic .NET code that retrieves data from three SQL Server tables into one Windows .NET DataSet, then exports the DataSet as XML to an XML file and the schema to a separate XSD file. One note worth mentioning when working with Windows .NET DataSets: You must set the property MissingSchemaAction of the DataAdapter class prior to filling the DataSet; like the following:
This is required to include all length information in the schema. Otherwise, all the .NET Framework string fields will be modified to Memo fields in Visual FoxPro. This is because the memo field is the default type in Visual FoxPro for unlimited length strings.
The XMLAdapter class can consume this XML by reading in the data and generating three different cursors. Here is the code:
adapter = CREATEOBJECT("XMLAdapter")
cFile = "c:\XMLAdapter\DotNET.xml"
cXSDFile = "c:\XMLAdapter\DotNET.xsd"
adapter.XMLSchemaLocation = cXSDFile
FOR EACH oXMLTable IN adapter.Tables
Three different cursors will be created, as shown in Figure 2. The schema information for this example was created as an external file using Visual Basic .NET. When you have an external file, you need to set the XMLSchemaLocation property of the XMLAdapter object to the XSD file prior to reading in the XML.