HOME | SIGN UP | ACCOUNT | SUBSCRIBE | TROUBLESHOOTING | LOGIN
News · Meetings · Jobs · Downloads · Universal Thread articles · Web articles · Conference coverages · Consultants · Blogs · FAQ · User groups · Photos · Videos

Introduction to XML with VFP and SQL Server 2000

Jorge A. Espinosa, Droguería Saporiti SACIFIA
Jorge Espinosa (Brandsen, Buenos Aires, Argentina) is a Programmer Analyst, and a Microsoft MCP. Dedicated to the development of desktop systems since the year 1987; always in the xBase environment, and in Visual FoxPro since version 3.0. He is also making inroads into the .NET world, with the C# language. He currently works as Systems Manager in Droguería Saporiti SACIFIA. He is an outstanding speaker at technical conferences in Argentina.

In my previous article (Introduction to XML), we saw one of the ways to use XML from VFP.

This time, we'll get deeper in this topic, and we'll see how to work with Microsoft SQL Server 2000, leveraging its XML features, and all of this, using Visual FoxPro as the client.

We already had an overview to XML, which is a specification created under the purpose of making heterogeneous platforms to "talk" the same language.

XML provides a pure text file format, self-descriptive, that allows it to be recognized by every existing platform, and that it what enabled it to become a true standard, compatible with every computing system.

XML and SQL Server 2000

SQL Server 2000 is the last RDBMS from Microsoft, designed to provide Back End data services for the Internet, and being XML today's standard to exchange data across the Web, SQL Server 2000 has become an amazing tool to provide this kind of services, specifically by means of its XML support.

With the addition of the FOR XML clause to the SELECT statement, and the OPENXML function, SQL Server 2000 has greatly enhanced the management scope that we had until now.

SQL Server 2000 provides us with the ability to obtain the results from a SELECT statement in XML format with the only addition of the FOR XML clause, as well as the reception, processing and data update or data insertion trough the use of the OPENXML function.

Figure 1: The main form

From Visual FoxPro we could take advantage of these features that SQL Server 2000 offers by accessing it trough the improved OLE DB provider and the ADO database library with extended support for XML, which allows to send queries and receive results in XML format trough the new Stream object.

Taking our little application -developed in VFP on our overview to XML in the previous article, we'll make the needed changes to access SQL Server 2000, and more specifically, to access its sample database Northwind. We'll make a few minor modifications to the visual layout of our original form to make it look as in the previous figure.

As we can see in the figure, we had added two buttons with the purpose of Adding and Updating records on our database.

Internally, our initial code would overcome severe modifications that we'll review now. Let's see:

The main form should have the following properties:

Property Description
CMDAdo Used to store our ADO Command object.
oConn Used to store our Connection object.
miXML It would be used to store our XML in the queries and also the generated XML for Adds and Updates.
oDoc Will store our Document object.
oResult Geared to store our AdoDB Stream object.

MiCodigoProd and miDescriProd would be used to add and update records.

Nodo and TotalNodos would behave as in the first example.

It would be also a method called CargaNodo that would be identical to the one in the first example.

To be able to advance further in our example, we will create a tabled called PRODUCTXML inside the Northwind database comprising two fields: ProductID, int and ProductName, char(50).

To have it populated with data we'll import the content of the Products table from the same database. We will do this because the original table has referential integrity restrictions that we should respect related to the rest of the database tables.

We'll work with a FormSet with a main form identical to the one shown in Figure 1, and two other forms used to Add and Update records respectively.

Now let's go to the example code:

In our FormSet we'll have the following code:

Thisformset.oconn = CREATEOBJECT("adodb.connection")

Thisformset.oconn.Open("Provider=SQLOLEDB.1;Password=;Persist Security Info=True;" ;
 + "User ID=sa;Initial Catalog=NorthWind;Data Source=SERVER-JORGE")

SET EXACT ON

In this lines we are creating a Connection object and we are opening the database trough a ConnectionString, for which we should have to fill in the right user and password in the corresponding properties, as well as the proper server name in the Data source property.

Cargar (load) Button would have the following code:

Thisformset.cmdAdo = CREATEOBJECT("adodb.command")
Thisformset.cmdAdo.ActiveConnection = Thisformset.oconn
Thisformset.cmdAdo.CommandType = 1    && adcmdStoredProc
Thisformset.cmdAdo.CommandText = "XML_ListaProductos"
Thisformset.oResult = CREATEOBJECT("adodb.stream")
Thisformset.oResult.Open

Thisformset.cmdAdo.Properties("Output Stream") = Thisformset.oResult
Thisformset.cmdAdo.Execute(, ,1024)

Thisformset.miXML = Thisformset.oResult.readtext(-1)

Thisformset.mixml = ""+Thisformset.mixml
Thisformset.mixml = Thisformset.mixml+""

Thisformset.odoc = createobject('msxml.domdocument')

