How to load XML program into SQL table using VB (or C#)

**Laurense** · 23-01-2010

As the title suggests, I want to load a table SQL Server from data in an XML program via a VB.NET or C#. I did some research on the internet, but I do not really know how to do (especially part II: transfer to SQL) if not to parse the file I got.

**opaper** · 23-01-2010

Can you specify how you coded XML. I guess it's in a datatable. If so, you will just have to loop through all rows in your datatable and each iteration to insert a query. In short you should clear that so we can better help you.

**Laurense** · 23-01-2010

With XPath, I recover my data, but that's another problem, I can not get it all back at once, I mean the Values of Nodes. (with XPath query I have ".//" not all Nodes of all contexts). To load all the values in a DataTable I does not seem correct and I am interested in your solution. This is in fact what I seek in a first step.

**!const** · 23-01-2010

For the insert in the comics you must loop through the XML data and transfer them into comics. In fact your two problems are intimately bound to recover because the data is forcing you to go through XPath and is the data recovered by Xpath that will insert into the database so as you can not recover in full you can not do insert the entire unless you save the entire XML file in an XML column type of database.

**opaper** · 23-01-2010

I searched on XPath and that does not change the problem, depending on the scheme of your XML. So we post an example of XML parser and a snippet of what you have done that may refer.

For reading the XML file, depending on that structure, if it allows, the easiest method is ReadXml of dataset.

Once you know your XML file browse like a picture it is more difficult to make a loop to get the comics.

Questions to ask are:
- Is that the diagram of the XML file can do the thing?
- Do you have a table that can accommodate the data in your comics? Should not it be the dynamically created (not complicated to do ...)

In short show us a schematic example of your XML ...

**Laurense** · 23-01-2010

Here is my XML file:

Code:

<?xml version="1.0"?>
<ASREQUEST_CIC_ASC>
  <AS_REQUEST>
<ASREQUEST_INFO>
    <COMPANY>ABC123</COMPANY>
    <JOB_NO>111111</JOB_NO>
    <ASC_CODE>000001</ASC_CODE>
    <PRODUCT>
      <PRODUCTSHORT>
        <MODEL_CODE>AS-111</MODEL_CODE>
        <SERIAL_NO>A000B111</SERIAL_NO>
        <PURCHASE_DATE>19840413</PURCHASE_DATE>
      </PRODUCTSHORT>
      <SYMPTOM>
        <SYMPTOM1_DESC></SYMPTOM1_DESC>
        <SYMPTOM2_DESC></SYMPTOM2_DESC>
        <SYMPTOM3_DESC></SYMPTOM3_DESC>
      </SYMPTOM>
    </PRODUCT>
    <SERVICE>
      <SERVICE_TYPE></SERVICE_TYPE>
      <SERVICE_TYPE_DESC></SERVICE_TYPE_DESC>
    </SERVICE>
    <IN_OUT_WARRANTY></IN_OUT_WARRANTY>
    <REQUEST_DATE></REQUEST_DATE>
    <REQUEST_TIME></REQUEST_TIME>
    <REPAIR_ETD_DATE></REPAIR_ETD_DATE>
    <REPAIR_ETD_TIME></REPAIR_ETD_TIME>
    <TRACKING>
      <TR_STATUS></TR_STATUS>
      <TR_STATUS_DESC></TR_STATUS_DESC>
    </TRACKING>
    <TRREASON>
      <TR_REASON></TR_REASON>
      <TR_REASON_DESC></TR_REASON_DESC>
    </TRREASON>
    <INQUIRY_TEXT></INQUIRY_TEXT>
    <REF_NO></REF_NO>
</ASREQUEST_INFO>
<CONSUMER_INFO>
    <BP_NO>EMPTY</BP_NO>
    <CUSTNAME>
      <TITLE></TITLE>
      <FIRST_NAME></FIRST_NAME>
      <LAST_NAME></LAST_NAME>
    </CUSTNAME>
    <PHONE>
      <TEL_NUMBER1></TEL_NUMBER1>
      <TEL_NUMBER2></TEL_NUMBER2>
      <TEL_NUMBER3></TEL_NUMBER3>
      <FAX_NUMBER></FAX_NUMBER>
      <E_MAIL></E_MAIL>
    </PHONE>
    <ADDRESS>
      <ADDRESS_NUMBER></ADDRESS_NUMBER>
      <COUNTRY></COUNTRY>
      <REGION></REGION>
      <POST_CODE></POST_CODE>
      <CITY></CITY>
      <STREET></STREET>
      <HOUSE_NUMBER></HOUSE_NUMBER>
      <ROOM_NUMBER></ROOM_NUMBER>
      <FLOOR></FLOOR>
    </ADDRESS>
</CONSUMER_INFO>
<DEALER_INFO>
    <BP_NO>EMPTY</BP_NO>
    <CUSTNAME>
      <TITLE></TITLE>
      <FIRST_NAME></FIRST_NAME>
      <LAST_NAME></LAST_NAME>
    </CUSTNAME>
    <PHONE>
      <TEL_NUMBER1></TEL_NUMBER1>
      <TEL_NUMBER2></TEL_NUMBER2>
      <TEL_NUMBER3></TEL_NUMBER3>
      <FAX_NUMBER></FAX_NUMBER>
      <E_MAIL></E_MAIL>
    </PHONE>
    <ADDRESS>
      <ADDRESS_NUMBER></ADDRESS_NUMBER>
      <COUNTRY></COUNTRY>
      <REGION></REGION>
      <POST_CODE></POST_CODE>
      <CITY></CITY>
      <STREET></STREET>
      <HOUSE_NUMBER></HOUSE_NUMBER>
      <ROOM_NUMBER></ROOM_NUMBER>
      <FLOOR></FLOOR>
    </ADDRESS>
</DEALER_INFO>
  </AS_REQUEST>
  <AS_REQUEST>
<ASREQUEST_INFO>
    <COMPANY>EFG456</COMPANY>
    <JOB_NO>222222</JOB_NO>
    <ASC_CODE>000002</ASC_CODE>
    <PRODUCT>	
      <PRODUCTSHORT>
        <MODEL_CODE>AS-222</MODEL_CODE>
        <SERIAL_NO>A000B222</SERIAL_NO>
        <PURCHASE_DATE>19840414</PURCHASE_DATE>
      </PRODUCTSHORT>
      <SYMPTOM>
        <SYMPTOM1_DESC></SYMPTOM1_DESC>
        <SYMPTOM2_DESC></SYMPTOM2_DESC>
        <SYMPTOM3_DESC></SYMPTOM3_DESC>
      </SYMPTOM>
    </PRODUCT>
    <SERVICE>
      <SERVICE_TYPE></SERVICE_TYPE>
      <SERVICE_TYPE_DESC></SERVICE_TYPE_DESC>
    </SERVICE>
    <IN_OUT_WARRANTY></IN_OUT_WARRANTY>
    <REQUEST_DATE></REQUEST_DATE>
    <REQUEST_TIME></REQUEST_TIME>
    <REPAIR_ETD_DATE></REPAIR_ETD_DATE>
    <REPAIR_ETD_TIME></REPAIR_ETD_TIME>
    <TRACKING>
      <TR_STATUS></TR_STATUS>
      <TR_STATUS_DESC></TR_STATUS_DESC>
    </TRACKING>
    <TRREASON>
      <TR_REASON></TR_REASON>
      <TR_REASON_DESC></TR_REASON_DESC>
    </TRREASON>
    <INQUIRY_TEXT></INQUIRY_TEXT>
    <REF_NO></REF_NO>
</ASREQUEST_INFO>
<CONSUMER_INFO>
    <BP_NO>EMPTY</BP_NO>
    <CUSTNAME>
      <TITLE></TITLE>
      <FIRST_NAME></FIRST_NAME>
      <LAST_NAME></LAST_NAME>
    </CUSTNAME>
    <PHONE>
      <TEL_NUMBER1></TEL_NUMBER1>
      <TEL_NUMBER2></TEL_NUMBER2>
      <TEL_NUMBER3></TEL_NUMBER3>
      <FAX_NUMBER></FAX_NUMBER>
      <E_MAIL></E_MAIL>
    </PHONE>
    <ADDRESS>
      <ADDRESS_NUMBER></ADDRESS_NUMBER>
      <COUNTRY></COUNTRY>
      <REGION></REGION>
      <POST_CODE></POST_CODE>
      <CITY></CITY>
      <STREET></STREET>
      <HOUSE_NUMBER></HOUSE_NUMBER>
      <ROOM_NUMBER></ROOM_NUMBER>
      <FLOOR></FLOOR>
    </ADDRESS>
</CONSUMER_INFO>
<DEALER_INFO>
    <BP_NO>EMPTY</BP_NO>
    <CUSTNAME>
      <TITLE></TITLE>
      <FIRST_NAME></FIRST_NAME>
      <LAST_NAME></LAST_NAME>
    </CUSTNAME>
    <PHONE>
      <TEL_NUMBER1></TEL_NUMBER1>
      <TEL_NUMBER2></TEL_NUMBER2>
      <TEL_NUMBER3></TEL_NUMBER3>
      <FAX_NUMBER></FAX_NUMBER>
      <E_MAIL></E_MAIL>
    </PHONE>
    <ADDRESS>
      <ADDRESS_NUMBER></ADDRESS_NUMBER>
      <COUNTRY></COUNTRY>
      <REGION></REGION>
      <POST_CODE></POST_CODE>
      <CITY></CITY>
      <STREET></STREET>
      <HOUSE_NUMBER></HOUSE_NUMBER>
      <ROOM_NUMBER></ROOM_NUMBER>
      <FLOOR></FLOOR>
    </ADDRESS>
</DEALER_INFO>
  </AS_REQUEST>
</ASREQUEST_CIC_ASC>

So here I want to extract data and put it in an SQL table (its fields match those of the XML file).

Question scheme file, I do not see what it is. Otherwise what does it mean in XML?

I actually "while" trying to load the XML parser, I tried the class XPathDocument, XmlDocument, XmlTextReader, XMLReadSetting, XmlDataDocument! all classes of the Framework anything! but without the desired result.

Something that gives as result (appearance) that looks a little bit to what I seek is as follows:

Code:

Dim xmlDatadoc As New XmlDataDocument()
xmlDatadoc.DataSet.ReadXml("C:\test_1.xml")
Dim ds As New DataSet("ds_test")
ds = xmlDatadoc.DataSet
frmXmlTest.DataGridView1.DataSource = ds.DefaultViewManager
frmXmlTest.DataGridView1.DataMember = "ASREQUEST_INFO"
frmXmlTest.Show()

The problem is that I have to specify each time the parent node to retrieve information for the tree