• Vui lòng đọc nội qui diễn đàn để tránh bị xóa bài viết
  • Tìm kiếm trước khi đặt câu hỏi

Import dữ liệu xml vào SQL server bằng VB.Net

Nơi trao đổi về các vấn đề trong lập trình Windows Forms, Console dùng cú pháp ngôn ngữ Visual Basic .NET và C#

Điều hành viên: tungblt, vuathongtin

Leo881991
Bài viết: 3
Ngày tham gia: T.Ba 28/02/2017 4:43 pm

Import dữ liệu xml vào SQL server bằng VB.Net

Gửi bàigửi bởi Leo881991 » T.Tư 15/03/2017 1:50 pm

Hi cả nhà!
Mình mới tìm hiểu về mảng VB.NET, có 1 vấn đề nhỏ cần sự "chỉ giáo" của mọi người.
Mình đang muốn import 1 file .xml với nội dung như ví dụ bên dưới vào 1 table trong SQL server sử dụng VB.NET.
Các dữ liệu mình muốn lấy bao gồm:

"Key, Name, StartTime, EndTime" trong Nodelist1: /Report/Result/Machines/Machine/Recipes/Recipe
"PartName, Stage, ..., SuccessRate, UnitName" trong Nodelist 2: /Report/Result/Machines/Machine/Recipes/Recipe/Items/Item

Nhưng khi run code, nó không import dữ liệu của toàn bộ các cụm <Item>...</Item>, mà chỉ import dữ liệu của các cụm liền sau nodelist1.
Ví dụ, trong 1 cụm <Recipe>...</Recipe>, mình có 3 cụm <Item>...</Item>. khi import, phần mềm chỉ cập nhật dữ liệu của cụm <Item></Item> đầu tiên. 2 cụm sau không được cập nhật.
Đã thử sử dụng for...next nhưng kết quả vẫn vậy


Có anh/bạn nào "is professor" về vấn đề import xml, có thể chia sẻ, giúp đỡ mình 1 chút không nhỉ...? :D


Nội dung file xml:

<?xml version="1.0" encoding="UTF-8" ?>
<?xml-stylesheet type="text/xsl" href="PartUsage.XSL" ?>
<Report>
<Infomation>
<Title>Part usage</Title>
<CreateDate>2017/01/10 17:14:47</CreateDate>
<CreateUser>ldtho</CreateUser>
</Infomation>
<Search>
<FromDate>2017/01/03 06:00:00</FromDate>
<ToDate>2017/01/10 06:00:00</ToDate>
<LineName></LineName>
<MachineName></MachineName>
<Recipes>
</Recipes>
<Option>0</Option>
<ReportType>Type-A</ReportType>
</Search>
<Result>
<Machines>
<Machine>
<Name>S10_2 (Program-A)</Name>
<Recipes>
<Recipe>
<Key>1835</Key>
<Name>S10_ROME_4SZ_10G_848_864_016_045_046_171_120_TV01</Name>
<StartTime>2017/01/05 12:38:13</StartTime>
<EndTime>2017/01/10 06:00:00</EndTime>
<Items>
<Item>
<PartName>9503083</PartName>
<Stage>4</Stage>
<GroupKey>0</GroupKey>
<Pos>10</Pos>
<SubPos>0</SubPos>
<Class>Tape</Class>
<PickupCount>23443</PickupCount>
<TotalPartsUsed>23417</TotalPartsUsed>
<RejectParts>0</RejectParts>
<PickupMiss>26</PickupMiss>
<ErrorParts>5</ErrorParts>
<DislodgedParts>0</DislodgedParts>
<DislodgedRate>0.000</DislodgedRate>
<RescanCount>0</RescanCount>
<PickupRate>100.000</PickupRate>
<RejectRate>0.000</RejectRate>
<ErrorRate>0.021</ErrorRate>
<SuccessRate>99.979</SuccessRate>
<UnitName>DP</UnitName>
</Item>
<Item>
<PartName>9503084</PartName>
<Stage>3</Stage>
<GroupKey>0</GroupKey>
<Pos>10</Pos>
<SubPos>0</SubPos>
<Class>Tape</Class>
<PickupCount>23416</PickupCount>
<TotalPartsUsed>23414</TotalPartsUsed>
<RejectParts>0</RejectParts>
<PickupMiss>2</PickupMiss>
<ErrorParts>2</ErrorParts>
<DislodgedParts>0</DislodgedParts>
<DislodgedRate>0.000</DislodgedRate>
<RescanCount>0</RescanCount>
<PickupRate>100.000</PickupRate>
<RejectRate>0.000</RejectRate>
<ErrorRate>0.009</ErrorRate>
<SuccessRate>99.991</SuccessRate>
<UnitName>DP</UnitName>
</Item>
<Item>
<PartName>54699C4</PartName>
<Stage>1</Stage>
<GroupKey>0</GroupKey>
<Pos>8</Pos>
<SubPos>0</SubPos>
<Class>Tape</Class>
<PickupCount>29294</PickupCount>
<TotalPartsUsed>29267</TotalPartsUsed>
<RejectParts>0</RejectParts>
<PickupMiss>27</PickupMiss>
<ErrorParts>2</ErrorParts>
<DislodgedParts>0</DislodgedParts>
<DislodgedRate>0.000</DislodgedRate>
<RescanCount>0</RescanCount>
<PickupRate>100.000</PickupRate>
<RejectRate>0.000</RejectRate>
<ErrorRate>0.007</ErrorRate>
<SuccessRate>99.993</SuccessRate>
<UnitName>DP</UnitName>
</Item>
</Items>
</Recipe>
</Recipes>
</Machine>
<Machine>
<Name>S10_2 (Program-B)</Name>
<Recipes>
<Recipe>
<Key>1681</Key>
<Name>S10_ROME_4SZ_10G_848_864_016_045_046_171_120_TV01</Name>
<StartTime>2017/01/05 12:38:13</StartTime>
<EndTime>2017/01/10 06:00:00</EndTime>
<Items>
<Item>
<PartName></PartName>
<Stage>0</Stage>
<GroupKey>0</GroupKey>
<Pos>0</Pos>
<SubPos>0</SubPos>
<Class></Class>
<PickupCount>0</PickupCount>
<TotalPartsUsed>0</TotalPartsUsed>
<RejectParts>0</RejectParts>
<PickupMiss>0</PickupMiss>
<ErrorParts>0</ErrorParts>
<DislodgedParts>0</DislodgedParts>
<DislodgedRate>0.000</DislodgedRate>
<RescanCount>0</RescanCount>
<PickupRate>0.000</PickupRate>
<RejectRate>0.000</RejectRate>
<ErrorRate>0.000</ErrorRate>
<SuccessRate>0.000</SuccessRate>
<UnitName>0</UnitName>
</Item>
</Items>
</Recipe>
<Recipe>
<Key>1680</Key>
<Name>COPY(1)-S10_ROME_4SZ_10G_848_864_016_045_046_171_1_TV01</Name>
<StartTime>2017/01/03 06:00:00</StartTime>
<EndTime>2017/01/05 12:38:13</EndTime>
<Items>
<Item>
<PartName></PartName>
<Stage>0</Stage>
<GroupKey>0</GroupKey>
<Pos>0</Pos>
<SubPos>0</SubPos>
<Class></Class>
<PickupCount>0</PickupCount>
<TotalPartsUsed>0</TotalPartsUsed>
<RejectParts>0</RejectParts>
<PickupMiss>0</PickupMiss>
<ErrorParts>0</ErrorParts>
<DislodgedParts>0</DislodgedParts>
<DislodgedRate>0.000</DislodgedRate>
<RescanCount>0</RescanCount>
<PickupRate>0.000</PickupRate>
<RejectRate>0.000</RejectRate>
<ErrorRate>0.000</ErrorRate>
<SuccessRate>0.000</SuccessRate>
<UnitName>0</UnitName>
</Item>
</Items>
</Recipe>
</Recipes>
</Machine>
<Machine>
<Name>S10_1 (Program-A)</Name>
<Recipes>
<Recipe>
<Key>318</Key>
<Name>S10_ROME_4SZ_10G_848_864_016_045_046_171_120_TV01</Name>
<StartTime>2017/01/03 06:00:00</StartTime>
<EndTime>2017/01/10 06:00:00</EndTime>
<Items>
<Item>
<PartName>4355612</PartName>
<Stage>32</Stage>
<GroupKey>0</GroupKey>
<Pos>4</Pos>
<SubPos>0</SubPos>
<Class>Tape</Class>
<PickupCount>23473</PickupCount>
<TotalPartsUsed>23472</TotalPartsUsed>
<RejectParts>0</RejectParts>
<PickupMiss>1</PickupMiss>
<ErrorParts>8</ErrorParts>
<DislodgedParts>0</DislodgedParts>
<DislodgedRate>0.000</DislodgedRate>
<RescanCount>0</RescanCount>
<PickupRate>100.000</PickupRate>
<RejectRate>0.000</RejectRate>
<ErrorRate>0.034</ErrorRate>
<SuccessRate>99.966</SuccessRate>
<UnitName>DP</UnitName>
</Item>
<Item>
<PartName></PartName>
<Stage>0</Stage>
<GroupKey>0</GroupKey>
<Pos>0</Pos>
<SubPos>0</SubPos>
<Class></Class>
<PickupCount>0</PickupCount>
<TotalPartsUsed>0</TotalPartsUsed>
<RejectParts>0</RejectParts>
<PickupMiss>0</PickupMiss>
<ErrorParts>0</ErrorParts>
<DislodgedParts>0</DislodgedParts>
<DislodgedRate>0.000</DislodgedRate>
<RescanCount>0</RescanCount>
<PickupRate>0.000</PickupRate>
<RejectRate>0.000</RejectRate>
<ErrorRate>0.000</ErrorRate>
<SuccessRate>0.000</SuccessRate>
<UnitName>0</UnitName>
</Item>
</Items>
</Recipe>
<Recipe>
<Key>264</Key>
<Name>S9_Crystal_4MZ_40A_261_146_148_150_152_154_191_193_TV01</Name>
<StartTime>2017/01/04 17:58:15</StartTime>
<EndTime>2017/01/04 22:27:28</EndTime>
<Items>
<Item>
<PartName></PartName>
<Stage>0</Stage>
<GroupKey>0</GroupKey>
<Pos>0</Pos>
<SubPos>0</SubPos>
<Class></Class>
<PickupCount>0</PickupCount>
<TotalPartsUsed>0</TotalPartsUsed>
<RejectParts>0</RejectParts>
<PickupMiss>0</PickupMiss>
<ErrorParts>0</ErrorParts>
<DislodgedParts>0</DislodgedParts>
<DislodgedRate>0.000</DislodgedRate>
<RescanCount>0</RescanCount>
<PickupRate>0.000</PickupRate>
<RejectRate>0.000</RejectRate>
<ErrorRate>0.000</ErrorRate>
<SuccessRate>0.000</SuccessRate>
<UnitName>0</UnitName>
</Item>
</Items>
</Recipe>
<Recipe>
<Key>263</Key>
<Name>S9_Crystal_4MY_40A_145_147_149_151_153_155_TV01</Name>
<StartTime>2017/01/04 05:35:49</StartTime>
<EndTime>2017/01/04 17:58:15</EndTime>
<Items>
<Item>
<PartName></PartName>
<Stage>0</Stage>
<GroupKey>0</GroupKey>
<Pos>0</Pos>
<SubPos>0</SubPos>
<Class></Class>
<PickupCount>0</PickupCount>
<TotalPartsUsed>0</TotalPartsUsed>
<RejectParts>0</RejectParts>
<PickupMiss>0</PickupMiss>
<ErrorParts>0</ErrorParts>
<DislodgedParts>0</DislodgedParts>
<DislodgedRate>0.000</DislodgedRate>
<RescanCount>0</RescanCount>
<PickupRate>0.000</PickupRate>
<RejectRate>0.000</RejectRate>
<ErrorRate>0.000</ErrorRate>
<SuccessRate>0.000</SuccessRate>
<UnitName>0</UnitName>
</Item>
</Items>
</Recipe>
<Recipe>
<Key>262</Key>
<Name>S9_Crystal_4MZ_40A_261_146_148_150_152_154_191_193_TV01</Name>
<StartTime>2017/01/03 06:00:00</StartTime>
<EndTime>2017/01/04 05:35:49</EndTime>
<Items>
<Item>
<PartName></PartName>
<Stage>0</Stage>
<GroupKey>0</GroupKey>
<Pos>0</Pos>
<SubPos>0</SubPos>
<Class></Class>
<PickupCount>0</PickupCount>
<TotalPartsUsed>0</TotalPartsUsed>
<RejectParts>0</RejectParts>
<PickupMiss>0</PickupMiss>
<ErrorParts>0</ErrorParts>
<DislodgedParts>0</DislodgedParts>
<DislodgedRate>0.000</DislodgedRate>
<RescanCount>0</RescanCount>
<PickupRate>0.000</PickupRate>
<RejectRate>0.000</RejectRate>
<ErrorRate>0.000</ErrorRate>
<SuccessRate>0.000</SuccessRate>
<UnitName>0</UnitName>
</Item>
</Items>
</Recipe>
</Recipes>
</Machine>
</Machines>
</Result>
</Report>



