WinBatch Tech Support Home

Database Search

If you can't find the information using the categories below, post a question over in our WinBatch Tech Support Forum.

TechHome

WinBatch
OLE COM ADO CDO ADSI LDAP
OLE with XML

Can't find the information you are looking for here? Then leave a message over on our WinBatch Tech Support Forum.

XML Recordset Primer

by stan littlefield

Part 1

The following is attached under the premise that it is sometimes better to see things than to just read about them. As there have been recent threads concerning XML, this may help clarify a few points for others interested in persisted recordsets.

When I (and others) use the term XML Recordset it refers to a 'special case' data file which is created from the ADO Save() method [ this method refers to a Recordset Object ]. 

RS.Save("temp.xml",1)
will create an ascii file named temp.xml which can later be opened and treated as though it were an Access or Oracle Table. The RS [ in RS.Save() ], can refer to either an exiting table in a database, the results of a query, or a fabricated recordset [ just fields and values entered on the fly ].

There are 2 parts to an XML Recordset, the schema and the data section. The schema is more or less a data dictionary for each field in the Recordset; the data ( often called z-row data ) is simply the data for each field enclosed in single quotes.

The two following xml files were created from the attached script makexml.wbt. They are essentially the same, except for meta-data in the schema section which directly relates to how the original table was opened. ubatch.xml was created with the keyset cursor and batchupdate lock mode, which default.xml just used defaults. Examine both with Notepad, ubatch contains attribute entries for basetable and basecolumn which allows the xml data to be re-integrated back into the original table, or (and this is important) another database with a table with the same name and structure as the original.

default.xml lacks basetable data, but since it is just an ASCII File, these could be added.

