Link to home
Start Free TrialLog in
Avatar of satmisha
satmishaFlag for India

asked on

Read Nested XML then Validate and finally Insert the Data in DataTable

Hi Expert,

Need to Read Nested XML, Then Need to Validate the Data and then Insert the Data inside DataBase.

Validation like:
# Having Valid Date
# Having Numeric Value of Number Tag
# Legnth Check: State Tag Should have more then one Character

Nested XML:
<?xml version="1.0" encoding="utf-8" ?>
<!--<po:PurchaseOrder xmlns:po="http://michalk.com/XmlDOM/PO.xsd" [ccc]
 xmlns:xsd="http://www.w3.org/2001/XMLSchema" [ccc=""]
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">-->
<PurchaseOrder>
  <Number>1001</Number>
  <OrderDate>8/12/01</OrderDate>
  <BillToAddress>
    <Street>101 Main Street</Street>
    <State>NC</State>
    <Date>12/12/2012</ZipCode>
  </BillToAddress>
  <BillToAddress>
    <State>NC</State>
    <Date>12/12/2012</ZipCode>
  </BillToAddress>
  <ShipToAddress>
    <Street>101 Main Street</Street>
    <City>Charlotte</City>
    <State>NC</State>
  </ShipToAddress>
  <ShipToAddress>
    <Street>101 Main Street</Street>
    <City>Charlotte</City>
  </ShipToAddress>
</PurchaseOrder>

Looking forward to hearing from you Expert.
Avatar of satmisha
satmisha
Flag of India image

ASKER

Enclosing the XML: Please use provided XML.

<?xml version="1.0" encoding="utf-8"?>
      <reports>

    <Number>1001</Number>
    <OrderDate>8/12/01</OrderDate>
   
            <report>
                  <group>Administration</group>
                  <reportname>Portfolio Summary Reports</reportname>
                  <roles>
                        <roleid>129</roleid>
                        <roleid>124</roleid>
                  </roles>
            </report>

            <report>
                  <group>Finance</group>
                  <reportname>General Ledger Reports</reportname>
                  <roles>
                        <roleid>129</roleid>
                  </roles>
            </report>

            <report>
                  <group>Support</group>
                  <reportname>Legal Entity Reports</reportname>
                  <roles>
                        <roleid>124</roleid>
                  </roles>
            </report>
   
            <report>
                  <group>Market</group>
                  <reportname>Market Summary Reports</reportname>
                  <roles>
                        <roleid>124</roleid>
                        <roleid>130</roleid>
                  </roles>
            </report>
   
      </reports>
Hi, both xml are different other than the Number and OrderDate tags. what is your final need?

since your first xml content is not wel formatted, i modified the xml as below and attached a sample code to validate the three things you mentioned.

<?xml version="1.0" encoding="utf-8" ?>
<!--<po:PurchaseOrder xmlns:po="http://michalk.com/XmlDOM/PO.xsd" [ccc]
 xmlns:xsd="http://www.w3.org/2001/XMLSchema" [ccc=""]
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">-->
<PurchaseOrder>
  <Number>1001</Number>
  <OrderDate>8/12/01</OrderDate>
  <BillToAddress>
    <Street>101 Main Street</Street>
    <State>NC</State>
    <Date>12/12/2012</Date>
    <ZipCode></ZipCode>
  </BillToAddress>
  <BillToAddress>
    <State>NC</State>
    <Date>12/12/2012</Date>
    <ZipCode></ZipCode>
  </BillToAddress>
  <ShipToAddress>
    <Street>101 Main Street</Street>
    <City>Charlotte</City>
    <State>NC</State>
  </ShipToAddress>
  <ShipToAddress>
    <Street>101 Main Street</Street>
    <City>Charlotte</City>
  </ShipToAddress>
</PurchaseOrder>

Open in new window

To validate the necessary tags, here is a simple checking process

Dim dsPurchaseXml As New DataSet
dsPurchaseXml.ReadXml(Server.MapPath("XMLFile2.xml"))

Dim dtPurOrd As New DataTable
Dim dtBillTo As New DataTable
Dim dtShipTo As New DataTable

dtPurOrd = dsPurchaseXml.Tables(0)
dtBillTo = dsPurchaseXml.Tables(1)
dtShipTo = dsPurchaseXml.Tables(2)

Dim chkPurOrdNum As Boolean = False
Dim chkPurOrderDate As Boolean = False
If dtPurOrd.Rows.Count <> 0 Then
	If IsNumeric(dtPurOrd.Rows(0).Item("Number")) Then
		chkPurOrdNum = True
	End If

	If IsDate(dtPurOrd.Rows(0).Item("OrderDate")) Then
		chkPurOrderDate = True
	End If

End If

Dim chkBillToState As Boolean = False
Dim chkBillToDate As Boolean = False
If dtBillTo.Rows.Count <> 0 Then
	For i = 0 To dtBillTo.Rows.Count - 1

		chkBillToState = False
		chkBillToDate = False

		If dtBillTo.Rows(i).Item("State").ToString.Length > 1 Then
			chkBillToState = True
		End If

		If IsDate(dtBillTo.Rows(i).Item("Date")) Then
			chkBillToDate = True
		End If

	Next
End If

