Francisco Tapia
fhtapia at gmail.com
Fri Jul 1 18:58:15 CDT 2005
Very COOL, Thanks for the FAST response! :)
On 7/1/05, MartyConnelly <martyconnelly at shaw.ca> wrote:
> Ok, here is some code to get you started it reads a northwind table
> and outputs three types of files
> XML Flat file format just elements plus initial XML PI
> XML ADTG format ( the type with zRows schema) suitable to be read
> directly back into an mdb
> CSV file
>
> Where you want to change this code is where I load the northwind SQL
> recordset.
> Replace it by what you have returned from XMLHTTP by oDOM.loadXML
> (objXMLHTTP.responseXML.xml)
> What you are getting returned in response object is just one long xml
> string.
>
> Application.ExportXML won't work here as it has to be an Access Object
> to be output.
> You might also want to use .validateonparse method to error check the
> xml you are getting initally.
>
> If you wanted to once you have your returned xml in the dom you could
> use Xpath
> to grab specific fields.
>
> Sub readmdb()
>
> Dim sSQL As String
> Dim iNumRecords As Integer
>
> Dim oConnection As ADODB.Connection
> Dim oRecordset As ADODB.Recordset
> Dim rstSchema As ADODB.Recordset
> Dim sConnStr As String
>
> 'sConnStr = "Provider=SQLOLEDB;Data Source=MySrvr;" & _
> "Initial Catalog=Northwind;User Id=MyId;Password=123aBc;"
> ' Connection "Provider=Microsoft.Jet.OLEDB.3.51;Data
> Source=D:\DataBases\Northwind.mdb"
>
> 'Access 97 version Jet 3.51
> ' sConnStr = "Provider=Microsoft.Jet.OLEDB.3.51;" & _
> "Data Source=C:\Program Files\Microsoft
> Office\Office\Samples\Northwind.mdb;" & _
> "User Id=admin;" & "Password="
> 'Access XP Jet 4
> sConnStr = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
> "Data Source=C:\Program Files\Microsoft
> Office\Office\Samples\Northwind.mdb;" & _
> "User Id=admin;" & "Password="
>
> 'On Error GoTo GetDataError
>
> ' Create and Open the Connection object.
> Set oConnection = New ADODB.Connection
> oConnection.CursorLocation = adUseClient
> oConnection.Open sConnStr
>
> sSQL = "SELECT ProductID, ProductName, CategoryID, UnitPrice " & _
> "FROM Products"
>
> ' Create and Open the Recordset object.
> Set oRecordset = New ADODB.Recordset
> oRecordset.Open sSQL, oConnection, adOpenStatic, _
> adLockBatchOptimistic, adCmdText
> With oRecordset
> Debug.Print .RecordCount
> Dim i As Long
> Dim strOut As String
> strOut = ""
> .MoveFirst
> For i = 0 To .RecordCount - 1
> strOut = strOut & !ProductName
> strOut = strOut & "," & !CategoryID
> strOut = strOut & "," & !UnitPrice
> strOut = strOut & vbCrLf
> .MoveNext
> Next i
> End With
> 'Save as Access csv file
> WriteFile "C:\Access files\xmlfiles\strout" & Format(Now,
> "yyyymmddhhmmss") & ".xml", strOut
>
> Dim oDOM As MSXML2.DOMDocument
> Dim oXML As MSXML2.DOMDocument
> Dim oXSL As MSXML2.DOMDocument
> Dim strHTML As String
> Dim strTransform As String
>
> Set oDOM = CreateObject("MSXML2.DOMDocument")
> oDOM.async = False
> 'Load the XML DOM
> 'Put recordset into XML Dom
> ' Here you would load your xml string
> ' read from whatever is returned by xmlhttp or xnlhttpserver into the
> XML DOM
> ' I forget if it is objXMLHTTP.responseXML.xml or objXMLHTTP.responseXML
> ' Set objXMLHTTP = New MSXML2.XMLHTTP
> ' Debug.Print "returned=" & objXMLHTTP.responseXML.xml
> ' oDOM.loadXML (objXMLHTTP.responseXML.xml)
> ' in place of line below
> oRecordset.Save oDOM, adPersistXML '1 magic number
>
> Set oXSL = CreateObject("MSXML2.DOMDocument")
> oXSL.async = False
> oXSL.Load "C:\Access files\xmlfiles\ADOGeneric.xsl" 'your XSLT
> stylesheet save as unicode not ansii
> 'note encoding as european language encoding need for swedish characters
> 'you could scan the the string and convert to unicode escape characters
> like '
> strTransform = oDOM.transformNode(oXSL)
> strHTML = "<?xml version='1.0' encoding=""ISO-8859-1""?>" & vbCrLf & _
> "<root>" & strTransform & "</root>"
> 'Save as flat xml file
> WriteFile "C:\Access files\xmlfiles\ADOGenericProduct" & Format(Now,
> "yyyymmddhhmmss") & ".xml", strHTML
> '
> 'the above XSLT transform with xsl file converts this to a flat XML
> format without rowset schema
>
> 'this will save to an XML file with ADTG MS Format suitable to dump
> back into table or recordset
> oRecordset.Save "C:\Access files\xmlfiles\ADOGenericProductADG.xml",
> adPersistXML
>
> Set oDOM = Nothing
> Set oXML = Nothing
> Set oXSL = Nothing
> Set oConnection = Nothing
> Set oRecordset = Nothing
> End Sub
> Public Sub WriteFile(ByVal sFileName As String, ByVal sContents As String)
> ' Dump XML String to File for debugging
> Dim fhFile As Integer
> fhFile = FreeFile
> ' Debug.Print "Length of string=" & Len(sContents)
> Open sFileName For Output As #fhFile
> Print #fhFile, sContents;
> Close #fhFile
> Debug.Print "Out File" & sFileName
> End Sub
>
> ADOgeneric.xsl file cut and paste and save in notepad as Unicode not Ansi
> This is XSLT file that strips all the extraneous rowset schema from ADTG
> formated xml file to create flat xml
>
> <?xml version='1.0'?>
> <xsl:stylesheet version="1.0"
> xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
> xmlns:s="uuid:BDC6E3F0-6DA3-11d1-A2A3-00AA00C14882" xmlns:z="#RowsetSchema">
> <s:Schema id="RowsetSchema"/>
> <xsl:output method="xml" omit-xml-declaration="yes" />
> <xsl:template match="/">
> <xsl:apply-templates select="//z:row"/>
> </xsl:template>
>
> <xsl:template match="z:row">
> <xsl:text disable-output-escaping="yes"><row></xsl:text>
> <xsl:for-each select="@*">
> <xsl:text disable-output-escaping="yes"><</xsl:text>
> <xsl:value-of select="name()"/>
> <xsl:text disable-output-escaping="yes">></xsl:text>
> <xsl:value-of select="."/>
> <xsl:text disable-output-escaping="yes"></</xsl:text>
> <xsl:value-of select="name()"/>
> <xsl:text disable-output-escaping="yes">></xsl:text>
> </xsl:for-each>
> <xsl:text disable-output-escaping="yes"></row></xsl:text>
> </xsl:template>
> </xsl:stylesheet>
>
>
> Francisco Tapia wrote:
>
> >Ok, so I have a webservices that returns an XML reocordset. I'd like
> >to save that recordset w/ ado to an XML file... anybody have any
> >samples?
> >
> >
> >
> >
> >
>
> --
> Marty Connelly
> Victoria, B.C.
> Canada
>
>
>
> --
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
>
--
-Francisco
http://pcthis.blogspot.com |PC news with out the jargon!
http://sqlthis.blogspot.com | Tsql and More...