Importing XML data into SQL Server table.

[jrbrady]

I just converted an Access 2002 application into VB.NET
(don't ask). VBA in Access has a nifty command for
importing XML into an Access table called, unsurprisingly
ImportXML(). It works like this: Application.ImportXML
([XML Filename]). The table name is a field in the .xml
file.

So, my question is, how do I do this in VB.NET using
ADO.NET into SQL Server? There's a DataSet command called
ReadXml that (supposedly) loads a DataSet with data from
a .xml file. But how do I save that data in my SQL Server
database?

I've tried this:

Dim da As DataAdapter = New DataAdapter("SELECT * FROM
[tblName]", sqlDBConn)
Dim ds As New DataSet
ds.ReadXml([XML Filename])
da.Update(ds)

....to no avail. And I've tried:

Dim da As DataAdapter = New DataAdapter("SELECT * FROM
[tblName]", sqlDBConn)
Dim ds As New DataSet
Dim dt As DataTable = ds.Tables.Add"([tblName]")
ds.ReadXml([XML Filename])
da.Update(ds)

....also to no avail. Please help. Thank you.

 

[Cowboy \(Gregory A. Beamer\)]

The XML format for both the DataSet and SQL Server are different from Access
XML format. If the XML is formatted for Access, you will have to transform.

Provided the XML is formatted correctly, you can place in a DataSet and use
Update on the DataAdapter. To check format.

1. Save your XML (the one you wish to import)
2. Run your DataAdapter command and then save the XML from the DataSet to a
disk

Compare the two.

Next, examine the DataSet created from the XML. The best way:

1. Create a DataSet
2. Read in the XML
3. Save the XML schema

Look at the XSD file created in Visual Studio .NET. You can compare this to
the XML schema from your SELECT command by either a) saving that schema or
b) dragging the table from Server Explorer onto an XSD file (Add Item >>
DataSet).

--
Gregory A. Beamer
MVP; MCP: +I, SE, SD, DBA

**********************************************************************
Think Outside the Box!
**********************************************************************