Posted by serge on 07/14/05 05:37

Using some VB sample code on the Internet I have the following
that works well and exports a set of records to an XML file:

Dim oCmd, sSQL, oDom

Set oDom = CreateObject("Msxml2.DOMDocument.4.0")

Set oCmd = CreateObject("ADODB.Command")
oCmd.ActiveConnection = "Provider=SQLOLEDB;Data Source=(local);Initial
Catalog=TestXML;UID=sa;Password=123456"

sSQL = "<ROOT
xmlns:sql=""urn:schemas-microsoft-com:xml-sql""><sql:query>" & "select *
from tblTest for xml auto</sql:query></ROOT>"

oCmd.CommandText = sSQL
oCmd.Dialect = "{5D531CB2-E6Ed-11D2-B252-00C04F681B71}"

oCmd.Properties("Output Stream") = oDom
oCmd.Execute , , 1024

oDom.Save "C:\temp\testdts.xml"


This is my first day of using XML in SQL Server and I need help on how
to imitate INSERT statements by basically importing data from an xml file.

With some changes I managed to write VB code that seems to be reading
the XML file I exported earlier but I can't seem to know what to do to be
able to take the data only of the XML and INSERT it into a table.
The code I used is the one found in the SQL Online Help, search in INDEX
for "OPENXML" and then choose "USING OPENXML"

If you have sample code or can point me to a link that has sample code to
achieve what I want, I would appreciate your help.

Thank you

[Back to original message]