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

satmisha
satmisha used Ask the Experts™
on
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.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®

Author

Commented:
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>
Rose BabuSenior Team Manager

Commented:
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

Author

Commented:
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.
HTML5 and CSS3 Fundamentals

Build a website from the ground up by first learning the fundamentals of HTML5 and CSS3, the two popular programming languages used to present content online. HTML deals with fonts, colors, graphics, and hyperlinks, while CSS describes how HTML elements are to be displayed.

Author

Commented:
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.
Rose BabuSenior Team Manager

Commented:
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.

Author

Commented:
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.
Senior Team Manager
Commented:
Glad to help you... :-)

and to check valid xml means what kind of validation?

if the xml file is not well formatted (i.e., missing < or > in any tags) then xmlDoc.Load function will throw an error.

so put all the above code inside a try...catch block. if any formatted error or the XML file is not valid then you can trigger the exception code in catch block

Try

    ' Place all the above code

Catch ex As Exception

    ' trigger the function that will ensure the xml is not valid

End Try

Author

Commented:
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..

Author

Commented:
Very Crisp and Accurate Answer.

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

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

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial