我应该使用什么方法从数据集(SQL)中获取3个不同的节点(XML)？

Question

我有一个DataSet (在VB.NET中)，它的列已经从一个SQL数据库中提取出来了。我的过程现在使用for-each循环为每个DataRow创建一个新的XML文件。

节点1

Burt_Order> <Insert_Order AdOrderNumber="0000222861" AoAdOrder_ID="81772" Burt_SalesRep="ENERESJ" SalesRep="ENERESJ" MailCount="0" NonSubCount="0" DealerCount="0" SubscribCount="75753200" QuantityExpected="75753200" QuantityBilled="7575300" Product="Saturation StarTribune" AoPrpRunSchedule_Notes="" AoPrpRunSchedule_ID="12333" Pagecount="2" Sides="1" Color="" AdID="12333" OrderNotes="" AoAdOrder_LastEditDate="2015-10-06 17:45:50.457" OrderCreateDate="9/29/2015 3:12:53 PM" Internalstatus="1"/>

节点2

<Insert_Order_Zone Order_Zone="55344ZX_HD"/> 
<Insert_Order_Zone Order_Zone="55342X_HD"/> 
<Insert_Order_Zone Order_Zone="55312X_HD"/>
(Note: these all would share the same AoAdOrder_ID then a different AoAdOrder_ID would give me a different XML doc with ZIPS that share that same AoAdOrder_ID)
<Insert_Order_Zone Order_Zone="57342X_HD"/> 
<Insert_Order_Zone Order_Zone="55322X_HD"/> 

节点3

<Insert_Order_Billing Price="610.47"/>

如何创建一个进程，为每个唯一的Order_Id创建单独的XML文件？