IF Thisformset.odoc.loadXML(Thisformset.miXML) && asynchronic; see Async Property
	
	DO WHILE Thisformset.odoc.readyState <> 4 && 4 Completed
	
		WAIT WINDOW 'Loading document...' TIMEOUT 1
	
	ENDDO

	Thisformset.totalnodos = Thisformset.odoc.documentElement.childNodes.length -1
	Thisform.editBox1.Value = Thisformset.odoc.xml
	Thisformset.carganodo(Thisformset.odoc.childNodes, "ProductXML")
	Thisform.cmdTop.Click
	Thisformset.Refresh
	

ELSE
	oE = Thisformset.odoc.parseError

	lcMsg = 'Error Code: ' + TRANSFORM(oE.errorCode) + CHR(13) + ;
			'File Position: ' + TRANSFORM(oE.filepos) + CHR(13) + ;
			'Line: ' + TRANSFORM(oE.line) + CHR(13) + ;
			'Line Position: ' + TRANSFORM(oe.linepos) + CHR(13) + ;
			'Reason: ' + oE.reason + CHR(13) + ;
			'Source text: ' + oE.srcText + CHR(13) + ;
			'URL: ' + oe.url

	MESSAGEBOX(lcMsg,0+48,'Error loading the document')

ENDIF

This button will create an AdoDB Command object, which would execute a Stored Procedure over the Northwind database called XML_ListaProductos, which in turn would return a list of products en XML format with Code and Description from our ProductXML table.

The Stored Procedure code is the following:

Select ProductID, ProductName
from ProductXML
For XML Auto

As we could see, this code has nothing extraordinary. The only thing that we have to do to get our results in XML format is to add the FOX XML AUTO clause at the end of our SELECT.

The FOR XML clause

The FOR XML clause, added to the SELECT statement, allows us to obtain the result of a query in XML format. This result can be obtained and viewed in SQL Query Analyzer as well as in an ADO Stream object. The later is the way in which we will work in our application.

The FOR XML clause has a few alternatives that we would briefly reference next.

The distinct options of the FOR XML clause are three and have a great difference in the mode in which data are returned:

FOR XML AUTO

This option returns data organized in a hierarchical structure, and in most cases is the more adequate way to work with them, as they can be expanded and examined at the several different levels, going deeper into the detail, as in a typical header-detail structure on several customers' invoices.

FOR XML RAW

It returns an element-based document as the AUTO option, but in this ones, each node gets the ROW name, independently of the table name from which the data got extracted. This option is useful to be used when we have to pull the data from more than a single table and it is not a hierarchical structure as the mentioned in the example above.

FOR XML EXPLICIT

It generates a document in the format of an universal hierarchic table. This mode requires that the column names for this table got explicitly specified. More information about the use of this alternative for the FOR XML clause can be obtained in the SQL Server 2000 Online Books.

And now, going back to our application, in our main form the navigational buttons and the ListBox to show the contents of our database would be preserved. Navigating the product list, we could locate one of them and switch to update mode, or click in the Nuevo (add) button to enter a new product.

To be able to do this, we should add two forms with something like what's seen above and bellow.

The one we have first is the Alta (new) form, to create a new product. This form contains two simple TextBoxes to enter the code and description of the product being added to the table. Those TextBoxes would be bounded to the properties that we added to our FormSet, miCodigoProd y miDescriProd. In this form, both objects would be enabled for writing, providing the ability to fill in the entered data.

The product would be added to our database trough the Click event on our Aceptar (Ok) button, from which we'll see the code:

Thisformset.cmdAdo = CREATEOBJECT("adodb.command")
Thisformset.cmdAdo.ActiveConnection = Thisformset.oconn
Thisformset.cmdAdo.CommandType = 4    && adcmdStoredProc
Thisformset.cmdAdo.CommandText = "XML_PruebaAlta"

Thisformset.mixml = ""
Thisformset.cmdado.parameters("@ProductXML").value = Thisformset.mixml

Thisformset.cmdado.execute

Thisformset.foxXML.list1.Clear
Thisformset.foxXML.editBox1.Value=''
Thisformset.foxXML.cmdCargar.Click

Thisform.Enabled = .F.
Thisform.visible = .F.

Thisformset.foxXML.Enabled = .t.

Now let's analyze the distinct code lines:

In first place, what we are doing is to create an AdoDB Command object to execute our Stored Procedure, called XML_PruebaAlta. This Store Procedure performs the record add into the ProductXML table with the two fields entered in the form, but for that to happen, we need to create our XML document, and so in the following lines we observe how, adding the corresponding tags we format our data-entered information in the proper way.

The above mentioned Store Procedure would receive a single parameter that's our XML document, which would be responsible for processing and extracting the needed data to perform the mentioned insertion.

Once all this is done, the following commands refreshes the form and deactivate the product Add form.

Now let's see what happens at the SQL Server side.

As we mentioned above, there is a Stored Procedure called XML_PruebaAlta inside our database, tat receives a parameter called "@ProductXML" and process it to extract from this document the needed data to perform the record insertion.

Lets see that Stored Procedure code:

CREATE PROCEDURE [XML_PruebaAlta]
@ProductXML varchar(4000) 

