Export from mssql table to xml file (in my case 2016)
First, in access you need to enable xml library
Secondly, create a function within vba module similar to the one I made.
Option Compare Database
Option Explicit
Dim sqlCommand As ADODB.Command
Dim conn As ADODB.Connection
Dim rst As New ADODB.Recordset
Public Function ExportaXmlImportexBCM()
Set conn = cn ‘ cn is database function to establish connection to sql server,
Dim xDoc As New MSXML2.DOMDocument60
Dim schemaNode As MSXML2.IXMLDOMNode
Const adPersistXML = 1
rst.Open “SELECT * FROM importex_bonuri_consum”, conn
rst.Save xDoc, adPersistXML
With xDoc
‘Remove schema node and hence unused namespace declarations
‘First select the Schema node and then call removeChild
.SetProperty “SelectionNamespaces”, _
“xmlns:s=’uuid:BDC6E3F0-6DA3-11d1-A2A3-00AA00C14882′”
Set schemaNode = .SelectSingleNode(“//s:Schema”)
With .DocumentElement
.RemoveChild schemaNode
.removeAttribute “xmlns:z”
.removeAttribute “xmlns:rs”
.removeAttribute “xmlns:s”
.removeAttribute “xmlns:dt”
End With
‘Use MSXML DOM to save the document, change your variables to fit to your environment
.Save “\\192.168.1.100\xmls\imports\example.xml”
End With
rst.Close
End Function
Importing into mssql table from xml file with vba access (in my case 2016)
First option is using sql Bulk metod from mssql server.
I used it import for a front-end made by myself to an acconunting software called Nexus used in Romania. The table is called
Public Function ImportaBCMImportex()
Set conn = cn ‘ cn is database function to establish connection to sql server,
Dim sqlCommand As New ADODB.Command
With sqlCommand
.ActiveConnection = conn
.CommandType = adCmdText
.CommandText = “declare @xmldata XML = (SELECT CONVERT(XML, BulkColumn) AS BulkColumn FROM OPENROWSET(BULK ‘\\192.168.1.100\xmls\imports\example.xml‘, SINGLE_CLOB)as X)” _
& “;WITH XMLNAMESPACES(‘urn:schemas-microsoft-com:rowset’ AS rs, ‘#RowsetSchema’ ASz)” _
& “insert into importex_bonuri_consum (id_importex,id_document,id_carnet,serie_document,numar_document,data_document,id_gestiune,den_gestiune,id_primitor,den_primitor, ” _
& “id_centru_profit,den_centru_profit,id_beneficiar,cif_beneficiar,den_beneficiar,cont_debit,observatii,validare,valoare,nume_transport,id_transport)” _
& “SELECT Nodes.Attr.value (‘@id_importex’, ‘varchar(25)’) AS ‘id_importex’, ” _
& “Nodes.Attr.value (‘@id_document’, ‘varchar(25)’) AS ‘id_document’,” _
& “Nodes.Attr.value (‘@id_carnet’, ‘bigint’) AS ‘id_carnet’, ” _
& “Nodes.Attr.value (‘@serie_document’, ‘varchar(10)’) AS ‘serie_document’, ” _
& “Nodes.Attr.value (‘@numar_document’, ‘varchar(20)’) AS ‘numar_document’, ” _
& “Nodes.Attr.value (‘@data_document’, ‘date’) AS ‘data_document’,” _
& “Nodes.Attr.value (‘@id_gestiune’, ‘varchar(25)’) AS ‘id_gestiune’,” _
& “Nodes.Attr.value (‘@den_gestiune’, ‘varchar(30)’) AS ‘den_gestiune’,” _
& “Nodes.Attr.value (‘@id_primitor’, ‘varchar(25)’) AS ‘id_primitor’,” _
& “Nodes.Attr.value (‘@den_primitor’, ‘varchar(30)’) AS ‘den_primitor’,” _
& “Nodes.Attr.value (‘@id_centru_profit’, ‘varchar(25)’) AS ‘id_centru_profit’,” _
& “Nodes.Attr.value (‘@den_centru_profit’, ‘varchar(30)’) AS ‘den_centru_profit’,” _
& “Nodes.Attr.value (‘@id_beneficiar’, ‘varchar(25)’) AS ‘id_beneficiar’,” _
& “Nodes.Attr.value (‘@cif_beneficiar’, ‘varchar(20)’) AS ‘cif_beneficiar’,” _
& “Nodes.Attr.value (‘@den_beneficiar’, ‘varchar(100)’) AS ‘den_beneficiar’,” _
& “Nodes.Attr.value (‘@cont_debit’, ‘varchar(17)’) AS ‘cont_debit’,” _
& “Nodes.Attr.value (‘@observatii’, ‘varchar(250)’) AS ‘observatii’,” _
& “Nodes.Attr.value (‘@validare’, ‘bit’) AS ‘validare’,” _
& “Nodes.Attr.value (‘@valoare’, ‘decimal(13,2)’) AS ‘valoare’,” _
& “Nodes.Attr.value (‘@nume_transport’, ‘varchar(20)’) AS ‘nume_transport’, ” _
& “Nodes.Attr.value (‘@id_transport’, ‘varchar(25)’) AS ‘id_transport’ FROM @xmldata.nodes(‘/xml/rs:data/z:row’) AS Nodes(Attr) “
.Execute
End With
End Function
I will update the second option for restricted mssql servers which don’t allow bulk method for import.