Finally, there is the xmladdro script [which doesn't add rows, it just modifies data ]. Run this script [assuming you have MDAC installed] and it will

Now look at the data section of ubatchu with notepad and compare it to ubatch - because ubatchu has base table attributes, it preserves the original values as well as the edited value - very useful if you wanted to update your original table based on modifications you made to the xml file.

Now, if you were to modify the script and put in code to add a row of data, then look at the results in notepad you would probably see rs:insert /rs:insert tags around it.

UBATCH.XML

<xml xmlns:s='uuid:BDC6E3F0-6DA3-11d1-A2A3-00AA00C14882'
	xmlns:dt='uuid:C2F41010-65B3-11d1-A29F-00AA00C14882'
	xmlns:rs='urn:schemas-microsoft-com:rowset'
	xmlns:z='#RowsetSchema'>
<s:Schema id='RowsetSchema'>
	<s:ElementType name='row' content='eltOnly' rs:updatable='true'>
		<s:AttributeType name='COMPANY' rs:number='1' rs:nullable='true' rs:maydefer='true' rs:write='true' rs:basetable='resorts'
			 rs:basecolumn='COMPANY'>
			<s:datatype dt:type='string' dt:maxLength='35'/>
		</s:AttributeType>
		<s:AttributeType name='EMAIL' rs:number='5' rs:nullable='true' rs:maydefer='true' rs:write='true' rs:basetable='resorts'
			 rs:basecolumn='EMAIL'>
			<s:datatype dt:type='string' dt:maxLength='50'/>
		</s:AttributeType>
		<s:AttributeType name='FAX' rs:number='4' rs:nullable='true' rs:maydefer='true' rs:write='true' rs:basetable='resorts'
			 rs:basecolumn='FAX'>
			<s:datatype dt:type='string' dt:maxLength='14'/>
		</s:AttributeType>
		<s:AttributeType name='FILE' rs:number='6' rs:nullable='true' rs:maydefer='true' rs:write='true' rs:basetable='resorts'
			 rs:basecolumn='FILE'>
			<s:datatype dt:type='string' dt:maxLength='12'/>
		</s:AttributeType>
		<s:AttributeType name='PHONE' rs:number='3' rs:nullable='true' rs:maydefer='true' rs:write='true' rs:basetable='resorts'
			 rs:basecolumn='PHONE'>
			<s:datatype dt:type='string' dt:maxLength='14'/>
		</s:AttributeType>
		<s:AttributeType name='POC' rs:number='2' rs:nullable='true' rs:maydefer='true' rs:write='true' rs:basetable='resorts'
			 rs:basecolumn='POC'>
			<s:datatype dt:type='string' dt:maxLength='35'/>
		</s:AttributeType>
		<s:extends type='rs:rowbase'/>
	</s:ElementType>
</s:Schema>
<rs:data>
	<z:row COMPANY='BEACH COVE' EMAIL='bsmith@sccoast.net' FAX='843-918-8399' FILE='bchcove.doc' PHONE='843-918-9111'
		 POC='Bob Simth'/>
	<z:row COMPANY='CARAVELLE' EMAIL='kevelle@sccoast.net' FAX='843-918-7093' FILE='cvelle.doc' PHONE='843-918-8222'
		 POC='Kevin Jones'/>
</rs:data>
</xml>

DEFAULT.XML

<xml xmlns:s='uuid:BDC6E3F0-6DA3-11d1-A2A3-00AA00C14882'
	xmlns:dt='uuid:C2F41010-65B3-11d1-A29F-00AA00C14882'
	xmlns:rs='urn:schemas-microsoft-com:rowset'
	xmlns:z='#RowsetSchema'>
<s:Schema id='RowsetSchema'>
	<s:ElementType name='row' content='eltOnly'>
		<s:AttributeType name='COMPANY' rs:number='1' rs:nullable='true' rs:maydefer='true' rs:writeunknown='true'>
			<s:datatype dt:type='string' dt:maxLength='35'/>
		</s:AttributeType>
		<s:AttributeType name='POC' rs:number='2' rs:nullable='true' rs:maydefer='true' rs:writeunknown='true'>
			<s:datatype dt:type='string' dt:maxLength='35'/>
		</s:AttributeType>
		<s:AttributeType name='PHONE' rs:number='3' rs:nullable='true' rs:maydefer='true' rs:writeunknown='true'>
			<s:datatype dt:type='string' dt:maxLength='14'/>
		</s:AttributeType>
		<s:AttributeType name='FAX' rs:number='4' rs:nullable='true' rs:maydefer='true' rs:writeunknown='true'>
			<s:datatype dt:type='string' dt:maxLength='14'/>
		</s:AttributeType>
		<s:AttributeType name='EMAIL' rs:number='5' rs:nullable='true' rs:maydefer='true' rs:writeunknown='true'>
			<s:datatype dt:type='string' dt:maxLength='50'/>
		</s:AttributeType>
		<s:AttributeType name='FILE' rs:number='6' rs:nullable='true' rs:maydefer='true' rs:writeunknown='true'>
			<s:datatype dt:type='string' dt:maxLength='12'/>
		</s:AttributeType>
		<s:extends type='rs:rowbase'/>
	</s:ElementType>
</s:Schema>
<rs:data>
	<z:row COMPANY='BEACH COVE' POC='Bob Simth' PHONE='843-918-9111' FAX='843-918-8399' EMAIL='bsmith@sccoast.net'
		 FILE='bchcove.doc'/>
	<z:row COMPANY='CARAVELLE' POC='Kevin Jones' PHONE='843-918-8222' FAX='843-918-7093' EMAIL='kevelle@sccoast.net'
		 FILE='cvelle.doc'/>
</rs:data>
</xml>

 


MAKEXML.WBT
 
;// Winbatch - two ways to persist a table to xml
;// stan littlefield- May 5, 2003

cMDB  = StrCat( dirget(),"resorts.mdb")
DB = ObjectOpen("ADODB.Connection")
cConn = "Provider=MicroSoft.Jet.OLEDB.4.0; Data Source=%cMDB%"
DB.Open(cConn)
RS = ObjectOpen("ADODB.Recordset")

;// this creates an xml file with base table data
RS.Open("resorts",cConn,1,4,512)
RS.Save( "ubatch.xml",1)
RS.Close()

;// this omits base table data
RS.Open("resorts",cConn)
RS.Save( "default.xml",1)


RS.Close()
ObjectClose(RS)
DB.Close()
ObjectClose(DB)
Exit

XMLADDRO.WBT

;// Winbatch - updating a xml file
;// the file ubatchu.xml started as a copy of
;// ubatch.xml which was persisteed from the makexml script
;//
;// In this script, the Company Field is modified
;// Since the original file was peresisted as batchupdate
;// the file will store meta-data, or the original value

cOLD = StrCat(dirget(),"ubatch.xml")
IF ! FileExist(cOLD) Then Exit
cXML = StrCat(dirget(),"ubatchu.xml")
FileCopy(cOLD,cXML,@FALSE)
RS        = ObjectOpen("ADODB.RecordSet")
RS.Open(cXML,"Provider=MSPersist;",1,4,256)

fld = RS.Fields("COMPANY")

While ! RS.Eof()
   x = fld.Value
   fld.Value = StrLower(x)
   RS.MoveNext()
Endwhile

RS.Save(cXML,1)
RS.Close()
ObjectClose(RS)
Exit

Part 2

I have attached a test file, a WSC that must be registered and a script - intended as a generic means to prepare an XML recordset that was not Saved as a disconnected RS in the first place so that it is treated as such.

First, the VB code I posted which performed the preparation has (in my opinion ) a bug. Therefore I modified the code as a WSC function. Lucky we will all be the day WB can code as 

For each Node in NodeList
Second, the WSC references the MSXML Dom 4.0 - just change that line to 
CreateObject("Microsoft.XMLDOM")
which I believe comes with IE, ad it will work.

In Part 3, I'll post the round-trip; from Fabricated Recrodset to insertion into Table.

Default.sav

<xml xmlns:s='uuid:BDC6E3F0-6DA3-11d1-A2A3-00AA00C14882'
	xmlns:dt='uuid:C2F41010-65B3-11d1-A29F-00AA00C14882'
	xmlns:rs='urn:schemas-microsoft-com:rowset'
	xmlns:z='#RowsetSchema'>
<s:Schema id='RowsetSchema'>
        <s:ElementType name='row' content='eltOnly' rs:updatable='true'>
                <s:AttributeType name='COMPANY' rs:number='1' rs:nullable='true' rs:maydefer='true' rs:writeunknown='true'>
			<s:datatype dt:type='string' dt:maxLength='35'/>
		</s:AttributeType>
		<s:AttributeType name='POC' rs:number='2' rs:nullable='true' rs:maydefer='true' rs:writeunknown='true'>
			<s:datatype dt:type='string' dt:maxLength='35'/>
		</s:AttributeType>
		<s:AttributeType name='PHONE' rs:number='3' rs:nullable='true' rs:maydefer='true' rs:writeunknown='true'>
			<s:datatype dt:type='string' dt:maxLength='14'/>
		</s:AttributeType>
		<s:AttributeType name='FAX' rs:number='4' rs:nullable='true' rs:maydefer='true' rs:writeunknown='true'>
			<s:datatype dt:type='string' dt:maxLength='14'/>
		</s:AttributeType>
		<s:AttributeType name='EMAIL' rs:number='5' rs:nullable='true' rs:maydefer='true' rs:writeunknown='true'>
			<s:datatype dt:type='string' dt:maxLength='50'/>
		</s:AttributeType>
		<s:AttributeType name='FILE' rs:number='6' rs:nullable='true' rs:maydefer='true' rs:writeunknown='true'>
			<s:datatype dt:type='string' dt:maxLength='12'/>
		</s:AttributeType>
		<s:extends type='rs:rowbase'/>
	</s:ElementType>
</s:Schema>
<rs:data>
	<z:row COMPANY='BEACH COVE' POC='Bob Simth' PHONE='843-918-9111' FAX='843-918-8399' EMAIL='bsmith@sccoast.net'
		 FILE='bchcove.doc'/>
	<z:row COMPANY='CARAVELLE' POC='Kevin Jones' PHONE='843-918-8222' FAX='843-918-7093' EMAIL='kevelle@sccoast.net'
		 FILE='cvelle.doc'/>
</rs:data>
</xml>

XMLFit.wsc

<?xml version="1.0"?>
<component>

<?component error="true" debug="true"?>

<registration
	description="XMLfit"
        progid="XMLfit.WSC"
	version="1.00"
	classid="{171ef928-4e75-4824-9215-4da3134436a6}"
>
</registration>

<public>
        <method name="AddAttributes">
		<PARAMETER name="xmlfile"/>
                <PARAMETER name="tablename"/>
	</method>
</public>

<implements type="Behavior" id="Behavior"/>

<script language="VBScript">
<![CDATA[

function AddAttributes(xmlfile,tablename)
    Dim DOMDoc
    Dim Root
    Dim Schema
    Dim ElementType
    Dim Node
    Dim Item
    Dim NewItem
    Dim XMLSource
    Set DOMDoc = CreateObject("MSXML2.DOMDocument.4.0")

    DOMDoc.Load xmlfile
    Set Root = DOMDoc.childNodes.Item(0)
    Set Schema = Root.childNodes(0)
    Set ElementType = Schema.childNodes(0)

    For Each Node In ElementType.childNodes
        If UCase(Node.baseName) = "ATTRIBUTETYPE" Then
            For Each Item In Node.Attributes
                If Item.baseName = "write" Then
                    ' Remove this attribute, which is unsupported
                    ' when the Recordset will be loaded
                    Node.Attributes.removeNamedItem Item.nodeName
                    Exit For
                End If
            Next

            Set NewItem = DOMDoc.createAttribute("rs:basetable")
            NewItem.Value = tablename
            Node.Attributes.setNamedItem NewItem

            Set NewItem = DOMDoc.createAttribute("rs:basecolumn")
            ' Assumes that the logical name is equal to the physical name
            NewItem.Value = Node.Attributes(0).nodeValue
            Node.Attributes.setNamedItem NewItem
        End If
    Next

    AddAttributes = Replace(DOMDoc.xml, Chr(34), "'")
    Set DOMDoc = Nothing
    Set Root = Nothing
    Set Schema = Nothing
    Set ElementType = Nothing
    Set Node = Nothing
    Set Item = Nothing
    Set NewItem = Nothing


end function

]]>
</script>

</component>

XMLFit.wbt

;// Winbatch - Updating XML Recordset
;//            So it might be used to Create
;//            a table with a similar structure in
;//            a Database, then insert rows
;//
;// Stan Littlefield - May 6, 2003
;////////////////////////////////////////////////////

;//  default.sav was created as a fabricated Recordset
;//  which read in some data from a MS Word File
;//  which was persisted
cXMX = StrCat(dirget(),"default.sav")
If ! FileExist(cXMX) Then Exit

;// default.xml will now be prepared with base table
;// information in the schema sectiom
;// addtionallu, the rows will be prepared to be
;// inserted into, say an Access Table at a later point
cXML = StrCat(dirget(),"default.xml")
FileCopy(cXMX,cXML,@FALSE)

BoxOpen("Loading and Preparing..",cXML)
; This is the tablename you want created or updated
cTable = "resorts"

;// perhaps our XML file already has base table info
cString = FileGet( cXML )
IF StrIndex(cString,"basetable",1,@FWDSCAN) Then goto setInsert
Drop(cString)

;// I threw together a WSC because I hate
;// wrestling with the Dom in WB code
oDom = ObjectOpen("XMLfit.WSC")
cString = oDom.AddAttributes(cXML,cTable)
ObjectClose(oDom)


:setInsert
If ! StrIndex(cString,"<rs:insert>",1,@FWDSCAN)
   cString = StrReplace(cString,"<rs:data>",StrCat("<rs:data>",@CRLF,"   <rs:insert>"))
   cString = StrReplace(cString,"</rs:data>",StrCat("   </rs:insert>",@CRLF,"</rs:data>"))
Endif
FilePut(cXML,cString)

BoxShut()
Exit

Part 3 (a)

Reading this will make little or no sense unless you have read previous posts on this topic, and/or played around with the example code/WSC files.

The goal is more or less a generic set of procedures to take an XML file and update its field structure and rows to a database Table ( i.e. Access, Oracle, SQL Server ). In simplest terms, if you took each field in the XML and the field type and created a matching field/type in your Table, the data would transfer. However the XML file also contains data for field length, decimal precision, whether the field can contain nulls.

Which still wouldn't seem a huge problem except for ADOX vs. the Recordset -

The XML recordset is traversed by obtaining information from the Field Object Collection. However, when building the Table is say Access, you use ADOX, which has a Column Object Collection. How do these match up...

stay tuned for Part 3(b)....

Part 3 (b)

I created a simple Access table with field names to represent various data types ( fboolean, fmemo... ) I then entered a single row, the opened the Recordset [the table is named colfield] and persisted it to an XML file.

Then, I ran a script to enumerate the properties and attributes of the data using (1) the Field Object - for the XML file (2) the Column Object - for the Access Table.

...same data, basically the same information, ah but my kingdom for some standardization. Remember, the focus is use the XML [Field] metadata to create an ADOX [Column] Table. The attached illustrates what matches with what.

colfield.txt

Comparing ADOX COLUMN Properties
To XML FIELD Properties
For DataSet: COLFIELD

ADOX COLUMN [Name]=fcurrency
ADOX COLUMN [Type]=6
ADOX COLUMN [DefinedSize]=0
ADOX COLUMN [NumericScale]=0
ADOX COLUMN [Precision]=19
---ADOX COLUMN [Attributes]---
  -  Fixed Length
  -  Accepts Nulls
---ADOX COLUMN [PROPERTIES]---
Default=0
Description=
Nullable=-1
Fixed Length=-1
Seed=1
Increment=1
Jet OLEDB:Column Validation Text=
Jet OLEDB:Column Validation Rule=
Jet OLEDB:IISAM Not Last Column=0
Jet OLEDB:AutoGenerate=0
Jet OLEDB:One BLOB per Page=0
Jet OLEDB:Compressed UNICODE Strings=0
Jet OLEDB:Allow Zero Length=0
Jet OLEDB:Hyperlink=0

ADOX COLUMN [Name]=fdecimal
ADOX COLUMN [Type]=131
ADOX COLUMN [DefinedSize]=0
ADOX COLUMN [NumericScale]=4
ADOX COLUMN [Precision]=18
---ADOX COLUMN [Attributes]---
  -  Fixed Length
  -  Accepts Nulls
---ADOX COLUMN [PROPERTIES]---
Default=0
Description=
Nullable=-1
Fixed Length=0
Seed=1
Increment=1
Jet OLEDB:Column Validation Text=
Jet OLEDB:Column Validation Rule=
Jet OLEDB:IISAM Not Last Column=0
Jet OLEDB:AutoGenerate=0
Jet OLEDB:One BLOB per Page=0
Jet OLEDB:Compressed UNICODE Strings=0
Jet OLEDB:Allow Zero Length=-1
Jet OLEDB:Hyperlink=0

ADOX COLUMN [Name]=fmemo
ADOX COLUMN [Type]=203
ADOX COLUMN [DefinedSize]=0
ADOX COLUMN [NumericScale]=0
ADOX COLUMN [Precision]=0
---ADOX COLUMN [Attributes]---
  -  Not Fixed Length
  -  Accepts Nulls
---ADOX COLUMN [PROPERTIES]---
Default=0
Description=
Nullable=-1
Fixed Length=0
Seed=1
Increment=1
Jet OLEDB:Column Validation Text=
Jet OLEDB:Column Validation Rule=
Jet OLEDB:IISAM Not Last Column=0
Jet OLEDB:AutoGenerate=0
Jet OLEDB:One BLOB per Page=0
Jet OLEDB:Compressed UNICODE Strings=0
Jet OLEDB:Allow Zero Length=0
Jet OLEDB:Hyperlink=0

ADOX COLUMN [Name]=fnumber
ADOX COLUMN [Type]=5
ADOX COLUMN [DefinedSize]=0
ADOX COLUMN [NumericScale]=0
ADOX COLUMN [Precision]=15
---ADOX COLUMN [Attributes]---
  -  Fixed Length
  -  Accepts Nulls
---ADOX COLUMN [PROPERTIES]---
Default=0
Description=
Nullable=-1
Fixed Length=-1
Seed=1
Increment=1
Jet OLEDB:Column Validation Text=
Jet OLEDB:Column Validation Rule=
Jet OLEDB:IISAM Not Last Column=0
Jet OLEDB:AutoGenerate=0
Jet OLEDB:One BLOB per Page=0
Jet OLEDB:Compressed UNICODE Strings=0
Jet OLEDB:Allow Zero Length=0
Jet OLEDB:Hyperlink=0

ADOX COLUMN [Name]=ftext
ADOX COLUMN [Type]=202
ADOX COLUMN [DefinedSize]=20
ADOX COLUMN [NumericScale]=0
ADOX COLUMN [Precision]=0
---ADOX COLUMN [Attributes]---
  -  Not Fixed Length
  -  Accepts Nulls
---ADOX COLUMN [PROPERTIES]---
Default=0
Description=
Nullable=-1
Fixed Length=0
Seed=1
Increment=1
Jet OLEDB:Column Validation Text=
Jet OLEDB:Column Validation Rule=
Jet OLEDB:IISAM Not Last Column=0
Jet OLEDB:AutoGenerate=0
Jet OLEDB:One BLOB per Page=0
Jet OLEDB:Compressed UNICODE Strings=-1
Jet OLEDB:Allow Zero Length=0
Jet OLEDB:Hyperlink=0

Field Info For XML Table: COLFIELD
XML FIELD [Name]=fboolean
XML FIELD [Type]=11
XML FIELD [DefinedSize]=2
XML FIELD [ActualSize]=2
XML FIELD [NumericScale]=255
XML FIELD [Precision]=255
---XML FIELD [Attributes]---
  -  Not Cache Deferred
  -  Fixed Length
  -  Is Not Chapter
  -  Is Not Collection
  -  Is Not Stream
  -  Does Not Accept Nulls
  -  Is Not Row URL
  -  Is Not LongBinary
  -  May Be Null
  -  May Defer
  -  Does Not Support Negatives
  -  Is Not Row ID
  -  Is Not Row Version
  -  May Be Updateable
  -  Updateable
---XML FIELD [PROPERTIES]---
BASETABLENAME=colfield
BASECATALOGNAME=
BASESCHEMANAME=
KEYCOLUMN=0
ISAUTOINCREMENT=0
RELATIONCONDITIONS=
CALCULATIONINFO=
OPTIMIZE=0

XML FIELD [Name]=fcurrency
XML FIELD [Type]=6
XML FIELD [DefinedSize]=8
XML FIELD [ActualSize]=8
XML FIELD [NumericScale]=255
XML FIELD [Precision]=19
---XML FIELD [Attributes]---
  -  Not Cache Deferred
  -  Fixed Length
  -  Is Not Chapter
  -  Is Not Collection
  -  Is Not Stream
  -  Accepts Nulls
  -  Is Not Row URL
  -  Is Not LongBinary
  -  May Be Null
  -  May Defer
  -  Does Not Support Negatives
  -  Is Not Row ID
  -  Is Not Row Version
  -  May Be Updateable
  -  Updateable
---XML FIELD [PROPERTIES]---
BASETABLENAME=colfield
BASECATALOGNAME=
BASESCHEMANAME=
KEYCOLUMN=0
ISAUTOINCREMENT=0
RELATIONCONDITIONS=
CALCULATIONINFO=
OPTIMIZE=0

XML FIELD [Name]=fdecimal
XML FIELD [Type]=131
XML FIELD [DefinedSize]=19
XML FIELD [ActualSize]=19
XML FIELD [NumericScale]=4
XML FIELD [Precision]=18
---XML FIELD [Attributes]---
  -  Not Cache Deferred
  -  Fixed Length
  -  Is Not Chapter
  -  Is Not Collection
  -  Is Not Stream
  -  Accepts Nulls
  -  Is Not Row URL
  -  Is Not LongBinary
  -  May Be Null
  -  May Defer
  -  Does Not Support Negatives
  -  Is Not Row ID
  -  Is Not Row Version
  -  May Be Updateable
  -  Updateable
---XML FIELD [PROPERTIES]---
BASETABLENAME=colfield
BASECATALOGNAME=
BASESCHEMANAME=
KEYCOLUMN=0
ISAUTOINCREMENT=0
RELATIONCONDITIONS=
CALCULATIONINFO=
OPTIMIZE=0

XML FIELD [Name]=fmemo
XML FIELD [Type]=203
XML FIELD [DefinedSize]=536870910
XML FIELD [ActualSize]=226
XML FIELD [NumericScale]=255
XML FIELD [Precision]=255
---XML FIELD [Attributes]---
  -  Not Cache Deferred
  -  Not Fixed Length
  -  Is Not Chapter
  -  Is Not Collection
  -  Is Not Stream
  -  Accepts Nulls
  -  Is Not Row URL
  -  Is LongBinary
  -  May Be Null
  -  May Defer
  -  Does Not Support Negatives
  -  Is Not Row ID
  -  Is Not Row Version
  -  May Be Updateable
  -  Updateable
---XML FIELD [PROPERTIES]---
BASETABLENAME=colfield
BASECATALOGNAME=
BASESCHEMANAME=
KEYCOLUMN=0
ISAUTOINCREMENT=0
RELATIONCONDITIONS=
CALCULATIONINFO=
OPTIMIZE=0

XML FIELD [Name]=fnumber
XML FIELD [Type]=5
XML FIELD [DefinedSize]=8
XML FIELD [ActualSize]=8
XML FIELD [NumericScale]=255
XML FIELD [Precision]=15
---XML FIELD [Attributes]---
  -  Not Cache Deferred
  -  Fixed Length
  -  Is Not Chapter
  -  Is Not Collection
  -  Is Not Stream
  -  Accepts Nulls
  -  Is Not Row URL
  -  Is Not LongBinary
  -  May Be Null
  -  May Defer
  -  Does Not Support Negatives
  -  Is Not Row ID
  -  Is Not Row Version
  -  May Be Updateable
  -  Updateable
---XML FIELD [PROPERTIES]---
BASETABLENAME=colfield
BASECATALOGNAME=
BASESCHEMANAME=
KEYCOLUMN=0
ISAUTOINCREMENT=0
RELATIONCONDITIONS=
CALCULATIONINFO=
OPTIMIZE=0

XML FIELD [Name]=ftext
XML FIELD [Type]=202
XML FIELD [DefinedSize]=20
XML FIELD [ActualSize]=32
XML FIELD [NumericScale]=255
XML FIELD [Precision]=255
---XML FIELD [Attributes]---
  -  Not Cache Deferred
  -  Not Fixed Length
  -  Is Not Chapter
  -  Is Not Collection
  -  Is Not Stream
  -  Accepts Nulls
  -  Is Not Row URL
  -  Is Not LongBinary
  -  May Be Null
  -  May Defer
  -  Does Not Support Negatives
  -  Is Not Row ID
  -  Is Not Row Version
  -  May Be Updateable
  -  Updateable
---XML FIELD [PROPERTIES]---
BASETABLENAME=colfield
BASECATALOGNAME=
BASESCHEMANAME=
KEYCOLUMN=0
ISAUTOINCREMENT=0
RELATIONCONDITIONS=
CALCULATIONINFO=
OPTIMIZE=0

Part 3 (c)

Previously, I illustrated going from an established table to a corresponding XML recordset; but the real goal is to go the other way. The attached script creates a recordset (fabricated), and persists it. Use the WSC I posted to prepare that data to be moved into a Database Table.

Testtrs.wbt

;// Winbatch - building an XML Recordset from scratch
;// Stan Littlefield - May 11, 2003
;//
;// NOTE: this sample uses hard-coded field types
;// you would probably want them set up as constants
;// i.e. adChar = 202
;////////////////////////////////////////////////////////
cXML = StrCat(dirget(),"testrs.sav")
If FileExist(cXML) Then FileDelete(cXML)

RS = ObjectOpen("ADODB.Recordset")
RS.CursorLocation = 3
flds = RS.Fields

;establish basic field properties
flds.append(:: Name="fboolean",Type=11,DefinedSize=2)
flds.append(:: Name="fcurrency",Type=6,DefinedSize=8)
flds.append(:: Name="fdecimal",Type=131,DefinedSize=19)
flds.append(:: Name="fmemo",Type=203,DefinedSize=536870910)
flds.append(:: Name="ftext",Type=202,DefinedSize=20)
flds.append(:: Name="fnumber",Type=5,DefinedSize=8)

; individually add addtional properties
fld = RS.Fields("fcurrency")
fld.Precision = 19
fld.NumericScale = 2

fld = RS.Fields("fdecimal")
fld.Precision = 18
fld.NumericScale = 4


RS.Open()

; add sample data
;// below 1 row is added, but you could parse in data
;// from a Web site, read in an ASCII file, take data
;// from a WORD or EXCEL document, or add bitmaps
;// and/or other binary data.
RS.Addnew()
fld = RS.Fields("fboolean")
fld.Value = @TRUE

fld = RS.Fields("fcurrency")
fld.Value = 55.00

fld = RS.Fields("fdecimal")
fld.Value = 99.4421

fld = RS.Fields("fnumber")
fld.Value = 4444444

fld = RS.Fields("ftext")
fld.Value = "Bill Shakespeare"

fld = RS.Fields("fmemo")
memo = "Is this the Sum Total of our Lives"
memo = strcat(memo,@CRLF,"A Tale Told by an Idiot, full of Sound and Fury")
memo = strcat(memo,@CRLF,"All of it signifying Nothing?")
fld.Value = memo


RS.Save(cXML,1)
;// at this point the data is saved to XML
;// and the field elements will be in the schema section
;// If you eventually wanted the data in say Access
;// Simply pass the xml file and a 'base table' name
;// to the XMLfit.WSC file I posted.
;// If you wanted the data sent to say SQL Server, in
;// addition to the basetable, you would want to add a
;// basecatalog, so modify the WSC accordingly


RS.Close()
ObjectClose(RS)
Exit

Article ID:   W16157

File Created: 2004:03:30:15:43:00
Last Updated: 2004:03:30:15:43:00