Recommendation - SQL data -> XML -> SQL Server


I'm looking for some quick advice on how to achieve a task I've been trying to do.

The situation:

I run a script on my various SQL Servers (2000 and 2005). The script's output is an XML file. (I chose XML because the majority of the servers are on their own domains with different firewall rules and what not, so linking al servers was not an option).

I also created an XSD file (booooooooring), which I still need to link to all the tables I created in a central SQL (for now 2000) Server. So I have an x-amount of XML files in a folder, and I will need to import those one by one. So far, so good.

I chose not to go for DTS, just because I want to up my XML / .NET (2.0) skills somewhat. I fiddled around with various options for a while, just to familiarise myself a little bit. Another reason is that I'll eventuallymove to SQL 2005, and I'd prefer not spending weeks on DTS now, only to have to do all of that again in the future.

The XML file contains both elements AND attributes, by the way. I could most likely make things easier for myself by getting rid of the attributes, but I'm always up for a challenge (and it will give me just a little more useful experience).

My basic idea now:

I import the XML into a DataSet as such: 

myXmlDataDocument = new XmlDataDocument();
myXmlDataDocument.DataSet.ReadXml(new StreamReader(myXmlDocument), XmlReadMode.InferSchema);
myDataSet = myXmlDataDocument.DataSet.Copy();

 Now this might not get me my attributes just now, but i'll worry about that later on (advice welcome!). My next move would be creating a second DataSet, link it to the database, and then add the XML DataSet to the database DataSet and send it back.

However, that sounds extremely cumbersome, and I am sure I might just be bogged with a little lack of understanding.

Can anyone shed some light in this matter? Basically I have the following questions:

1) What's the proper way to grab both the attributes and the elements from an XML file that basically forms a Hierarchical dataset?

2) Is the approach I describe feasible considering this is hierarchical data? (I.e. I have Element A1, with child elements B1 and B2, which are identical regarding datatypes etc).

3) Are there easier ways? Or am I taking an approach that is too narrowminded, and will I be forced to generate dynamic SQL INSERT statements?

Any nudges are welcome. Please just some directions, and not some fully typed out source code? (I learn faster by researching myself. Just blindly copy/pasting code works at times, but when it comes to XML, XSD and what not, there's an awful lot of ways to get things done. And frankly I tried a bunch of them, and am a bit overwhelmed. (Being unable to see the trees because of the forest, as we see in Holland). Just some pointers or particular classes to check out would be very appreciated.

Thank thee :)


6/1/2006 2:10:35 PM 7181 articles. 0 followers. Follow

1 Replies

Similar Articles

[PageSpeed] 48

Are you really going to let me go my own way here? Do you realise the danger in that? What if I end up coding Pandora's box?



6/6/2006 7:43:00 AM

Similar Artilces:

reading data from xml service or xml datasource
Hi, I'm trying to figure out a way to import data (but not export to, I'm just trying to get their news section) from an external db (SqlServer 2000 or 2005) to a gridview and detailsview on my site (resides at a web host). I spoke to the organisation that owns the external db and they are willing to look into several solutions, whitout giving out their db password etc. Not suprisingly, I was reccomended using a web service. However, I believe I need to install stuff in order to enable one, and so, it won't be able for me (I guess), because of my web host's limitations. Instead, I am now t...

XMl to XML
Hello,   I retrieved an XML from the dataset which retrieves data from the table in the database. I need to present the data  in a different structure. Is XSLT the way or are there any other options. please let me know. Thanks!! Yes XSLT is a good choice to convert xml file to other forms. You can take a look at XSLT Tutorial.Sincerely,Young Fang...

XML in, XML out
Hi All (and a happy holiday to those that will get a break), I am trying to read in an XML file of addresses. I need to remove all the address data from the file where code =~ /^000/ (there are none in the example data below). I need to reproduce that data 'as is', so I need to honour the tag structure, although the order of the tags doesn't need to be honoured. I have been trying to use XML::Simple and I had a go with XML::Smart but I haven't been able to get the results I want. My best effort is below (with XML::Simple). There are a couple of differences ...