Leo881991
Bài viết: 3
Ngày tham gia: T.Ba 28/02/2017 4:43 pm

Re: Import dữ liệu xml vào SQL server bằng VB.Net

Gửi bàigửi bởi Leo881991 » T.Tư 15/03/2017 11:25 pm

Đây là đoạn code mình làm thử để import :) Có bạn nào phát hiện ra vấn đề gì không nhỉ? :D

Dim connetionString As String
Dim connection As SqlConnection
Dim command As SqlCommand
Dim adpter As New SqlDataAdapter
Dim ds As New DataSet()
Dim sql As String

Dim m_xmld As XmlDocument
Dim m_nodelist1 As XmlNodeList

m_xmld = New XmlDocument()
m_xmld.Load("C:\Users\quangu\Desktop\Other\Products1.xml")
m_nodelist1 = m_xmld.SelectNodes("/Report/Result/Machines/Machine/Recipes/Recipe")
For Each m_node1 In m_nodelist1
Dim Key1 = m_node1.SelectSingleNode("Key").InnerText
Dim Name = m_node1.SelectSingleNode("Name").InnerText
Dim StartTime = m_node1.SelectSingleNode("StartTime").InnerText
Dim EndTime = m_node1.SelectSingleNode("EndTime").InnerText

Dim PartName = m_node1.SelectSingleNode("Items/Item/PartName").InnerText
Dim Stage = Convert.ToInt32(m_node1.SelectSingleNode("Items/Item/Stage").InnerText)
Dim GroupKey = m_node1.SelectSingleNode("Items/Item/GroupKey").InnerText
Dim Pos = Convert.ToInt32(m_node1.SelectSingleNode("Items/Item/Pos").InnerText)
Dim SubPos = Convert.ToInt32(m_node1.SelectSingleNode("Items/Item/SubPos").InnerText)
Dim [Class] = m_node1.SelectSingleNode("Items/Item/Class").InnerText
Dim PickupCount = Convert.ToInt32(m_node1.SelectSingleNode("Items/Item/PickupCount").InnerText)
Dim TotalPartsUsed = Convert.ToInt32(m_node1.SelectSingleNode("Items/Item/TotalPartsUsed").InnerText)
Dim RejectParts = Convert.ToInt32(m_node1.SelectSingleNode("Items/Item/RejectParts").InnerText)
Dim PickupMiss = Convert.ToInt32(m_node1.SelectSingleNode("Items/Item/PickupMiss").InnerText)
Dim ErrorParts = Convert.ToInt32(m_node1.SelectSingleNode("Items/Item/ErrorParts").InnerText)
Dim DislodgedParts = Convert.ToInt32(m_node1.SelectSingleNode("Items/Item/DislodgedParts").InnerText)
Dim DislodgedRate = m_node1.SelectSingleNode("Items/Item/DislodgedRate").InnerText
Dim RescanCount = m_node1.SelectSingleNode("Items/Item/RescanCount").InnerText
Dim PickupRate = m_node1.SelectSingleNode("Items/Item/PickupRate").InnerText
Dim ErrorRate = m_node1.SelectSingleNode("Items/Item/ErrorRate").InnerText
Dim SuccessRate = m_node1.SelectSingleNode("Items/Item/SuccessRate").InnerText
Dim UnitName = m_node1.SelectSingleNode("Items/Item/UnitName").InnerText

