Converting a Dataset to a RecordSet web services issue
- From: Kevin <gocaveman@xxxxxxxxx>
- Date: 20 Apr 2007 11:56:51 -0700
Ok, I have done a ton of research trying to convert a Dataset from the
output of a Web Service, to a RecordSet that is usable in some VBA
routines. I have tried many, many options, including the solution
(using the term loosly) here:
http://support.microsoft.com/default.aspx?scid=kb;en-us;316337
The trouble I have, is that I am currently stuck in Microsoft "type
hell", where I have thousands of types of objects on both sides, but
none of them are compatible with the types on the other side. I tried
the DLL hack that is listed in the URL above, but when I try to add it
into my project, I just get "Can't add a reference to the specified
file".
So, I have the output of my web service in a file to show the ouput of
the web service, and I have some example text that I am able to load
into. Does anyone have a routine that can perform the conversion?
My code:
Public Function ConvertXmlToRecordset(ByVal vsXml As String) As
ADODB.Recordset
Dim loRecordset As New ADODB.Recordset
Dim loStream As New ADODB.Stream
loStream.Open Options:=adOpenStreamUnspecified
loStream.WriteText vsXml
loStream.Position = 0
'write to file for testing purposes
'loStream.SaveToFile "C:\temp\test99.xml"
'read from file for testing purposes
loRecordset.Open Source:="C:\temp\test98.xml",
Options:=ADODB.CommandTypeEnum.adCmdFile
loRecordset.Open Source:=loStream,
Options:=ADODB.CommandTypeEnum.adCmdFile
'loRecordset.Open Source:=loStream,
Options:=ADODB.CommandTypeEnum.adCmdUnknown
Set ConvertXmlToRecordset = loRecordset
Incoming from the Web Service:
<soap:Body xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/";>
<GetPartMasterResponse xmlns="http://tempuri.org/";>
<GetPartMasterResult>
<xs:schema id="NewDataSet" xmlns="" xmlns:xs="http://www.w3.org/2001/
XMLSchema" xmlns:msdata="urn:schemas-microsoft-com:xml-msdata">
<xs:element name="NewDataSet" msdata:IsDataSet="true";
msdata:UseCurrentLocale="true";>
<xs:complexType><xs:choice minOccurs="0" maxOccurs="unbounded">
<xs:element name="Table">
<xs:complexType><xs:sequence><xs:element name="PART_NBR"
type="xs:string" minOccurs="0"/><xs:element name="Description"
type="xs:string"
minOccurs="0"/><xs:element name="Status" type="xs:string"
minOccurs="0"/><xs:element name="DRAWING_REVISION_LEVEL"
type="xs:string"
minOccurs="0"/><xs:element name="BOM_EXISTS" type="xs:string"
minOccurs="0"/><xs:element name="ECN_NBR" type="xs:string"
minOccurs="0"/><xs:element name="Facility" type="xs:string"
minOccurs="0"/></xs:sequence></xs:complexType></xs:element></
xs:choice></xs:complexType></xs:element>
</xs:schema>
<diffgr:diffgram xmlns:msdata="urn:schemas-microsoft-com:xml-msdata"
xmlns:diffgr="urn:schemas-microsoft-com:xml-diffgram-v1">
<NewDataSet xmlns=""><Table diffgr:id="Table1" msdata:rowOrder="0";>
<PART_NBR>7592780-001</PART_NBR>
<Description>MSE FLUID INTERCONNECT BOTTLE ASSEMBLY</Description>
<Status>Released</Status>
<DRAWING_REVISION_LEVEL>A</DRAWING_REVISION_LEVEL>
<BOM_EXISTS>Y</BOM_EXISTS>
<ECN_NBR>""</ECN_NBR>
<Facility>RB</Facility>
</Table>
</NewDataSet>
</diffgr:diffgram>
</GetPartMasterResult>
</GetPartMasterResponse>
</soap:Body>
Acceptable example text from MS.com:
<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="ShipperID" rs:number="1"
rs:basetable="shippers" rs:basecolumn="ShipperID"
rs:keycolumn="true">
<s:datatype dt:type="int" dt:maxLength="4" rs:precision="10"
rs:fixedlength="true" rs:maybenull="false"/>
</s:AttributeType>
<s:AttributeType name="CompanyName" rs:number="2"
rs:nullable="true" rs:write="true" rs:basetable="shippers"
rs:basecolumn="CompanyName">
<s:datatype dt:type="string" dt:maxLength="40" />
</s:AttributeType>
<s:AttributeType name="Phone" rs:number="3" rs:nullable="true"
rs:write="true" rs:basetable="shippers"
rs:basecolumn="Phone">
<s:datatype dt:type="string" dt:maxLength="24"/>
</s:AttributeType>
<s:extends type="rs:rowbase"/>
</s:ElementType>
</s:Schema>
<rs:data>
<z:row ShipperID="1" CompanyName="Speedy Express"
Phone="(503) 555-9831"/>
<z:row ShipperID="2" CompanyName="United Package"
Phone="(503) 555-3199"/>
<z:row ShipperID="3" CompanyName="Federal Shipping"
Phone="(503) 555-9931"/>
</rs:data>
</xml>
Anyway, there are a ton of references for the error "Recordset cannot
be created. Source XML is incomplete or invalid", which is what you
get when you put the output DataSet xml into an ADODB.Recordset.
Unfortunately, there don't seem to be any solutions that work.
Any help would be great. I am looking for a simple solution, if
possible. One that doesn't include a DLL would be preferrable.
Thanks,
-Kevin
.
- Follow-Ups:
- Prev by Date: Re: Gnome XSLT Processor
- Next by Date: Re: Converting a Dataset to a RecordSet web services issue
- Previous by thread: Gnome XSLT Processor
- Next by thread: Re: Converting a Dataset to a RecordSet web services issue
- Index(es):
Relevant Pages
|
|