AS

DECLARE @hDoc int
EXEC sp_xml_preparedocument @hDoc OUTPUT, @ProductXML
INSERT ProductXML (ProductID, ProductName)
SELECT ProductID = miXML.ProductID,
	ProductName = miXML.ProductName

        FROM OPENXML (@hDoc, '/Products') 
        WITH (ProductID int,
		ProductName char(50) 
		) as miXML


EXEC sp_xml_removedocument @hDoc

Well... as we can see, this is a bit more complex than the first analyzed Stored Procedure. But anyway, let's look at the code and analyze it little by little:

We define that it will be called XML_PruebaAlta, and that it will receive a parameter called @ProductXML, of VarChar data type with a 4000 character extension (just in case), as we have to remember that XML is just a text, right?

Then we declare an internal variable called hDoc of integer type. In this variable, SQL would store the document assigned handle at the processing moment, and will generate in memory a hierarchical structure in a tree shape representing the document trough the extended Stored Procedure sp_xml_preparedocument.

Next, we have a simple part that's the insertion. Here we tell in what table we will insert and what fields.

Later, instead of indicating which are the values to insert, we indicate that it is the result of a SELECT. This SELECT is executed over the previously generated XML structure in memory, and, as we can observe in the FROM clause we indicate that it is performed over an OPENXML that receives as parameter the initial handle that we get, and we indicate the document root. It is important to highlight that XML is case sensitive, and so we have to be very careful when we create our Stored Procedure and when we generate the document that would arrive as a parameter, as it is mandatory that the root and the fields perfectly match.

Finally, with the WITH sentence, we indicate the structure that our SELECT would return, as if we don't do this, the result would contain, together with the fields we're interested in, several metadata columns the we don't want to receive.

To close the Stored Procedure, we release from memory the structure generated, using sp_xml_removedocument, passing as a parameter the initially stored handle.

Now, after creating the corresponding Stored Procedure, we can execute our application and test, clicking the New button, to enter a new product. If everything went right, we can continue by creating the last form that will allow us to update an existing product information.

We can look at the figure to see how that form should appear.

As you can see, this time I'm not making you work too much, as the mentioned form is a copy of the previous one, having as its only difference the textbox corresponding to the product code in a disables state.

As for the code, let's take a look at the Aceptar button.

Thisformset.cmdAdo = CREATEOBJECT("adodb.command")
Thisformset.cmdAdo.ActiveConnection = Thisformset.oconn
Thisformset.cmdAdo.CommandType = 4    && adcmdStoredProc
Thisformset.cmdAdo.CommandText = "XML_PruebaModi"

Thisformset.mixml = ""
Thisformset.cmdado.parameters("@ProductXML").value = Thisformset.mixml

Thisformset.cmdado.execute

Thisformset.foxXML.list1.Clear
Thisformset.foxXML.editBox1.Value=''
Thisformset.foxXML.cmdCargar.Click

Thisform.Enabled = .F.
Thisform.visible = .F.

Thisformset.foxXML.Enabled = .t.

Ok... very little to analyze here. It is very similar to the Aceptar button on our New product form. Indeed, except for the name of the Stored Procedure to execute that has changed, the rest is identical.

Then, let's take a look at the code in our XML_PruebaModi Stored Procedure:

CREATE PROCEDURE [XML_PruebaModi]
@ProductXML varchar(4000) 

AS

DECLARE @hDoc int
EXEC sp_xml_preparedocument @hDoc OUTPUT, @ProductXML

UPDATE ProductXML 
SET ProductName =miXML.ProductName

        FROM OPENXML (@hDoc, '/Products') 
        WITH (ProductID int,
		ProductName char(50) 
		) as miXML

Where ProductXML.ProductID = miXML.ProductID

EXEC sp_xml_removedocument @hDoc

If you understood the previous one, this is very simple and we don't need to analyze it line by line. It is enough to highlight as a difference that we are performing an UPDATE, saving the ProductName field, and adding the WHERE clause to indicate that it have to be performed over the product with the ProductID field matching with the one passed as a parameter.

We can create the Stored Procedure with the code above and test how the system works now.

To finish with our example in a tidy way, we can add the following code to the Cancelar (cancel) and Salir (exit) buttons on the added forms.

For the Cancelar (cancel) button:

Thisform.Enabled = .F.
Thisform.visible = .F.
Thisformset.foxXML.Enabled = .t.

And for the Salir (exit) button:

Thisformset.Release

I sincerely hope that this article had sparkled your interest. See you next time.

Download the source code for this article

More articles from this author

NoTitleDate
1.Access the Outlook contacts list and send MAPI emailsDecember 2002
2.An introduction to XMLSeptember 2002
3.Components and tiers in Visual FoxPro 8.0April 2004
4.Multi-Tier application development with VFP and VBMay 2002

Copyright © 1993 - 2014 Level Extreme Inc., All Rights Reserved · Telephone: 506-783-9007 Email: info@universalthread.com · Privacy & Security · Copyright · Terms & Conditions