Load XML to ADO

[neilkonitzer]

I am interested in taking an XML string (or file) and loading the data into an ADO recordset. My attempts result in me receiving the "Recordset cannot be created. Source XML is incomplete or invalid" error. I know that I am getting this error because the XML was not previously persisted from ADO and therefore does not have the necessary ADO schema elements. Has anyone had success with a work-around? Thanks in advance! Neil Konitzer
Freisoft

http://www.freisoft.com

 

[jel]

Haven't tried it yet, but with
RS.Save "C:\temp\rs", adPersistXML
you can save your recordset in xml, why not use that as an example? The xml doesn't seem too complex, it assignes namespaces, a schema that defines your recordset (fieldnames, datatypes etc) and a datasection where each row is a node, and each field is an attribute to that node.

 

[neilkonitzer]

The problem is that the XML was not sourced from ADO, so I cannot use the Save method. My problem is getting the XML INTO an ADO recordset that was not there to begin with. Neil Konitzer
Freisoft

http://www.freisoft.com

 

[jel]

Well, it doesn't hurt to try and find out what ADO considers to be 'valid XML', does it?

I had no trouble loading this xml, describing 1 record with 1 integer-field (MyNumericField) with value 1:

<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='MyNumericField' rs:number='1' rs:nullable='true' rs:maydefer='true' rs:writeunknown='true'>
			<s:datatype dt:type='int' dt:maxLength='4' rs:precision='10' rs:fixedlength='true'/>
		</s:AttributeType>
		<s:extends type='rs:rowbase'/>
	</s:ElementType>
</s:Schema>
<rs:data>
	<z:row MyNumericField='1'/>
</rs:data>
</xml>

 

[neilkonitzer]

You had no problems loading this XML because it includes all the ADO schema elements that is required for loading ADO. The XML that you have included in your last post was saved by an ADO recordset. The XML that I am trying to load is received from a web service and does not include the rowset schema values.

I appreciate your help, but I don't think that you comprehend my issue. Try manually making an XML file without the rowschema elements and then try to load that into an ADO recordset. It just won't work.

I think that I'm going to use XSLT to transform the XML document for processing. I just wanting to see if there was a more direct 'A to B' path to take. Neil Konitzer
Freisoft

http://www.freisoft.com

 

[jel]

Yep, that's exactly what I tried to say: you need to include the schema, and your data-section must of course be valid to that schema. If you want to know what it looks like, just save a recordset...
Of course you could add the schema with string-manipulation in VB, but my guess is that using xsl is a lot easier.
I mean, you need to transform xml to xml, and that is what someone invented xsl for, isn't it?

 

[bobdk]

Hi Neil

Did you found a simple solution to convert an XML into an XML which works with ADO?

I have various XML tables (not created by ADO) which I have to import into ADO recordset...

I would be really appreciated to have some hint.

Best regards
bob