connetionString = "Data Source=.;Initial Catalog=demo;Integrated Security=True"
connection = New SqlConnection(connetionString)
ds.ReadXml("C:\Users\quangu\Desktop\Other\Products1.xml")
connection.Open()

Title = ds.Tables(0).Rows(0).Item(0)
CreateUser = ds.Tables(0).Rows(0).Item(0)
sql = "Insert into ProductImport5 values('" & Key1 & "', '" & name & "', '" & StartTime & "', '" & EndTime & "', '" & PartName & "', " & Stage & ", '" & GroupKey & "', " & Pos & ", " & SubPos & ", '" & [Class] & "', " & PickupCount & ", " & TotalPartsUsed & ", " & RejectParts & ", " & PickupMiss & ", " & ErrorParts & ", " & DislodgedParts & ", '" & DislodgedRate & "', '" & RescanCount & "', '" & PickupRate & "', '" & ErrorRate & "', '" & SuccessRate & "', '" & UnitName & "')"

command = New SqlCommand(sql, connection)
adpter.InsertCommand = command
adpter.InsertCommand.ExecuteNonQuery()

Next

FlyingFox
Guru
Guru
Bài viết: 766
Ngày tham gia: T.Tư 20/04/2011 9:56 am
Been thanked: 310 time

Re: Import dữ liệu xml vào SQL server bằng VB.Net

