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("M sxml2.DOMDocume nt.4.0")
Set oCmd = CreateObject("A DODB.Command")
oCmd.ActiveConn ection = "Provider=SQLOL EDB;Data Source=(local); Initial
Catalog=TestXML ;UID=sa;Passwor d=123456"
sSQL = "<ROOT
xmlns:sql=""urn :schemas-microsoft-com:xml-sql""><sql:quer y>" & "select *
from tblTest for xml auto</sql:query></ROOT>"
oCmd.CommandTex t = sSQL
oCmd.Dialect = "{5D531CB2-E6Ed-11D2-B252-00C04F681B71}"
oCmd.Properties ("Output Stream") = oDom
oCmd.Execute , , 1024
oDom.Save "C:\temp\testdt s.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
that works well and exports a set of records to an XML file:
Dim oCmd, sSQL, oDom
Set oDom = CreateObject("M sxml2.DOMDocume nt.4.0")
Set oCmd = CreateObject("A DODB.Command")
oCmd.ActiveConn ection = "Provider=SQLOL EDB;Data Source=(local); Initial
Catalog=TestXML ;UID=sa;Passwor d=123456"
sSQL = "<ROOT
xmlns:sql=""urn :schemas-microsoft-com:xml-sql""><sql:quer y>" & "select *
from tblTest for xml auto</sql:query></ROOT>"
oCmd.CommandTex t = sSQL
oCmd.Dialect = "{5D531CB2-E6Ed-11D2-B252-00C04F681B71}"
oCmd.Properties ("Output Stream") = oDom
oCmd.Execute , , 1024
oDom.Save "C:\temp\testdt s.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
Comment