Avatar of satmisha
satmisha
Flag 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.
XMLVisual Basic.NET.NET Programming

Avatar of undefined
Last Comment
satmisha

8/22/2022 - Mon
satmisha

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>
Rose Babu

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
satmisha

ASKER
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.
I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck
satmisha

ASKER
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 Babu

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.
satmisha

ASKER
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.
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
ASKER CERTIFIED SOLUTION
Rose Babu

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
satmisha

ASKER
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..
satmisha

ASKER
Very Crisp and Accurate Answer.

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

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