• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 584
  • Last Modified:

Exporting to XML from SQL Server(2000) in vb.net (2005) using a xsd as base.

Hello.


I have a xsd file that defines the format i must use to export data to a XML file.

This XSD is provided for me by the government. (see attachment).

I have used Microsoft's xsd to create the classes based on this XSD schema. Then added the class to the project.

Now, i need to actually export the data to a XML. Using, for example, a data reader to get the data from SQL Server (2000) and then, somewhat, export it to a single xml file. This is the part i am stuck atm.

What's the best approach for doing so in vb.net. ?? Can you provide some code examples?


Thanks in advance for the help.

SAF-T-PT.XSD
0
nvivas
Asked:
nvivas
  • 11
  • 7
1 Solution
 
Alfred A.Commented:
You can use XSLT to transform your data into XML if you want.
0
 
nvivasAuthor Commented:
Hi Alfred1.

Thanks for your response. It doesnt help me much tho. I'm new to all this and don't know what you mean by using XSLT.Can you be more specific? Provide some code examples?

Thanks.
0
 
Alfred A.Commented:
XSLT - Extensible Stylesheet Languange Transformation

Check this out:

http://www.w3schools.com/xsl/

0
Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
Alfred A.Commented:
You can also use XML DOM (Document Object Model) if you want.
0
 
Alfred A.Commented:
Here is some basic stuff about XML programming in VB.NET.  DOM and XSLT is also explained.

http://www.beansoftware.com/ASP.NET-Tutorials/XML-Programming-VB.NET.aspx
0
 
Alfred A.Commented:
Oh by the way, if you will be working with datasets, you can convert the dataset information into XML text by using Dataset.WriteXML function.  You can also use the Dataset.ReadXML function to read an XML document and load it into a DOM XmlDocument.  From there you can manipulate the information you want to transform it to the desired XML.
0
 
nvivasAuthor Commented:
Hello again Alfred1.

Thanks for your responses.

I'm not interested in reading XML but writing.

The link you provide, i have already read. I can understand it and used XmlWriter class to do some  testing.
This approach will force me to implement all the tables manually. What i am looking for is some sort of approach that picks up any given class ( generated from the xsd) and generates the xml automatically.

If i use xsd executable from microsoft, with the /d parameter instead of /c it will generate the datasets for the structure that exists in the xsd file i attached.
This doesnt work very well for xsd's with the complexity of this one. So i hit a wall here.


What i am looking for is something that allows me to, for example, by using a code similar to the one in the article. Then i somewhat use the xsd to correctly format the data returned by the command. For this i thought of using classes (provided by the xsd) and use some generic tool that looked at the structure of the class and exported it to XML correctly.

The point of this is that, if someday they change the xsd, with minimal changes to the code i will be able to perform the adaptation.

I appologise for the lack of English skills.

Thanks in advance,

Protected Sub  Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
        Dim ds As New dataset
        Dim connStr As String = "database=NorthWind;Data Source=localhost;User id=sa;password=sa"
        Using conn As New SqlConnection(connStr)
            Dim command As New SqlCommand("select * from customers", conn)
            conn.Open()
            ds.DataSetName = "Customers"
            ds.Load(command.ExecuteReader(), LoadOption.OverwriteChanges, "customers")
            Response.ContentType = "text/xml"
            ds.WriteXml(Response.OutputStream)
        End Using
    End Sub

Open in new window

0
 
Alfred A.Commented:
What is the error you are getting when using xsd.exe?  This tool has help me a lot in most cases in generating datasets.
0
 
nvivasAuthor Commented:
Im not getting the error with the tool.

Its when i use the dataset provided by it that i get and error in the class declaration.

When i use this

Dim ALL As New XSDSchema.Namespace._SAF_T_PT

i get the message in PT :(rough translation) : " The same table "Address" cant be subordinated in 2 nested relations"

Should i only use

Dim auditfile As New XSDSchema.Namespace._SAF_T_PT.AuditFileDataTable

And work my way down in the nested tables from there??

0
 
Alfred A.Commented:
OK.  In Visual Studio 2008, there is an XML Schema Explorer that you can use to generate an XML.   I am not sure with VS2005, it has been awhile.

You can try the following for creating strongly typed datasets:

