Video: How Do I: Create XML Documents from SQL Data?

Presenter:Beth Massi


Visual Basic developers will learn how to query their SQL Server database in order to easily create XML and HTML documents directly in the Visual Basic editor.

Click here to go to original video page


Minute 0
My name is Beth Massey into going to show you that you can create XML documents from data in your database in Visual Basic using blank stands for language integrated query and it allows us to write queries over things like objects databases and XML in a standard way Visual Basic provides deep support for XML through what s called XML literals this feature allows you to use familiar convenient syntax for working with XML in your Visual Basic code we can query a database to link to SQL which is built on top of 80 of net and provides the plumbing for managing relational data in a database as actual objects without losing the ability to query easily and previous videos I showed you how to use the all our designer to create your thing to SQL classes and greater data I also showed you how to work with XML literals in Visual Basic in this video we re going to put these two concepts together in order to easily create XML for data stored in a database
Minute 1
S2 to first_and you create a him consul application in Visual Studio 2008 which I have open now some aesthetic of a new project and ridges can a name that s create XML think so the first thing I wanted you to add is on the right click and added a new item in this item is going to be a link to sequels class link to SQL classes and I m just going to name this Northland is we re going to ask and tables from our Northwind database so this opens the oh are designer which is a new designer in Visual Studio 2008_into expand my server Explorer and I have a data connection already to Northwind database just expanded tables here and I m in a drag customers order details
Minute 2
Orders and products onto the design surface and this will create a link to SQL classes this allows us to query a database is just an echo of her here turn module and ones that write some code that will create an XML document of all of the customers in a customer table peso first I miss going to an declared data context here a case that will get access to the records in a database now I can do they showed in previous videos is I can actually type XML directly into the editor here tanks on the build up a document with the Buddhist are you customers and then going to open up and it embedded expression with the last 10 tax and this will allow me to write a link query so it s pretty easy and is going to say from customer and he
Minute 3
You got customers can easily get IntelliSense on that because that s what the link to SQL classes generated for us select and now I can start writing ability of this document so what I want to do is I want to have a customer element K and within the customer element and one half name in the open up another expression here because you can nest fees and we can get back VB code and I can say customer Company name K Bennett can do am a nether elements in this ways you can see better called phone Kenny and then it will open up another embedded expression customer phone as you can see here or I m just creating at this XML document from the top down its very logical to me and redoing that based on data that s inside of our database and it s very simple
Minute 4
Customer country for instance okay so this is simple document that s going to basically just dump out all the customers inside of our Northwind database so just to say customers not say if y can save directly for its ex element and ask documents have a method called safe and we can just don t does not cease AC Drive and I want to open it right away okay so let s see what this does is hit F5 this opens up Internet Explorer for mean in this XML document as you can see it s built up this document with all of the customers and our Northwind database so that
Minute 5
Easy it is to create XML for data in your database so let s look at some different examples to let say instead of an XML document I will ask the build up an HTML document we can do that to sell a way to do is change how this works dim customers equals and now I can start declaring HTML case somebody that diminishes open up the body tag seeded build up any kind of document here so table a list them all inside of a table just isn t like this and then I can go ahead and do row and let s do a savings of student named phoning country and so
Minute 6
TD will make this on the header row of bold print since and then steam name just take this and will put three columns and here it s easy to go so phone and country cake so next row here s over going to create a link query at any steps could be basically the same idea is so I m an open up its embedded expression gave me the same thing from customer and TV customers looks_select night and select the row tank then I can open up the embedded expression again
Minute 7
TD and a Soviet customer Company name change and then I m going to just copy this couple times clue a company named if we want phone and then we want country today and now I ve got an HTML document of talk is an XML document to do the same thing except this time in a car HTML K and that s in a fight ensued as a click day and there s my HTML document that was not easy to create and select the figure out how we can do a little bit more complex documents with the data that we have if you notice we have come some one to many relationships here at figure out how we can express those and create an
Minute 8
XML document is much more complex family created axonal document that come displays all of the orders for every customer sitting on a show customers their orders and order details of tank so how do we do that so I m just comment this out and and I want to declare a new document contains some is a damn orders equals quarters Cato be a route then I want to traverse the customers all their orders and in all those order details and build up a document or tank than nested document selects open up an embedded expression from customer and DB customers can t select change
Minute 9
Customer case the name and phone here case then open up an embedded expression again company name K Cinelli won a deal to sell in a create the orders node and K C orders Kenny to watch if I saw this to see how that orders change to face with keeping track of what the ending cat tag is only change the opening track a person I need another embedded expression with a link query ends this time could be from order and customer got an annual C orders here obtain frightened the link to SQL classes generated the proper Thomas Association is for us
Minute 10
Analogies can deselect order came in here I want to select the order date paying order that order date and we want for instance the havoc to shift the trucks came a tank and then from here we want to also get at the details as well okay so I m an essay details and he never going to nest the details in here so to open up an expression here in some detail in order not order details to concede it now
Minute 11
Is that Association select cannabis can call the items containing item and where to have product tank so the product we want to get at the product name okay so now what s interesting is if we look back at our DB amount tile here are our relationships and is even a little bit here so can see little bit better you ll notice that order
Minute 12
Week at the product name okay so it s very simple to do that we just go detail product see it s right there product name to such illegal easy and it s so you don t have to do any crazy joins anything like that because our model is displaying the associations exactly how we want them what they said and I also want to when I do is I want to create an attribute for instance of the IT so can show you not alone open up another embedded expression and then were in detail product ID okay so lets you see before we added a total score start aggregating our details to get the order total is to see how this document looks in case on existing orders that save see
Minute 13
Orders XML and in the process start in case of theft I run this and now you see his orders only order so that it s basically the new route to the media allow this spreadsheet to be interactive and tanks of is the orders and Emmanuel notice underneath that is customers pay to get customers and anything I or the orders for the customer pay and then underneath those we have the details I make will write so that s how easy it was to create a come more complex
Minute 14
Relationships inside of our database okay so let s figure out now how we can display the order totals soared in a needed do you need aggregate our details in order to put the order total up here case and I want to put the total and tank so in that case all you do is open up another embedded expression and we can say aggregate detail in order time order details into a knowledge to a sum up the detail unit price times detail not quantity tank so that s all that s just the statement that will basically aggregate all her details for us I said now and go back here and hit F5
Minute 15
Now we should see the order totals in NAR or a total they that s how easy it is to build a XML documents from your databases you just need to create a elect a SQL classes in order to easily query over your data and then you can and use link to XML to create your XML documents and you re not even constrained to creating XML documents you can create HTML documents anything with an XML structure so I d like to thank you for your time if you like to check out the Visual Basic developer Center will be posting updates these videos there please also check out the Visual Basic team blog and my blog for more information basic