使用datatable将xml数据加载到数据库中

Question

嗨，朋友们，希望大家一切顺利。我面临一个问题，当保存数据到sql server数据库从xml文件使用数据表使用vb.net，即"Conversion from type 'DBNull' to type 'String' is not valid"。但并不总是得到错误可能是基于xml文件。下面是我的一个xml文件。我过去常常随机生成xml文件。

<Jobs>
- <Job ID="895562" PositionID="2300056">
  <Title>Senior Network Administrator</Title> 
- <Summary>
- <![CDATA[ Join a global leader in an exciting Australia and New Zealand role. As the Leader of Technical Support you will be responsible for mentoring and managing a technical team striving to become the technical centre of excellence in the region. A hands on role, you will not only be the technical expert but you will work along side the sales team and directly contribute to the growth of the business. Th
  ]]> 
  </Summary>
  <DateActive Date="2009-10-15T19:06:00-05:00">10/15/2009</DateActive> 
  <DateExpires Date="2009-11-14T19:06:00-05:00">11/14/2009</DateExpires> 
  <DateUpdated Date="2009-10-15 19:06:00">10/15/2009</DateUpdated> 
  <Country>xxxx</Country> 
  <State>xxx</State> 
  <City>xxx</City> 
  <PostalCode>888899</PostalCode> 
  <Min Value="90000">90,000.00</Min> 
  <Max Value="100000">100,000.00</Max> 
  <Type ID="1">Per Year</Type> 
  <Currency ID="4">xxx</Currency> 
  <BuilderFields /> 
  <DisplayOptions>4</DisplayOptions> 
  <AddressType>6</AddressType> 
  </Job>
- <Job ID="84000527" PositionID="61383275">
  <Title>QA Test Analyst- Banking and Finance</Title> 
- <Summary>
- <![CDATA[ Extensive background in QA testing within the finance sector essential ISEB Testing qualification essential About our Client Our client is a leading global banking organisation Job Description In this role you will be responsible for systems integration testing, Quality Assurance and Environment support for a wide variety of technology projects. Responsibilities will include: Working closely with d
  ]]> 
  </Summary>
  <DateActive Date="2009-10-15T18:31:11-05:00">10/15/2009</DateActive> 
  <DateExpires Date="2009-11-14T20:00:35-05:00">11/14/2009</DateExpires> 
  <DateUpdated Date="2009-10-15 20:01:00">10/15/2009</DateUpdated> 
  <Country>xxx</Country> 
  <City>xxx</City> 
  <PostalCode>8888</PostalCode> 
  <Min Value="90000">90,000.00</Min> 
  <Max Value="100000">100,000.00</Max> 
  <Type ID="1">Per Year</Type> 
  <Currency ID="4">xxx</Currency> 
  <CompanyName>Michael Page International</CompanyName> 
  <BuilderFields /> 
  <DisplayOptions /> 
  <AddressType>6</AddressType> 
  </Job>
<Job ID="895562" PositionID="2300056">
  <Title>Senior Network Administrator</Title> 
- <Summary>
- <![CDATA[ Join a global leader in an exciting Australia and New Zealand role. As the Leader of Technical Support you will be responsible for mentoring and managing a technical team striving to become the technical centre of excellence in the region. A hands on role, you will not only be the technical expert but you will work along side the sales team and directly contribute to the growth of the business. Th
  ]]> 
  </Summary>
  <DateActive Date="2009-10-15T19:06:00-05:00">10/15/2009</DateActive> 
  <DateExpires Date="2009-11-14T19:06:00-05:00">11/14/2009</DateExpires> 
  <DateUpdated Date="2009-10-15 19:06:00">10/15/2009</DateUpdated> 
  <Country>xxxx</Country> 
  <State>xxx</State> 
  <City>xxx</City> 
  <PostalCode>888899</PostalCode> 
  <Min Value="90000">90,000.00</Min> 
  <Max Value="100000">100,000.00</Max> 
  <Type ID="1">Per Year</Type> 
  <Currency ID="4">xxx</Currency> 
  <BuilderFields /> 
  <DisplayOptions>4</DisplayOptions> 
  <AddressType>6</AddressType> 
  </Job>
</Jobs>

请给我。帮我克服这个问题。提前谢谢。++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++=

