xmltocursor()--how to use? 1

[peteschulte]

Hello Fox Experts,

We'll be receiving client data (view it at the url) in xml to print billing statements. Deciding whether to use AXC.exe or VFP8, I encountered problems with VFP8 using xmltocursor().

It appears that VFP stopped reading the file. See

http://web.afts.com/peter/bpexport.xml

and the results in BPExport512.dbf.

With flag 512, most of the result fields are memo fields, which is not the case with AXC results. Here's the line of code.
XMLTOCURSOR("\\Gmc\d\Stmt\Monterey\TEST\UB_XML\testExport\VFP8\BPExport.xml","curBPExport",512)
How can we keep them in their original data type?

With the other flags, 4, 1024, 2048 and 8192 I got "XML parse error: Invalid at the top level of the document. Line 1, Position 1." (Sometimes I got "Alias not found" which I resolved by creating a dbf to send the results to.) Can you instruct me a little on which if any flag to use and how?

VFP_Code_Export_XML.txt This file at the same url provides much the description as I've given -- just a little more detail.

Here I have asked many questions, but it all boils down to:
I just want to get the xml data broken out into FoxPro tables like AXC does its tables, or even into one table.

BTW -- anyone care to recommend yet another solution? We need to make a decision and/or purchase tomorrow.

Thank you,
Peter

 

[Olaf Doschke]

xmltocursor will not convert any xml to a cursor. It requires the XML to have a certain structure, so it can determine a table structure and extract records.

The XML you receive there, contains multiple tables and single informations, it's hierarchical XML and you need to invest more in parsing this. The XMLAdapter class of vfp will be the better choice to convert this to some objects and cursors and single variables.

Bye, Olaf.

 

[Olaf Doschke]

Local loXMLRequest, lcXML, loXMLAdapter, loXMLTable
Close Tables All 
loXMLRequest = CreateObject("Msxml2.xmlhttp")
loXMLRequest.open("GET","[URL unfurl="true"]http://web.afts.com/peter/bpexport.xml",.F.)[/URL]
loXMLRequest.send(.null.)
lcXML = oXML.responseText
loXMLAdapter = CreateObject("XMLAdapter")
loXMLAdapter.LoadXML(lcXML,.F.,.T.)
For Each loXMLTable in loXMLAdapter.Tables
   loXMLTable.ToCursor()
EndFor
SET

It creates 14 cursors from that xml, no wonder why xmltocursor "stopped", it's meant for XML representing a single table only it's no universal xml to cursor converter.

The right tool for the right job...

Bye, Olaf.

 

[Olaf Doschke]

most of the result fields are memo fields

Thats because in the schema part of the xml most fields are describes as type="xs:string" with no length given VFP or XMLAdapter or XMLtoCursor must assume the strings can be any length, so they must use MEMO.

I don't know AXC.exe, is it part of the software package, which genereates this XML? Then it's likely having inside knowlegde about the specifics of the fields, these informations don't come from the XML itself. Perhaps it's also measuring the max length of all the values occurring in the xml, but that's an extra step which will need reading the XML two times before it could be converted.

What you can do is to create the needed cursors, eg Trans or Customer or Fees with appropriate field lengths and then ToCursor will not generate the cursors but append to them. The fiueld names must match and the field type must be appropriate of course, you can't append a text to a numeric field.

Bye, Olaf.

 

[peteschulte]

Thank you, Olaf! Your help is very much appreciated! I made a small contribution.

 

[peteschulte]

Hi Olaf and friends.

This is the first month when we will print from client's xml instead of csv data. The code you supplied is great for creating tables from XML. Since too many are memo fields, I identified which fields from which tables we actually need, then narrowed it down to a dozen or so Memo fields needing to be changed -- usually character data is fine.

From account table we need to convert data types for acct_no and main_addr_
OKAY changed main_addr_ to 30 Chars and account_no to Char 12
From customer table we need last-name, use_autopay, care_of, main_addr_ pl_addr_2, pl_city, pl_city, pl_state, pl_zip. –
OKAY changed all from M4 to character as follows: Char 4; Char 40; Char 30; Char 4; Char 10
From billsummary table we need, totalpayments. –
OKAY found one rec with 67 payments
From Trans Table we need tran_subtype, doc_desc, fee_desc. –
OKAY each was memo 4 and is now char 8
From billingsummary table we need closed_date --
OKAY this is already of type TimeDate
From notessummary we need description –
OKAY descrip is now Char with 253 bytes width

I have done what you said here

What you can do is to create the needed cursors, eg Trans or Customer or Fees with appropriate field lengths and then ToCursor will not generate the cursors but append to them.

See the link for a list of the empty and properly typed tables for the XMLAdapter object to append to.

Now I'm wondering how to modify the following code so that it will write to the existing tables in their folder.

SET DEFAULT TO \\Gmc\d\Stmt\Monterey\UB_Test_1231
Local lcXML, loXMLAdapter, loXMLTable
Close Tables All

xmlfile="BPtestXML.txt"
xmlcontent = FILETOSTR(xmlfile)
lcXML = xmlcontent 

loXMLAdapter = CreateObject("XMLAdapter")

loXMLAdapter.LoadXML(lcXML,.F.,.T.)
&& XMLAdapter.LoadXML( cXMLDocument [, lFile [, lValidateOnParse ]] )

For Each loXMLTable in loXMLAdapter.Tables
   loXMLTable.ToCursor()
  	COPY TO (loXMLTable.Alias+".dbf")
ENDFOR

Thanks for your assistance and Happy

Coding in 2009!

 

[Olaf Doschke]

As mentioned in tht quote, it's the ToCursor() call that needs to be changed, take a look at it's parameters:

lAppend - append to an existing cursor/table, if .T.
cAlias - Aliasname of the cursor to fill (optional -defaults to the Alias property of the Xmltable)
nCodepage - Codepage of the cursor (optional, defaults to 0)

That means all you need is the lAppend parameter, as the Alias property is what you already used to generate the DBFs via COPY TO.

CD ...\RevisedDataTypes\
For lnCount = 1 To Adir(laDbfs,"*.dbf")
   Use (laDbfs(lnCount,1)) In 0 Shared
Endfor 

...

loXMLAdapter.LoadXML(lcXML,.F.,.T.)
For Each loXMLTable in loXMLAdapter.Tables
   loXMLTable.ToCursor(.T.)
EndFor

Bye, Olaf.