Problem loading an xml string containing xml data into XML document
 Hi all,I have a problem loading an xml string containing xml response data returned by other server(spatial server) into XML document some times becoz the returned xml response contains special chars like <, >, ', etc.Is there any way to parse this XML response string before loading into XML document. Hi Bharath, if you can't find a ready made one you can write one yourself. Parse the string that you get for the elements that it would contain and inject the CDATA element between them to allow special character. example if you have a string <childNode>This con...

ADO.NET cannot retrieve XML data on VPS server
HiI built a list view web application with ADO.NET to retrieve XML data which runs in windows 2003 server, MSSQL 2005 server, ASP.NET 2.0. When I run it in windows 2003 server (VPS server, a shared dedicated hosting), MSSQL 2005 express, ASP.NET 2.0, it cannot display the list. I can login the program, just the list view cannot be run in that configuration. I set read/write permission to all folder and xml files. xmlload file did not throw exception. But, sometimes it shows IIS 500 internal server error. How should I modify the server environment to let the program run?Please advise. I fou...

The local XML file './data/bugzilla-update.xml' cannot be created. Please make sure the web server can write in this directory.
Hi All, With bugzilla 3.0, after login with admin. There is a error message "The local XML file './data/bugzilla-update.xml' cannot be created. Please make sure the web server can write in this directory." I have try below commands. # su - apache $ cd /var/www/html/bugzilla/data $ touch bugzilla-update.xml OK. Then relogin with admin There is a error message "The local XML file './data/bugzilla-update.xml' has an invalid XML format. Please delete it and try accessing this page again." We can see the web server can write in directory "....

The local XML file './data/bugzilla-update.xml' cannot be created. Please make sure the web server can write in this directory. #2
Hi,=20 Lasse is helping me with the upgrade of bugzilla 2.22 to 3.0, and we are follwing the "Steps for Upgrading",=20 1. Sanity Check run, and all the warnings were fixed 2. backup made 3. Replace the files in our installation with the new version of bugzilla 4. Run the after installation=20 It said that some of the old templates have to be removed, after that all says o.k. Now trying to open bugzilla we get: The local XML file './data/bugzilla-update.xml' cannot be created. Please make sure the web server can write in this directory. Th...

RE: The local XML file './data/bugzilla-update.xml' cannot be created. Please make sure the web server can write in this directory.
Hi,=20 More accurate information about the mail previously sent, I misunderstood about the file (please read below). Regards /Cinthya ______________________________________________=20 From: Fabricius Lasse (Nokia-TP/Copenhagen) =20 Sent: Tuesday, July 17, 2007 11:43 AM To: Cc: Kamberi Cinthya (Nokia-TP/Copenhagen) Subject: RE: The local XML file './data/bugzilla-update.xml' cannot be created. Please make sure the web server can write in this directory. Well, The file is not in the ./data directory at all.. The directory is n...

Create more XML data in exisiting XML
Friends I have following kind of XML xyz abc dotnet some street apt 000 some city st 12345 Hedge test Trimmer 10 20 Hedge test Trimmer 10 20 I have to insert submitted ordered data in this XML and create more nodes of purchaseOrder in purchaseData element thruogh ASP.Net 2.0 What is the best way to do this?? Are there any reference articles available?? Thanks JSQUARE...

I can show xml data in repeater, but how to save changed data back to xml?
I want to be able to choose which images in a folder should be used for the AdRotator on my web site.So what I've done is created a simple web page where I can view all images in a folder. As each image is displayed using a Repeater, a checkbox is added. The image's filename is checked (using a loop) against the filenames in the AdRotator's xml file. If the image is listed in the xml file then the checkbox is ticked, so the user can see which images are currently selected to be displayed. This all works fine.I can then tick or untick the checkbox for any image depending on whether I want eac...

how to send xml and non xml data
hello, sorry if the question is stupid but i cant find anywhere the solution... i have an web app that responds by sending xml documents to clients. sometimes it is necessary to send non xml data like images along with the xml document so the client would receive them, apply any xsl transformations and present the final resoult. my question is how to send xml and non xml data? a reference like html style wont apply here... please any help or links would be very welcome! thanx Hi max_payne, max_payne:sometimes it is necessary to send non xml data like images along with...