Gửi bàigửi bởi FlyingFox » CN 19/03/2017 11:30 am

Có thể viết như sau:
  1. Private Sub ImportData()
  2.         Dim xmlFileName = "C:\Users\quangu\Desktop\Other\Products1.xml"
  3.         Dim connetionString = "Data Source=.;Initial Catalog=demo;Integrated Security=True"
  4.         Dim connection As New SqlConnection(connetionString)
  5.         Dim ds = New DataSet()
  6.         Try
  7.             ds.ReadXml(xmlFileName)
  8.             Dim dtRecipe As DataTable = ds.Tables("Recipe")
  9.             Dim rel1 As DataRelation = dtRecipe.ChildRelations(0)
  10.             Dim dtRelation As DataTable = dtRecipe.ChildRelations(0).ChildTable
  11.             Dim rel2 As DataRelation = dtRelation.ChildRelations(0)
  12.             connection.Open()
  13.             Dim cmd As SqlCommand = connection.CreateCommand()
  14.             For Each r1 As DataRow In dtRecipe.Rows
  15.                 Dim strKey As String = r1("Key").ToString()
  16.                 If String.IsNullOrEmpty(strKey) Then Continue For
  17.                 Dim ar1 As DataRow() = r1.GetChildRows(rel1)
  18.                 For Each r2 As DataRow In ar1
  19.                     Dim ar2 As DataRow() = r2.GetChildRows(rel2)
  20.                     For Each r3 As DataRow In ar2
  21.                         Dim strPartName As String = r3("PartName").ToString()
  22.                         If String.IsNullOrEmpty(strPartName) Then Continue For
  23.                         Dim sql As String = "Insert into ProductImport5 values('" & strKey & "', '" & r1("Name") & "', '" & r1("StartTime") & "', '" & r1("EndTime") & "', '" & r3("PartName") & "', " & r3("Stage") & ", '" & r3("GroupKey") & "', " & r3("Pos") & ", " & r3("SubPos") & ", '" & r3("Class") & "', " & r3("PickupCount") & ", " & r3("TotalPartsUsed") & ", " & r3("RejectParts") & ", " & r3("PickupMiss") & ", " & r3("ErrorParts") & ", " & r3("DislodgedParts") & ", '" & r3("DislodgedRate") & "', '" & r3("RescanCount") & "', '" & r3("PickupRate") & "', '" & r3("ErrorRate") & "', '" & r3("SuccessRate") & "', '" & r3("UnitName") & "')"
  24.                         cmd.CommandText = sql
  25.                         cmd.ExecuteNonQuery()
  26.                     Next
  27.                 Next
  28.             Next
  29.         Catch ex As Exception
  30.             MessageBox.Show(ex.Message)
  31.         Finally
  32.             If connection IsNot Nothing Then connection.Close()
  33.         End Try
  34.     End Sub

Leo881991
Bài viết: 3
Ngày tham gia: T.Ba 28/02/2017 4:43 pm

Re: Import dữ liệu xml vào SQL server bằng VB.Net

Gửi bàigửi bởi Leo881991 » T.Ba 30/05/2017 10:51 pm

Rất Thank bác FlyingFox.
Ngắn gọn mà hiệu quả quá... :) phải học hỏi các bác nhiều... :D


Quay về “Visual Basic .NET và C# (VB.NET & C#)”

Đang trực tuyến

Đang xem chuyên mục này: Không có thành viên nào trực tuyến.7 khách