http://www.developer.com/db/article.php/10920_3069061_2/Using-XML-Schemas-to-Create-Strongly-Typed-DataSets.htm

Also, you can do this using XMLSpy.
0
 
Alfred A.Commented:
Based on your previous comment, yes you can.

Oh, and by the way, why not use xsd /c option?

Check the following:

http://www.eggheadcafe.com/community/aspnet/7/10071371/generate-xml-file-using-x.aspx

in the site mentioned above, have a try of this (this is just ugly :-)).

  - use xsd.exe to generate classes for the specified W3C XML Schema
  - compile the resulting source code into an assembly
  - use reflection to instantiate and fill an instance of the first type defined in that assembly
- use XML Serialization to serialize the instance, and produce an XML doc that conforms to the XSD
check out this link for complete code: http://cheeso.members.winisp.net/srcview.aspx?dir=xml-serialization&file=Xsd2xml.cs
0
 
nvivasAuthor Commented:
Hey Alfred1

Take a look at the xsd that i provided.


Imagine i want to generate a xml based on the MasterFiles  definition. It has generalledger,Customer,Supplier,Product,TaxTable.

Admiting im using the dataset class generated by the exec tool, how would you do that in code?

I would start with    

 Dim masterfile As New XSDSchema.Namespace._SAF_T_PT.MasterFilesDataTable


and end with

masterfile.writexml("test.xml")

 
How do i populate, say, customers in the masterfile?
0
 
nvivasAuthor Commented:
Looking @ then links you provided i've found something very interesting ( i think!)

in
http://www.liquid-technologies.com/XmlDataBinding/Xml-Schema-To-VBNet.aspx

You can see:

 1Dim p As New Person()
  2p.Name = "Fred"
  3p.DateOfBirth = New XmlDateTime(1978, 6, 26)
  4
  5Dim a As New Address()
  6p.Address = a
  7p.Address.HouseNo = 7
  8p.Address.PostCode = "WV6 6JY"
  9
 10Dim runAroundCar As New Car()
 11p.Cars.Add(runAroundCar)
 12runAroundCar.Model = "Ford"
 13runAroundCar.Make = "Escort"
 14
 15Dim toyCar As New Car()
 16p.Cars.Add(toyCar)
 17toyCar.Model = "Lotus"
 18toyCar.Make = "Elise"
 19
 20Debug.WriteLine(p.ToXml())
 21
 22p.ToXmlFile("SampleFile.xml")



That's something more(very) close to what i'm looking for.

Can i do that with just VS2005, xsd.exe and MS SQL Server 2000 ??
0
 
Alfred A.Commented:
Well, there are a lot of ways to approach this.  If you use the System.Xml framework and use Dataset, XML Document Object Model, XPath and XMLWriter, your code would be large but if you design it to do it in small pieces let say put Customer to Customer.vb class and handle customer there, until all the leaves of the XML are done and then connect all of them.  This would be manageable.

I experienced doing this approach before and it works just fine.  Like I said, there are a lot of ways to do it.  The pros of this approach is a bit convenience because properties or methods are available to help you creating the XML.  The cons is that there is a slight performance penalty hit.

I hope this helps.
0
 
Alfred A.Commented:
Answering your comment ID:31278777, my previous comment before is actually what I was talking about. :-)

Yes, I believe you can still use that approach in VS2005 based on what I did with a project similar to what you have.
0
 
Alfred A.Commented:
By the way, a bit of warning,  the only bad thing with that approach is that it might make your code really big especially if you have a large XML Schema.

Just make sure you isolate the elements/tables in their own respective classes so that they are manageable.
0
 
nvivasAuthor Commented:
Damnit. You are replying toooo fast!! :D

For me to do that, i would need to buy the Liquid software..... Is there a way to produce similar thing using the classes provided by the xsd.exe ??

Do you have any code example from one of your projects?

Take the post nº31277755 example. How would you complete it for it to work???

(i have a meeting now - will come back tomorrow... thanks so much for your effort)


0
 
nvivasAuthor Commented:
This didn't quite help me solve my problem. Nevertheless I'm giving the points for the effort Alfred1 showed.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

  • 11
  • 7
Tackle projects and never again get stuck behind a technical roadblock.
Join Now