General XML Output script

[Steve Wood]

This xbasic script creates XML output from any table(s) you include in the variable TBLS. The output is for ALL fields in the table, and has this structure:

<Records>
 <Table1Name>
  <FieldA>myvalue</FieldA>
  <FieldB>myvalue</FieldB>
 <Table1Name>
 <Table2Name>
  <FieldA>myvalue</FieldA>
  <FieldB>myvalue</FieldB>
 <Table2Name>
</Records>

'Date Created: 22-Dec-2007 09:27:29 AM
'Last Updated: 22-Dec-2007 09:38:57 AM
'Created By  : Steve Wood
'Updated By  : Steve Wood

'GENERAL UTILITY TO WRITE ALL TABLE RECORDS TO XLM OUTPUT
'ASSUMES ALL TABLES ARE FILTERED ON THE SAME KEY FIELD AND FILTER VALUE
'OUTPUT USES THE TABLE NAMES AS XML ELEMENT TAGS
dim myfilter as c
dim lst as c

'THIS IS MY TEST VALUE, REMOVE OR REPLACE WITH YOUR OWN
myfilter = "1106061328004846246" 'test value
 
'NAME THE OUTPUT FILE
xmlfile = "c:\records.xml"

'LIST YOUR TABLE HERE.
tbls = <<%txt%
tr_trust_master
tr_childbene
%txt%

'CODE BELOW CREATES OUTPUT.
'CHANGE 'RECORDS' TO ANOTHER VALUE IF APPROPRIATE
'NOTE MY FILTER IS BASED ON JOBID, CHANGE TO FIT YOUR SITUATION
lst = "<Records>" + crlf()
for each i in tbls
tbl=table.open(i)
query.filter = "Job_Id=" + s_quote(myfilter)
query.order  = "Job_Id"
query.options = "T"
tbl.fetch_first()
indx = tbl.query_create()
WHILE .not. tbl.fetch_eof()
	lst = lst + " <" + i + ">" + crlf()
	flds=tbl.field_name_get()
	FOR each foo in flds
		fld = tbl.field_get(foo)
		v = fld.value_get()
		g = "  <"+foo+">"+alltrim(v)+"</"+foo+">"
		lst = lst + g + crlf()
next
lst = lst + " </" + i + ">" + crlf()
tbl.fetch_next()
END WHILE
indx.close()
tbl.close()
tbl.query_detach()
next
lst = lst + "</Records>"

save_to_file(lst,xmlfile)

[Steve Wood]

Re: General XML Output script

Here is some related code. My initial example dumped the entire contents of whatever table(s) you set in the 'tbls' variable. The new code below provides more control. I wanted to be able to determine which tables and fields were included in the xml export. The code is very compact and hard to follow, but does produce a nicely formed xml file.

I created a table named xmlexport_detail (see attached image) which defined which tables and fields were to be included in the export. I can toggle any table in or out, or add or remove tables as necessary, same with any field.

Notice in the attached image that I have Element Name (this is an arbitrary variable name, not the field name) and Element Expression. The script executes the Expression and saves the value to the Element (variable). Any xbasic function would work just as if I typed it manually in the script. Using this, I can write proper xml, such as:

<Records>
 <Name>Steve Wood</Name>
 <Address>100 Main</Address>
 <Child>
  <Name>Mike</Name>
 </Child>
 <Child>
  <Name>Jane</Name>
 </Child>
<Records>

'Date Created: 22-Dec-2007 09:27:29 AM
'Last Updated: 22-Dec-2007 09:38:57 AM
'Created By  : Steve Wood
'Updated By  : Steve Wood
'Notes:
' - "Job_ID" is my key field for filter, replace with your own
' - replace my two lines setting "myfilter" with your own code to filter your tables
' - replace "xmlfile" with what you want your file named

dim myfilter as c
dim lst as c
dim tbl as p
myfilter = "8aed33a0f33049b2a644c46ac414fb60" ' test filter 
xmlfile = "c:\ats\xml\records2.xml"

lst = "<Records>" + crlf()

tbls = table.external_record_content_get("xmlexport_master","alltrim(tablename)","exporder","exclude=.f.")
for each h in tbls
	flds = table.external_record_content_get("xmlexport_detail","alltrim(element_name) +' = '+ alltrim(element_exp)","","Exclude=.f. .and. tablename="+quote(h))
	tbl=table.open("[PathAlias.ADB_Path]\\"+h)
	query.filter = "Job_Id=" + s_quote(myfilter)
	query.order  = "Job_Id"
	query.options = "T"
	tbl.fetch_first()
	indx = tbl.query_create()
	WHILE .not. tbl.fetch_eof()
		lst = lst + " <"+h+">" + crlf()
		for each j in flds
			evaluate_template(evaluate_string(j))
		next

		FOR each i in flds
			i = word(i,1)
			lst = lst + "  <"+i+">" + "{"+i+"}" + "</"+i+">" + crlf()
		next
		lst = evaluate_string(lst)
		lst = lst + " </"+h+">" + crlf()
		tbl.fetch_next()
	END WHILE
	indx.close()
	tbl.close()
	tbl.query_detach()
next
lst = lst + "</Records>"

save_to_file(lst,xmlfile)

[Steve Wood]

Re: General XML Output script

Slight modification for the script below. There are two FOR EACH loops referencing the variable "flds". Apparenly you can't use the same variable in two different FOR EACH loop, or at least in this case. So, add this:

flds2 = flds ' just under where it sets the value for flds

and then use flds2 in the second loop, as in:

FOR each i in flds2