Help with xml data into xml file
I have some xml data that comes from a web service . I want to write that xml data into xml file.  Could any one please post some samples   thanks Cemal   Please disregard, I found the solution. Try below code   FileStream fs = new FileStream(Server.MapPath("~/myxml.xml"), FileMode.OpenOrCreate, FileAccess.ReadWrite, FileShare.ReadWrite);            StreamWriter sw = new StreamWriter(fs);            sw.Write(ValueReturnFromWEbService); &nbs...

Ubuntu Install: The local XML file './data/bugzilla-update.xml' cannot be created. Please make sure the web server can write in this directory and that you can access the web. If you are behind a prox
Hello list, I found the fix for this on SELinux via but I cannot find the way to adapt its findings to my Ubuntu install. /public_html/bugzilla# uname -a Linux hfs #8 SMP Mon Sep 20 15:54:33 UTC 2010 x86_64 GNU/Linux drwxrwx--- 7 root www-data 4096 May 22 17:45 data Thanks! -- Ryan ...

AJAX: How to get xml data using XMLHTTP.responseXml where xml data processed in C# code
Hi All, I am trying t retrieve data from an xml file on server side using Ajax: How to get xml data using XMLHTTP.responseXml  where xml data processed in C#  as:- C# part xmldoc.Load(xmlfile);this.Response.ContentType= "text/xml"; this.Response.Write(xmldoc); --- AJAX- Javascript part is ---if(XMLHTTP) { XMLHTTP.onreadystatechange = function() {if(XMLHTTP.readystate==4 && XMLHTTP.status == 200){ var xmlDoc = XMLHTTP.responseXml; //********  HERE THIS IS NOT RETRIEVING THE xml data instead this brings  XMLHTTP.responseText data *...

Using POST method to post the xml data + other data to the server?
Hello, Can anyone tell me what I did wrong based on the code I have below? I want to send the xml data and other data to the server. The example of request looks something like this:  and also the data parameter for xml data.  The request should be URL-encoded and the content type of the request set to "application/x-www-form-urlencoded".  So I have the code below but gets an error where it gets the response from the web server "The remote server returned an error: (500) Internal S...

Web resources about - Recommendation - SQL data -> XML -> SQL Server -

Implementing Recommendations of the 9/11 Commission Act of 2007 - Wikipedia, the free encyclopedia
Passed the House on January 9, 2007 (299-128 Roll call vote 015 , via Reported by the joint conference committee on July 25, ...

Recommendation Platform Top10 Adds Facebook-Integrated No. 1 Button
Recommendation platform Top10 added a No. 1 button to its website, allowing users to share their top recommendations for topics of all kinds ...

Under the Hood: Building the App Center recommendation engine - Facebook
Facebook Engineering hat eine Notiz mit dem Titel Under the Hood: Building the App Center recommendation engine geschrieben. Du kannst den vollständigen ...

Introducing the Recommendations Bar
Today we're launching the Recommendations Bar, a new social plug-in that helps people find articles based on what their friends like and share ...

Facebook’s Instant Personalization Improves Suggestions on Clicker Online TV Recommendations
Today, Facebook unveils instant personalization for online television recommendations engine Clicker. When you visit the site while logged in ...

Zacks Investment Research - Proven Stock Ratings Research Recommendations
Zacks is the leading investment research firm focusing on equities earnings estimates and stock analysis for the individual investor, including ...

foursquare : With foursquare deemphasizing the checkin in v5.0, are the explore recommendations going ...
Answer (1 of 4): I guess there's another way of looking at it. They're trying to make you feel less pressured to check-in and spend more time ...

Recollect — Beautiful music recommendations for iPhone, iPod touch, and iPad on the iTunes App Store
Get Recollect — Beautiful music recommendations on the App Store. See screenshots and ratings, and read customer reviews.

Fair Work Commission must not ignore penalty rate recommendations, says Eric Abetz
Former workplace relations minister Eric Abetz says the Fair Work Commission cannot ignore calls to reduce Sunday penalty rates, if as expected ...

Turnbull government responds to Harper review into competition laws, accepts majority of recommendations ...
... retail trading hours, taxi services and road transport regulations. The Turnbull government has agreed to the vast majority of recommendations ...

Resources last updated: 12/28/2015 1:38:19 AM