VB.Net -- Bulk Insert XML into Existing SQL Server Table

Question

I recently figured out how to export data as XML from SQL Server using VB.Net. I use three SQL statements and dimension an XPath document to add the root tags to the export:

        Dim connectionString As String
        Dim connection As New SqlConnection
        Dim command As New SqlCommand()

        Dim sql = "select * from scheduledata for xml auto, elements " & _
        "select * from costdata for xml auto, elements " & _
        "select * from csintegrationdata for xml auto, elements "

        connectionString = "Data Source=(localdb)\v11.0; _ 
        Initial Catalog=localACETest;Integrated Security=True"
        connection = New SqlConnection(connectionString)
        connection.Open()

        command.CommandText = sql
        command.Connection = connection
        command.CommandType = CommandType.Text

        Dim xmlRead As XmlReader = command.ExecuteXmlReader()

        Dim xp As New XPath.XPathDocument(xmlRead)
        Dim xn As XPath.XPathNavigator = xp.CreateNavigator()
        Dim xd As New XmlDocument
        Dim root As XmlNode = xd.CreateElement("Data")
        root.InnerXml = xn.OuterXml
        xd.AppendChild(root)

        Dim fStream As New FileStream(Directory, FileMode.Create, FileAccess.ReadWrite)

        xd.Save(fStream)
        fStream.Close()

I would like to be able to re-import this data into the SQL tables using VB, but I'm having some trouble. The code I'm using is this:

        Using sqlconn As New SqlConnection(connectionString)
            Dim ds As New DataSet()
            Dim sourcedata As New DataTable()
            Dim ii As Integer = 0
            ds.ReadXml("C:\Users\coopere.COOPERE-PC\Desktop\Test.xml")
            sqlconn.Open()

            Using bulkcopy As New SqlBulkCopy(sqlconn)
                sourcedata = ds.Tables(0)

                bulkcopy.DestinationTableName = "CSIntegrationData"
                bulkcopy.ColumnMappings.Add("Period", "Period")
                bulkcopy.ColumnMappings.Add("Program", "Program")
                bulkcopy.ColumnMappings.Add("CostControlAccount", "CostControlAccount")
                ...

                bulkcopy.WriteToServer(sourcedata)
            End Using
            sqlconn.Close()
        End Using

When doing this, I get an error: System.InvalidOperationException: The given ColumnName 'CostControlAccount' does not match up with any column in data source.

I believe the reason for this is because one of the tables that exported to XML has a column full of NULL values which aren't winding up anywhere in the XML. But that won't always be the case -- oftentimes, there will be values.

Considering SqlBulkCopy's columnmappings can't handle accepting null values, how can I handle null values in my Export/Import? I'm also open to a different route entirely.

Answer 1

After much digging, I found this link.

As part of the FOR XML function you can tack on XSINIL at the end to handle null values. The resulting XML is below.

select * from TABLENAME for xml auto, elements XSINIL

<ScheduleWorkPackage xsi:nil="true" />