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

You can use XSLT to transform your data into XML if you want.

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.

XSLT - Extensible Stylesheet Languange Transformation

Check this out:

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

You can also use XML DOM (Document Object Model) if you want.

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

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.

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

Select allOpen in new window

What is the error you are getting when using xsd.exe?  This tool has help me a lot in most cases in generating datasets.

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??

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.

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

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?

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 ??

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.

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.

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)

This didn't quite help me solve my problem. Nevertheless I'm giving the points for the effort Alfred1 showed.