Database Search
If you can't find the information using the categories below, post a question over in our WinBatch Tech Support Forum.
TechHome
OLE with XML
- ADO Reading XML Data
- Creating User-Defined Types Using XML Document Object Model
- DBF to XML Converter
- Edit an Existing XML File
- Get First Column of Data from an Excel Spreadsheet
- MSXML Transformation via OLE
- Read XML Row by Row Based on Namespace String
- Replace Existing Element with New Data
- Soundex UDF
- Uses XML DOM and DHTML to Create a DSO-like editable HTML display
- XML Recordset and Excel Named Ranges
- XML Recordset Editing Script
- XML Recordset Primer
- XML Recordset to MDB
- XPATH and XML Parsing
Can't find the information you are looking for here? Then leave a message over on our WinBatch Tech Support Forum.
XML Recordset Editing Script
XML Recordset Editing Script
This uses XML DOM and DHTML to produce a DSO-like editable HTML display.
This goes the whole 9 yards if you want, it'll persist an MS Access recordset to XML via ADO, then dynamically retrieve and specify all namespace properties for the XML file. It then retreives the field width specs and builds a list for the HTML to dynamically allocate for display.
cleaned up some small bugs and the Caption/Span for the display area.
you can test it with the Northwind (NWIND.mdb) database. Let me know if you run into any issues. I haven't worked with MEMO fields, so that might be another version.
Winbatch 2004F, MSXML 4.0 SDK (http://msdn.microsoft.com/library/en-us/xmlsdk/html/xmmscxmloverview.asp?frame=true) must be installed...
#defineFunction ADOPersistMDBtoXML(dbase, sqlstr, xfile)
cnn = objectopen("ADODB.Connection")
rst = objectopen("ADODB.Recordset")
if fileexist(xfile) then filedelete(xfile)
adOpenDynamic = 2
adLockReadOnly = 1
adPersistXML = 1
; ' Open the connection, recordset and save to XML...
cnn.Open(strcat("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=", dbase, ";"))
rst.Open(sqlstr ,cnn, adOpenDynamic, adLockReadOnly)
rst.Save(xfile, adPersistXML)
objectclose(rst)
objectclose(cnn)
return
#endFunction
#defineFunction GetXMLNameSpaceStr(xfile)
xmlDoc = ObjectOpen("Msxml2.DOMDocument.4.0")
xmlDoc.async = @False
xmlDoc.load(xfile)
root = xmlDoc.documentElement
ratts = root.attributes
nsvalues = ""
for x = 0 to ratts.length-1
thisratt = ratts.item(x)
if strindexnc(thisratt.name, "xmlns", 1, @fwdscan)
thisns = strcat(thisratt.name, "='", thisratt.value, "'")
nsvalues = iteminsert(thisns, -1, nsvalues, " ")
endif
objectclose(thisratt)
next
objectclose(ratts)
objectclose(root)
objectclose(xmlDoc)
return(nsvalues)
#endFunction
#defineFunction GetXMLDataTypeFieldLengths(xfile, str, NameSpaceStr)
xmlDoc = ObjectOpen("Msxml2.DOMDocument.4.0")
xmlDoc.async = @False
xmlDoc.load(xfile)
if NameSpaceStr <> "" then xmlDoc.setProperty("SelectionNamespaces", NameSpaceStr)
nodepath = strcat("//", str)
fldlengthstr = ""
dNode = xmlDoc.selectNodes(nodepath)
for x = 0 to dNode.length-1
thisnode = dNode.item(x)
dtatts = thisnode.attributes
for y = 0 to dtatts.length-1
thisfldlength = dtatts.item(y)
if thisfldlength.name == "dt:maxLength" then fldlengthstr = iteminsert(thisfldlength.value, -1, fldlengthstr, "|")
objectclose(thisfldlength)
next
objectclose(dtatts)
objectclose(thisnode)
next
objectclose(dNode)
objectclose(xmlDoc)
return(fldlengthstr)
#endFunction
#definesubroutine startMSIE()
Browser = ObjectOpen("InternetExplorer.Application")
Browser.addressbar = @false
Browser.statusbar = @false
Browser.menubar = @false
Browser.toolbar = @false
browser.visible = @true
url = "c:\RiftsPC-URL.html"
browser.navigate(url)
WaitForPageLoad()
; setup the document object...
browserDoc = Browser.Document
all = browserdoc.all
return(browser)
#endsubroutine
#DefineSubroutine WaitForPageLoad() ; assume Browser
While browser.busy || browser.readystate == 1
TimeDelay(0.5)
EndWhile
While browser.Document.ReadyState != "complete"
TimeDelay(0.5)
EndWhile
return
#EndSubroutine
#DefineFunction GetNodeSetAttributes(xfile, str, namespacestr)
retstr = ""
Doc = ObjectOpen("Msxml2.DOMDocument.4.0")
Doc.async = @False
Doc.load(xfile)
if namespacestr <> "" then Doc.setProperty("SelectionNamespaces", namespacestr)
nodepath = strcat("//", str)
dNode = Doc.selectSingleNode(nodepath)
attlist = dNode.Attributes
for x = 0 to attlist.length-1
thisatt = attlist.item(x)
retstr = iteminsert(thisatt.name, -1, retstr, "|")
objectclose(thisatt)
next
objectclose(attlist)
objectclose(dNode)
objectclose(Doc)
return(retstr)
#EndFunction
#DefineFunction GetRecordsetData(xfile, str, namespacestr)
Doc = ObjectOpen("Msxml2.DOMDocument.4.0")
Doc.async = @False
Doc.load(xfile)
if namespacestr <> "" then Doc.setProperty("SelectionNamespaces", namespacestr)
nodepath = strcat("//", str)
retstr = ""
dNode = Doc.selectNodes(nodepath)
for y = 0 to dNode.length-1
thisnode = dNode.item(y)
data = thisnode.attributes
rowstr = ""
for x = 0 to data.length-1
thisdata = data.item(x)
rowstr = iteminsert(thisdata.value, -1, rowstr, "|")
objectclose(thisdata)
next
retstr = iteminsert(rowstr, -1, retstr, @tab)
objectclose(data)
objectclose(thisnode)
next
objectclose(dNode)
objectclose(Doc)
return(retstr)
#EndFunction
#DefineFunction WriteRecordsetData(xfile, chain, rsdata, namespacestr)
Doc = ObjectOpen("Msxml2.DOMDocument.4.0")
Doc.async = @False
Doc.load(xfile)
if namespacestr <> "" then Doc.setProperty("SelectionNamespaces", namespacestr)
nodepath = strcat("//", chain)
for y = 1 to itemcount(rsdata, @tab)
data = itemextract(y, rsdata, @tab)
dNode = Doc.selectNodes(nodepath)
thisnode = dNode.item(y-1)
attnode = thisnode.attributes
for x = 0 to attnode.length-1
thisdata = itemextract(x+1, data, "|")
thisatt = attnode.item(x)
thisatt.value = thisdata
objectclose(thisatt)
next
objectclose(attnode)
objectclose(thisnode)
objectclose(dNode)
next
Doc.save(xfile)
objectclose(Doc)
return
#EndFunction
xfile = "c:\test\stan\chgcode.xml" ; <-- change to your path...
;-----------------------------------------
; if you want to start from a different XML file, uncomment the next
; portion of code...and change the path to the XML file...
;-----------------------------------------
;dbase = "C:\Data\Access\nwind.mdb"
;sqlstr = "SELECT [FirstName], [LastName], [EmployeeID] from [Employees]"
;xfile = "c:\test\stan\rst.xml"
;ADOPersistMDBtoXML(dbase, sqlstr, xfile)
namespacestr = GetXMLNameSpaceStr(xfile)
str = "s:datatype"
ColumnWidthList = GetXMLDataTypeFieldLengths(xfile, str, namespacestr)
titlestr = "XML Edit Recordset Test"
str = "rs:data/z:row"
ColumnList = GetNodeSetAttributes(xfile, str, namespacestr)
MasterDataList = ""
MasterDataList = GetRecordsetData(xfile, str, namespacestr)
NumberOfRows = itemcount(MasterDataList, @tab)
DataPageSize = 20
NumberOfPages = NumberOfRows/DataPageSize
if NumberOfRows mod DataPageSize > 0 then NumberOfPages = NumberOfPages+1
Terminate (NumberOfRows == 0, "XML Recordset Editor", "No records found, exiting...")
CurrentPage = 1 ; start at Page 1...
br = startMSIE()
gosub WriteHTML
gosub BuildPageTable
gosub BuildPageControl
gosub BuildButtonControl
while @true
yields(2000)
if psel.innerText <> ""
pageval = psel.innerText
psel.innerText = ""
select @true
case pageval == "+"
if CurrentPage <> NumberOfPages
CurrentPage = CurrentPage + 1
gosub InventoryScreen
gosub BuildPageTable
endif
break
case pageval == "-"
if CurrentPage <> 1
CurrentPage = CurrentPage - 1
gosub InventoryScreen
gosub BuildPageTable
endif
break
endselect
endif
if bsel.innerText <> ""
result = bsel.innerText
if result == "Save"
gosub InventoryScreen
Main.innerHTML = ""
gosub SaveRecordset
endif
break
endif
endwhile
br.quit
return
exit
:BuildPageTable
endrec = CurrentPage * DataPageSize
if endrec > itemcount(MasterDataList, @tab) then endrec = itemcount(MasterDataList, @tab)
strec = endrec - (DataPageSize - 1)
if strec < 1 then strec = 1
;
ThisTable = `<table class="bigblue" border="1" cellpadding="3" style="border-collapse: collapse">`
;ThisTable = strcat(ThisTable, `<caption><b>Page %CurrentPage% of %NumberofPages%</b></caption>`)
ThisTable = strcat(ThisTable, `<tr align="center">`)
for cl = 1 to itemcount(ColumnList, "|")
thiscol = itemextract(cl, ColumnList, "|")
ThisTable = strcat(ThisTable, `<td><b>`, thiscol, `</b></td>`)
next
ThisTable = strcat(ThisTable, `</tr>`, @crlf)
;
for cl = strec to endrec
rownum = cl-strec+1
ThisTable = strcat(ThisTable, `<tr>`)
thisdata = itemextract(cl, MasterDataList, @tab)
for td = 1 to itemcount(thisdata, "|")
thisfld = itemextract(td, thisdata, "|")
colwidth = itemextract(td, ColumnWidthList, "|")
thisinput = strcat(`<input type="text" class="bigblue" size="`, colwidth, `" id="Col%td%Row%rownum%" value="`, thisfld, `">`)
ThisTable = strcat(ThisTable, `<td>`, thisinput, `</td>`)
next
ThisTable = strcat(ThisTable, `</tr>`, @crlf)
next
ThisTable = strcat(ThisTable, `</table>`, @crlf)
Main.innerHTML = ThisTable
MainCap = all.MainCap
MainCap.innerHTML = strcat(`<b>Page `, CurrentPage, ` of `, NumberofPages, `</b>`)
objectclose(MainCap)
return
:WriteHTML
browserdoc.write(" <br>")
browserdoc.title = titlestr
browserdoc.writeln('<style> .msg {font-size: 8pt; font-weight: bold; color: blue} .hidden {font-size: 1; font-weight: bold; color: white}')
browserdoc.writeln('.hand {cursor: hand;} .blue {color: blue; font-size: 8pt} .bigblue {color: blue; font-size: 10pt} .smblue {color: blue; font-size: 7pt}')
browserdoc.writeln('.frame {border: .25mm solid blue;} .silver {border: .25mm solid silver;} .bignavy {color: navy; font-size: 10pt}')
browserdoc.writeln('.smallscr {width: 845; height:215; overflow:auto;}')
browserdoc.writeln('.scr {width: 845; height:490; overflow:auto;}</style>')
browserdoc.writeln(`<center>`)
browserdoc.write(`<span id="MainCap" class="bigblue"></span><div class="msg scr" id="Main"></div><br>`)
browserdoc.write(`<div id="PageControl"></div><br><div id="ButtonControl"></div>`)
browserdoc.write(`<div class="hidden" id="psel"></div>`)
browserdoc.write(`<div class="hidden" id="bsel"></div>`)
Main = all.Main
PageControl = all.PageControl
ButtonControl = all.ButtonControl
psel = all.psel
bsel = all.bsel
return
:BuildPageControl
ThisTable = `<table id="dispTable" class="bigblue" border="0">`
ThisTable = strcat(ThisTable, `<tr><td><input type="submit" value="Page Up" onclick="psel.innerText='-'"></td>`, @crlf)
ThisTable = strcat(ThisTable, `<td> </td>`, @crlf)
ThisTable = strcat(ThisTable, `<td><input type="submit" value="Page Down" onclick="psel.innerText='+'"></td>`, @crlf)
ThisTable = strcat(ThisTable, `</tr></table>`, @crlf)
PageControl.innerHTML = ThisTable
dt = all.dispTable
mstyle = Main.style
mstyle.width = dt.clientWidth * 2
;mstyle.height = dt.Height
objectclose(mstyle)
objectclose(dt)
return
:BuildButtonControl
ThisTable = `<table class="bigblue" border="0">`
ThisTable = strcat(ThisTable, `<tr><td><input type="submit" value="Exit & Save" onclick="bsel.innerText='Save'"></td>`, @crlf)
ThisTable = strcat(ThisTable, `<td> </td>`, @crlf)
ThisTable = strcat(ThisTable, `<td><input type="submit" value="Discard" onclick="bsel.innerText='Exit'"></td>`, @crlf)
ThisTable = strcat(ThisTable, `</tr></table>`, @crlf)
ButtonControl.innerHTML = ThisTable
return
:InventoryScreen
for rn = strec to endrec
rownum = rn-strec+1
thisrow = ""
for cn = 1 to itemcount(ColumnList, "|")
colstr = strcat("Col", cn, "Row", rownum)
thiscol = all.item(colstr)
thisrow = iteminsert(thiscol.value, -1, thisrow, "|")
objectclose(thiscol)
next
MasterDataList = itemreplace(thisrow, rn, MasterDataList, @tab)
next
return
:SaveRecordset
MainCap = all.MainCap
MainCap.innerHTML = ""
objectclose(MainCap)
Main.innerHTML = strcat(`<span id="msgspan" class="bigblue"></span>`)
PageControl.innerHTML = ""
objectclose(PageControl)
ButtonControl.innerHTML = ""
objectclose(ButtonControl)
msgspan = all.item("msgspan")
msgspan.innerHTML = strcat("Saving Recordset...<br><br>please wait...")
WriteRecordsetData(xfile, str, MasterDataList, namespacestr)
return
Article ID: W16156
File Created: 2004:09:01:11:42:50
Last Updated: 2004:09:01:11:42:50