Dim chkShipToState As Boolean = False
If dtShipTo.Rows.Count <> 0 Then
	For i = 0 To dtShipTo.Rows.Count - 1

		chkShipToState = False
		If dtShipTo.Rows(i).Item("State").ToString.Length > 1 Then
			chkShipToState = True
		End If
	Next

End If

Dim ck1 = chkPurOrdNum
Dim ck2 = chkPurOrderDate
Dim ck3 = chkBillToState
Dim ck5 = chkBillToDate
Dim ck6 = chkShipToState

Open in new window

Hope you may get an idea on your process
Thanks for your quick reply.. Requesting you to please consider second XML.


Also Pls help me out in inserting that into DataTable.

Looking forward to hearing from you.
DataTable is Sql DataTable... and there is only One Sql DataTable in DataBase having following structure:
Having EmployeeTable with provided Column Information:

Number,
OrderDate,
Group,
ReportName,
RoleID

pls assist.
Hi,

Here is the complete solution for you

XMLFile2.xml

<?xml version="1.0" encoding="utf-8"?>
<reports>
  <Number>1001</Number>
  <OrderDate>8/12/01</OrderDate>
  <report>
    <group>Administration</group>
    <reportname>Portfolio Summary Reports</reportname>
    <roles>
      <roleid>129</roleid>
      <roleid>124</roleid>
    </roles>
  </report>
  <report>
    <group>Finance</group>
    <reportname>General Ledger Reports</reportname>
    <roles>
      <roleid>129</roleid>
    </roles>
  </report>
  <report>
    <group>Support</group>
    <reportname>Legal Entity Reports</reportname>
    <roles>
      <roleid>124</roleid>
    </roles>
  </report>
  <report>
    <group>Market</group>
    <reportname>Market Summary Reports</reportname>
    <roles>
      <roleid>124</roleid>
      <roleid>130</roleid>
    </roles>
  </report>
</reports>

Open in new window

Vb.NET Code Behind

Dim xmlDoc As New XmlDocument
xmlDoc.Load(Server.MapPath("XMLFile2.xml"))

Dim dtOrders As New DataTable
dtOrders.Columns.Add("Number")
dtOrders.Columns.Add("OrderDate")
dtOrders.Columns.Add("Group")
dtOrders.Columns.Add("ReportName")
dtOrders.Columns.Add("RoleID")

Dim drOrders As DataRow

Dim Number As String = ""
Dim OrderDate As String = ""

Number = xmlDoc.DocumentElement.SelectSingleNode("Number").InnerText
OrderDate = xmlDoc.DocumentElement.SelectSingleNode("OrderDate").InnerText

Dim xmlNodeList As XmlNodeList
xmlNodeList = xmlDoc.DocumentElement.GetElementsByTagName("report")

Dim group As String = ""
Dim reportname As String = ""
Dim roleid As String = ""

Dim insertSql As String = "INSERT INTO TABLE_NAME(Number, OrderDate, Group, ReportName, RoleID) VALUES "

For Each n As XmlNode In xmlNodeList

	group = n.ChildNodes(0).InnerText
	reportname = n.ChildNodes(1).InnerText

	Dim rolesNode As XmlNodeList
	rolesNode = n.LastChild.ChildNodes

	For j = 0 To rolesNode.Count - 1
		roleid = rolesNode.Item(j).InnerText

		' Format the insert sql statement
		insertSql += Environment.NewLine & " ('" & Number & "', '" & OrderDate & "', '" & group & "', '" & reportname & "', '" & roleid & "'), "

		' Add data to datatable
		drOrders = dtOrders.NewRow

		drOrders("Number") = Number
		drOrders("OrderDate") = OrderDate
		drOrders("group") = group
		drOrders("reportname") = reportname
		drOrders("roleid") = roleid

		dtOrders.Rows.Add(drOrders)

	Next

Next

Dim finalSqlStr = insertSql.ToString.Trim.TrimEnd(",")

Dim finalDtOrders As New DataTable
finalDtOrders = dtOrders

Open in new window

Output SQL statement to insert

INSERT INTO TABLE_NAME(Number,OrderDate,Group,ReportName,RoleID) VALUES 
 ('1001', '8/12/01', 'Administration', 'Portfolio Summary Reports', '129'), 
 ('1001', '8/12/01', 'Administration', 'Portfolio Summary Reports', '124'), 
 ('1001', '8/12/01', 'Finance', 'General Ledger Reports', '129'), 
 ('1001', '8/12/01', 'Support', 'Legal Entity Reports', '124'), 
 ('1001', '8/12/01', 'Market', 'Market Summary Reports', '124'), 
 ('1001', '8/12/01', 'Market', 'Market Summary Reports', '130')

Open in new window

Hope this works well for you.
Yes, You are awesome... It is working fine...Happy to see your effors...

Could You also point out how to ensure that provided XML is Valid XML or not.. through vb.net. Since I also need to ensure that provided XML is valid XML in case if it doesn't I'll trigger something..

Looking your reply.
ASKER CERTIFIED SOLUTION
Avatar of Rose Babu
Rose Babu
Flag of India image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Thanks a lot.... Glad to see your quick reply.

I am having few other doubts but not going to mention them only in this one.. so accepting your solution...

And requesting you to please help me in resolving few others.

Thanks a ton..
Very Crisp and Accurate Answer.

Glad to chat with You....Expert.....

Thanks a Ton....!!!!!!