Main Process
For Each dr As DataRow In ds_AtexData.Tables("t_AtexData").Rows
                If dr.Item("PROCESSED FLAG") = 0 Then
                    Establish_OutputFile()
                    CreateXMLFileToBurt(dr)
                End If
            Next
 Public Sub CreateXMLFileToBurt(ByRef dr)
        Try
            JobLogger.Write("STARTING CreateXMLFileToBurt() ")
            'Delete the XML file if it already exists
            'If File.Exists(str_OutFileName) Then
            '    Kill(str_OutFileName)
            'End If

            xmlWSetting.Indent = True
            o_xmlWriter = XmlWriter.Create(XMLdirectory & timeofrun.Substring(0, 10).Replace("/", "") & "_" & timeofrun.Substring(11, 8).Replace(":", "") & "_" & xmlfilenumber & "_" & str_OutFileName, xmlWSetting)

            'Start writing out XML
            o_xmlWriter.WriteStartDocument()
            o_xmlWriter.WriteStartElement("Burt_Order") 'root

            For Each dsaf As DataRow In ds_AtexData.Tables("t_AtexData").Rows   'REMOVE REMOVE REMOVE REMOVE REMOVE REMOVE REMOVE REMOVE REMOVE 
            Next
            'o_xmlWriter.WriteStartDocument()
            'o_xmlWriter.WriteStartElement("Burt_Order") 'root

            o_xmlWriter.WriteStartElement("Insert_Order")
            o_xmlWriter.WriteAttributeString("Internal_status", dr.Item("Internalstatus"))
            o_xmlWriter.WriteAttributeString("Order_Create_Date", dr.Item("OrderCreateDate"))
            o_xmlWriter.WriteAttributeString("AoAdOrder_LastEditDate", dr.Item("AoAdOrder_LastEditDate"))
            o_xmlWriter.WriteAttributeString("Order_Notes", dr.Item("OrderNotes"))
            o_xmlWriter.WriteAttributeString("AdID", dr.Item("AdID"))
            'o_xmlWriter.WriteAttributeString("Color", dr.Item("Color"))
            'o_xmlWriter.WriteAttributeString("Sides", dr.Item("Sides"))
            o_xmlWriter.WriteAttributeString("Page_Count", dr.Item("Pagecount"))
            o_xmlWriter.WriteAttributeString("Page_Type", dr.item("Page_Type"))
            o_xmlWriter.WriteAttributeString("AoPrpRunSchedule_ID", dr.Item("AoPrpRunSchedule_ID"))
            o_xmlWriter.WriteAttributeString("AoPrpRunSchedule_Notes", dr.Item("AoPrpRunSchedule_Notes"))
            o_xmlWriter.WriteAttributeString("Product", dr.Item("Product"))
            o_xmlWriter.WriteAttributeString("QuantityBilled", dr.Item("QuantityBilled"))
            o_xmlWriter.WriteAttributeString("QuantityExpected", dr.Item("QuantityExpected"))
            o_xmlWriter.WriteAttributeString("SubscribCount", dr.Item("SubscribCount"))
            o_xmlWriter.WriteAttributeString("DealerCount", dr.Item("DealerCount"))
            o_xmlWriter.WriteAttributeString("NonSubCount", dr.Item("NonSubCount"))
            o_xmlWriter.WriteAttributeString("MailCount", dr.Item("MailCount"))         '                              o_xmlWriter.WriteAttributeString("", dr.item(""))
            'o_xmlWriter.WriteAttributeString("SalesRep", dr.item("SalesRep"))
            'o_xmlWriter.WriteAttributeString("Burt_SalesRep", dr.item("SalesRep"))
            o_xmlWriter.WriteAttributeString("AoAdOrder_ID", dr.item("AoAdOrder_ID"))
            o_xmlWriter.WriteAttributeString("AdOrderNumber", dr.item("AdOrderNumber"))
            o_xmlWriter.WriteAttributeString("Weight", dr.item("Weight"))
            o_xmlWriter.WriteEndElement() 'Insert_Order

            'For Each dr2 As DataRow In ds_AtexData.Tables("t_atexdata").Rows            '
            '    For Each dr3 As DataRow In ds_AtexData.Tables("t_atexdata").Rows           '
            If dr.Item("AdOrderNumber") = dr2.Item("AdOrderNumber") AndAlso dr2.Item("FLAG") = 0 Then
                o_xmlWriter.WriteStartElement("Insert_Order_Zone")
                dr2.Item("FLAG") = 1
                o_xmlWriter.WriteAttributeString("Order_Zone", dr.Item("Order_Zone"))
                o_xmlWriter.WriteEndElement() 'Insert_Order_Zone
            End If  '
                  '
            o_xmlWriter.WriteStartElement("Insert_Order_Billing")
            o_xmlWriter.WriteAttributeString("Price", dr.Item("Price"))
            o_xmlWriter.WriteAttributeString("Bill_Quantity", dr.item("QuantityBilled"))
            o_xmlWriter.WriteEndElement() 'Insert_Order_Billing

            'Next

            'ElseIf ds_AtexData.Tables("t_AtexData").Rows.Count = 0 Then
            'o_xmlWriter.WriteRaw("There are no orders to be processed at this time.")
            'End If
            o_xmlWriter.WriteEndElement() 'end root
            o_xmlWriter.WriteEndDocument()
            o_xmlWriter.Close()

            'Next

            'o_xmlWriter.Close()

            JobLogger.Write("COMPLETED CreateXMLFileToBurt() ")
            JobLogger.Write("...")
        Catch ex As Exception
            JobLogger.Write("****  ERROR ***** " & ex.ToString.Trim)
            Throw New AT_Application_Exception
        End Try
    End Sub

Answer 1

您可能需要尝试DataTable的Select，以便筛选和排序您的数据集。

(参考链接：https://msdn.microsoft.com/en-us/library/way3dy9w(v=vs.110).aspx

如果我理解您想要做的事情，那么您需要为每个AoAdOrderID编写一个单独的XML文件，并有三个节点。

如果您使用select方法并对表中想要的内容进行筛选(如“标志”=0).然后按AoAdOrderID排序，然后循环遍历行。

Dim expression As String = "FLAG = 0"
Dim sortOrder As String = "AoAdOrderID"
Dim foundRows As DataRow() = ds_AtexData.Tables("t_AtexData").Select(expression, sortorder)

在您的循环中，您将检查AoAdOrderID中的更改。如果发生更改，则关闭前一个文件并启动下一个文件。

再说一次，我不确定这是不是你想做的。如果没有，也许你的目标是什么，我会看看能不能帮上忙。