English 中文(简体)
利用数据表将Xml数据输入数据库
原标题：loading xml data into database using datatable
  • 时间：2009-10-16 06:31:25
  •  标签：

朋友们希望大家做得很好。 我在把数据从Xml文档中从使用vb.net i.e的数据文档中保存到Sql服务器数据库时面临一个问题,即从DBNull型改为“Sting”并不有效。” 但并不总是出现错误可能基于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>

Pls. help me to overcome this problem. Thanks in advance. ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++=

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
问题回答

我并不认为这给你的法典造成问题,但是,在处理可能包含空地的数据流时,你需要谨慎地对待你的网码:

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

如果<代码>dtRow.Item(“COUNTRY”)(或你再次使用)是空的/NUL,那么所有这些电话<代码>Replace>可能在其鼻子上平。

你们需要更加仔细地运用这些方法,并检查DBNull。 价值一!

创造类似职能(在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;
}

页: 1 Trim(Replace(...)。

页: 1

时间：2009-10-16 07:59:29

您可以尝试以下储存的提纲,然后将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
时间：2009-10-16 07:11:56


上一篇：
下一篇：


相关问题
热门标签

友情链接

Allggapp Alljchome-教程家园 mvfinale.com-影视剧情大结局大全