Protected Sub lnkbtnUpload_Click(ByVal sender As Object, ByVal e As System.EventArgs)
    Dim fid, filename As String
    fid = CType(sender.parent.controls(0).parent.parent.controls(0), TableCell).Text
    filename = fid & ".xml"


    Dim uploadDS As New DataSet
    Dim tempDT As New DataTable
    Dim i As Integer
    Dim ConeFilePath As String = Nothing

     ConeFilePath = "~/CareerOneModifiedFeeds/" & filename.ToString()
    uploadDS.ReadXml(Server.MapPath(ConeFilePath))
    tempDT = uploadDS.Tables(0).DefaultView.ToTable
    Dim dtRow As DataRow
    Dim JobCount As Integer
    Dim Count As Integer = 0

    Dim strsaltype, strsalcur As String
    Dim strMinSal, strMaxSal As String

    For i = 0 To tempDT.Rows.Count - 1
        Count = Count + 1
        dtRow = tempDT.Rows(i)
        'Code to get the check whether the job id already exists or not
        JobCount = Dr.CheckJobIDExists(UserID, Trim(dtRow.Item("JOB_ID")))

        Dim FullLoc As String = Replace(dtRow.Item("COUNTRY"), "'", "''").Trim() + "-" + Replace(dtRow.Item("STATE"), "'", "''").Trim()
        If dtRow.Item("CITY") <> "" Then
            FullLoc = FullLoc + "-" + Replace(dtRow.Item("CITY"), "'", "''").Trim()
        End If



        If JobCount = 1 Then

            '   Update the record aganist that jobid
            DR.UpdateExistingCareerOneJobs_XML(UserID, Trim(dtRow.Item("JOB_ID")), Trim(Replace(dtRow.Item("TITLE"), "'", "''")), _
            Trim(Replace(dtRow.Item("Summary"), "'", "''")), Trim(Replace(dtRow.Item("DateActive"), "'", "''")), _
            Trim(Replace(dtRow.Item("DateExpire"), "'", "''")), FullLoc, Trim(Replace(dtRow.Item("COUNTRY"), "'", "''")), _
            Trim(Replace(dtRow.Item("STATE"), "'", "''")), Trim(Replace(dtRow.Item("CITY"), "'", "''")), Trim(Replace(dtRow.Item("PostalCode"), "'", "''")), _
            Trim(Replace(dtRow.Item("BuilderFields"), "'", "''")), Trim(Replace(dtRow.Item("DisplayOptions"), "'", "''")), _
            Trim(Replace(dtRow.Item("AddressType"), "'", "''")), Trim(Replace(dtRow.Item("CompanyName"), "'", "''")), _
            Trim(Replace(dtRow.Item("PositionID"), "'", "''")), Trim(Replace(dtRow.Item("SalMin"), "'", "''")), Trim(Replace(dtRow.Item("SalMax"), "'", "''")), _
            Trim(Replace(dtRow.Item("SalType"), "'", "''")), Trim(Replace(dtRow.Item("SalCurrency"), "'", "''")), Replace(JobCat, "'", "''"), DateTime.Now())

        Else
            '   Insert record new jobid

                  Dim z As Integer = DR.InsertCareeroneJobs_XML(UserID, Trim(dtRow.Item("JOB_ID")), Trim(Replace(dtRow.Item("TITLE"), "'", "''")), _
            Trim(Replace(dtRow.Item("Summary"), "'", "''")), Trim(Replace(dtRow.Item("DateActive"), "'", "''")), _
            Trim(Replace(dtRow.Item("DateExpire"), "'", "''")), FullLoc, Trim(Replace(dtRow.Item("COUNTRY"), "'", "''")), _
            Trim(Replace(dtRow.Item("STATE"), "'", "''")), Trim(Replace(dtRow.Item("CITY"), "'", "''")), _
            Trim(dtRow.Item("PostalCode")), Trim(Replace(dtRow.Item("BuilderFields"), "'", "''")), _
            Trim(Replace(Convert.ToString(dtRow.Item("DisplayOptions")), "'", "''")), Trim(Replace(dtRow.Item("AddressType"), "'", "''")), _
            Trim(Replace(dtRow.Item("CompanyName"), "'", "''")), Trim(Replace(dtRow.Item("PositionID"), "'", "''")), Trim(strsaltype.ToString()), _
            Trim(strsalcur.ToString()), Trim(Replace(Convert.ToString(JobCat), "'", "''")), strMinSal, strMaxSal, DateTime.Now())
            '  Trim(Replace(dtRow.Item("PositionID"), "'", "''")),Trim(strsaltype.ToString()), Trim(strsalcur.ToString()), _

            Response.Write(z)
        End If

    Next i
End Sub

Answer 1

您可以尝试下面的storedproc，您可以在其中发送xml文件的xml内容并将其插入到表中。请访问此link

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE JobsBulkInsertXML @items xml

    AS
    DECLARE @hDoc int
    exec sp_xml_preparedocument @hDoc OUTPUT, @items

    Insert Into absorbentorder
    SELECT dpc, absorbentcode, quantity
    FROM OPENXML (@hDoc, '/absorbentitems',1)
    WITH (dpc char(15), absorbentcode char(15), quantity char(15)) XMLItems

    EXEC sp_xml_removedocument @hDoc


    GO

Answer 2

我不认为这是SQL代码的问题--但在处理可能包含空字段的数据行时，您需要在vb.net代码中小心：

Trim(Replace(dtRow.Item("DateExpire"), "'", "''"))
Trim(Replace(dtRow.Item("COUNTRY"), "'", "''"))

如果dtRow.Item("COUNTRY") (或您正在使用的任何一个)为空/NULL，则所有这些对Replace的调用都可能失败。

您需要更加小心地应用这些方法，并首先检查DBNull.Value！

创建一个类似这样的函数(在C#中-将其转换为VB.NET应该没有问题)：

public string ReplaceString(DataRow row, string fieldname)
{
   // first make sure the value of the field in the row is not NULL !
   if(!row.IsNull(fieldname))
   {
       // only if NOT null, do the replace
       return row[fieldname].Replace("'", "''").Trim();
   }

   // if the field is DBNull, return an empty string
   return string.Empty;
}

然后用它来代替对Trim(Replace(...))的显式调用。

Marc