Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Load XMLTextReader Data into Datatable 1

Status
Not open for further replies.

Chuksawa

Programmer
Oct 12, 2014
3
US
Hi all,
Need some assistance figuring out the code that will pass XML data from a webservice into a local database. The code I have written so far grabs the data, however I'm unable to parse and load into database.

Thanks in advance


Code:
using System;
using System.Windows.Forms;
using System.Data.SqlClient;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Xml;
using System.Data;
using System.IO;
using System.Text;
using System.Collections;
using System.Data.Sql;


public partial class _Default : System.Web.UI.Page
{
    protected void Page_Load(object sender, EventArgs e)
    {
        string connectionstring = null;
        SqlConnection connection;
        SqlCommand command = new SqlCommand();
        SqlDataAdapter adapter = new SqlDataAdapter();
        DataSet ds = new DataSet();
        DataTable dt = new DataTable("WLS_dev");
        connectionstring = "Data Source=.\\SQLEXPRESS;AttachDbFilename=C:\\Users\\Desktop\\var\\dev\\database\\AdventureWorks2008_Data.mdf;Integrated Security=True;Connect Timeout=30;User Instance=True";
        connection = new SqlConnection(connectionstring);



        XmlTextReader reader = new XmlTextReader("[URL unfurl="true"]https://www.xxxx");[/URL]
        reader.WhitespaceHandling = WhitespaceHandling.Significant;

        XmlDocument xDoc = new XmlDocument();
        xDoc.Load(reader); 

        XmlNodeReader nodeReader = new XmlNodeReader(xDoc);        
        nodeReader.Close();         
        ds.ReadXml(nodeReader); 

        //how do i parse and pass into dbase table? 
        ds.Tables.Add(dt);


    }
}
 
Cannot say with that little information.

1.) What is your datatable definition, i.e. what columns do you need?
2.) what does your xml look like?

“Knowledge is power. Information is liberating. Education is the premise of progress, in every society, in every family.” (Kofi Annan)
Oppose SOPA, PIPA, ACTA; measures to curb freedom of information under whatever name whatsoever.
 
Thanks Makeitso, much appreciated.

the xml string is fairly large but i dont need everything in it. I need the columns below but if you give a couple of examples i can complete the code -
"[Picture], [Inspector_ID], [Inspector], [Store_details], [Region], [Time], [LastName], [UserName], [DeviceDate], [Name], [Date]"
Connection string -
"Data Source=.\SQLEXPRESS;AttachDbFilename=C:\Users\NAwa\Desktop\var\dev\database\AdventureWorks2008_Data.mdf;Integrated Security=True;Connect Timeout=30;User Instance=True"


xml string -
XML:
xmlData	"<?xml version=\"1.0\" encoding=\"UTF-8\"?><CanvasResult><TotalPages>1</TotalPages><CurrentPage>1</CurrentPage><Submissions><Submission Id=\"8205588\"><Form Id=\"859095\"><Name>WLS (dev) - Download test</Name><Status>retired</Status><Version>1</Version></Form><Date>2014.10.09 17:45:37</Date><DeviceDate>2014.10.09 17:45:26</DeviceDate><UserName>cawa@xxx.com</UserName><FirstName>Cxx</FirstName><LastName>Axx</LastName><ResponseID>47ABAEED-84E3-4199-A726-F79C68AED2E3</ResponseID><Sections><Section><Name>General</Name><Screens><Screen><Name>General</Name><Responses><Response><Label>Date</Label><Value>10/09/2014</Value><Type>Date</Type></Response><Response><Label>Time</Label><Value>13:45</Value><Type>Time</Type></Response><Response><Label>Region</Label><Value>Boston</Value><Type>Value List</Type></Response><Response><Label>Store Details</Label><Value>2184 (Seekonk, MA Prj:MA2184.WWER14)</Value><Type>Value List</Type></Response><Response><Label>Inspector</Label><Value>Anderson, John</Value><Type>Value List</Type></Response><Response><Label>Inspector ID</Label><Value>2227</Value><Type>Integer</Type></Response></Responses></Screen></Screens></Section></Sections></Submission><Submission Id=\"8207670\"><Form Id=\"859233\"><Name>WLS (dev) - Download test</Name><Status>retired</Status><Version>2</Version></Form><Date>2014.10.09 19:20:07</Date><DeviceDate>2014.10.09 19:20:00</DeviceDate><UserName>cawa@apexcos.com</UserName><FirstName>Chuks</FirstName><LastName>Awa</LastName><ResponseID>48E246EA-7873-4766-91DD-425D36165632</ResponseID><Sections><Section><Name>General</Name><Screens><Screen><Name>General</Name><Responses><Response><Label>Date</Label><Value>10/09/2014</Value><Type>Date</Type></Response><Response><Label>Time</Label><Value>15:19</Value><Type>Time</Type></Response><Response><Label>Region</Label><Value>Boston</Value><Type>Value List</Type></Response><Response><Label>Store Details</Label><Value>2184 (Seekonk, MA Prj:MA2184.WWER14)</Value><Type>Value List</Type></Response><Response><Label>Inspector</Label><Value>Allen, Brian</Value><Type>Value List</Type></Response><Response><Label>Inspector ID</Label><Value>2258</Value><Type>Integer</Type></Response><Response><Label>Picture</Label><Value>509229831</Value><Type>Image Capture</Type></Response></Responses></Screen></Screens></Section></Sections></Submission></Submissions></CanvasResult>"	string

Let me know if anything else needed.
Thanks
 
Makeitso - with some help I was able to figure out the rest of the operation.
Parsing the XML string was the hardest part. Please don't spend any more effort on it; your offer was much appreciated.

Cheers!

 
Sometimes trying to explain the problem is enough to see the solution. ;-)
Glad you got it sorted!

“Knowledge is power. Information is liberating. Education is the premise of progress, in every society, in every family.” (Kofi Annan)
Oppose SOPA, PIPA, ACTA; measures to curb freedom of information under whatever name whatsoever.
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top