For m a t t ing Tw o Colu m n s fr om t h e Em ploye e s Ta ble in VB.NET

Maker Data Matrix ECC200 in VB.NET For m a t t ing Tw o Colu m n s fr om t h e Em ploye e s Ta ble

When y ou use t he XslPat h pr opert y w it h a SqlXm lCom m and obj ect , y ou don t get t o see t he under ly ing XML docum ent . The int er nal code in t he SqlXm lCom m and class aut om at ically conv ert s it s XML docum ent t o HTML code according t o t he inst ruct ions in t he file t o which t he XslPat h pr opert y point s. The following sam ple t ransform s an XML docum ent based on t he Em ployees t able in t he Nort hw ind dat abase. I nst ead of j ust saving t he final HTML page, t he pr ocedur e first saves t he XML docum ent w it hout set t ing t he XslPat h propert y . Then t he procedur e assigns a st ring v alue t o t he XslPat h propert y t hat point s t o an XSLT file and saves a second docum ent in HTML form at . The SQLToXMLToHTMLFor Em ployees pr ocedur e st art s creat ing an XML docum ent wit h a SqlXm lCom m and obj ect point ing t o t he Nort hw ind dat abase. The SQL st ring for t he obj ect ext ract s t he Em ployeeI D, First Nam e, and Last Nam e colum ns from t he Em ployees t able by using a SELECT st at em ent w it h a FOR XML clause. Recall t hat t his process ret ur ns an XML fragm ent wit hout a unique out er t ag for t he docum ent . Ther efor e, t he pr ocedur e assigns a st ring value ( My Root ) t o t he Root Tag pr opert y for t he SqlXm lCom m and obj ect . Next t he pr ocedur e set s up t o save t he XML docum ent in a file nam ed Unfor m at t edEm ployees.xm l befor e inv ok ing t he Ex ecut eToSt ream m et hod t o sav e t he XML docum ent . The set up process enables t he Ex ecut eToSt ream m et hod t o pass t he docum ent dir ect ly from t he SqlXm lCom m and obj ect t o a file. Aft er saving t he XML docum ent , t he pr ocedur e assigns t he XslPat h propert y for t he SqlXm lCom m and obj ect . The propert y point s t o t he My XSL.xslt file in t he r oot folder of t he XMLSam ples solut ion folder . Then, t he pr ocedur e inv ok es t he Ex ecut eSt ream m et hod for t he SqlXm lCom m and obj ect t o r epr esent t he HTML page wit h an in- m em or y st ream obj ect . Aft er capt ur ing t he HTML as a st ream obj ect , t he procedure m ov es on t o r ead t he st ream and t hen wr it e it t o an ext er nal file nam ed For m at t edEm ployees.ht m l. Sub SQLToXMLToHTMLForEmployees() Specify SqlXmlCommand. Dim cmd1 As New SqlXmlCommand("Provider=sqloledb;" & _ "Data Source=(local);" & _ "Initial Catalog=northwind;Integrated Security=SSPI") cmd1.CommandText = _ "SELECT EmployeeID, FirstName, LastName " & _ "FROM Employees FOR XML AUTO" cmd1.CommandType = SqlXmlCommandType.Sql cmd1.RootTag = "MyRoot" Name the path and file for the Xml result set, then instantiate a Stream object for the file s contents.

Dim myXMLfile As String = _ "c:\SQL Server Development with VBDotNet\" & _ "12\UnFormattedEmployees.xml" Dim myFileStream As New System.IO.FileStream _ (myXMLfile, System.IO.FileMode.Create) Execute cmd1 and store the result set in the stream. cmd1.ExecuteToStream(myFileStream) Close the file stream to recover the resource. myFileStream.Close() Set the XslPath property to specify the name of the XSLT style sheet. cmd1.XslPath = "..\MyXSL.xslt" Return the HTML from cmd1 as an in-memory stream object; then, create a stream reader to read the contents of the stream. Dim stm1 As Stream stm1 = cmd1.ExecuteStream Dim srd1 As New StreamReader(stm1) Declare and instantiate a string for the name of the file pointing at the FileStream with the HTML content. Dim str1 As String = _ "c:\SQL Server Development with VBDotNet\" & _ "12\FormattedEmployees.html" Dim fst1 As New FileStream(str1, FileMode.OpenOrCreate) Declare and instantiate a StreamWriter to populate the file holding the HTML content; then, read the StreamReader s contents into a string and write the string to fst1. Dim swt1 As New StreamWriter(fst1) Dim str2 As String = srd1.ReadToEnd swt1.Write(str2) Close the file. swt1.Close() End Sub Figur e 12- 12 shows t he UnForm at t edEm ployees.xm l file. Not ice t hat it cont ains nine Em ployees elem ent s. Each elem ent has t hree at t ribut es wit h v alues for Em ploy eeI D, First Nam e, and Last Nam e. The cont ent and layout follow direct ly from t he Com m andText propert y set t ing for t he SqlXm lCom m and obj ect in t he SQLToXMLToHTMLFor Em ployees pr ocedur e. I t is t he UnForm at t edEm ploy ees.xm l file t hat t he My XSL.xslt file t ransform s. Figu r e 1 2 - 1 2 . Th e Un for m at t e dEm p loye e s.x m l file con t en t s g en e ra t ed by t h e SQLToX M LToH TM LFor Em p loye e s pr oce d u r e.

Figur e 12- 13 shows t he t ransform ed XML docum ent saved as Form at t edEm ploy ees.ht m l. The file in Figure 12- 13 appears as a t able inst ead of a raw list ing of elem ent s. I n addit ion, t he second colum n display ing last nam e appears in it alics and bold. Ther e s addit ional form at t ing as well, such as a t able header wit h a background color . The My XSL.xslt file facilit at ed all t he lay out and for m at t ing changes bet ween Figure 12- 12 and Figur e 12- 13. There s one m or e difference bet w een t he t wo figur es. Figur e 12- 13 has only t wo colum ns, but t he init ial XML docum ent has t hr ee at t r ibut es for ev er y Em ploy ees elem ent wit hin t he docum ent . This difference result s fr om t he fact t hat t he My XSL.xslt file select s only t wo of t he t hree at t ribut es for display . Figu r e 1 2 - 1 3 . Th e For m a t t e dEm ploye es.h t m l file con t en t s g en e ra t ed by t h e SQLToX M LToH TM LFor Em p loye e s pr oce d u r e.

The list ing for My XSL.xslt appears next . I t com m ences wit h it s declarat ion as an XML docum ent and a r eference t o t he Wor ld Wide Web Consort ium nam espace for XSLT files. The design of t he t ransform has t w o m ain part s denot ed w it hin t wo xsl: t em plat e elem ent s. The first elem ent m at ches t he Em ploy ees elem ent in t he

source XML docum ent , nam ely Unform at t edEm ployees.xm l. For each Em ployees elem ent wit hin t he sour ce docum ent , t he t ransform file select s t wo at t r ibut es First Nam e and Last Nam e. The Last Nam e select ion is em bedded w it hin t ags t hat render t he at t r ibut e values in bold and it alic. This init ial segm ent of t he file also defines a row lay out for t he r esult w it h beginning and ending < TR> t ags. Each select ed value appears wit hin beginning and ending < TD> t ags t o indicat e t hat t he values occupy different cells w it hin t he row . The second xsl: elem ent wit hin t he .x slt file defines t he overall body for t he docum ent . For exam ple, t his elem ent st art s w it h a beginning HTML elem ent and closes wit h an ending HTML elem ent . The HEAD block assigns a color in hexadecim al not at ion t o t he backgr ound- color at t ribut e for t he t able heading ( t h) elem ent . The BODY block launches a TABLE block and form at s t he heading for t he t able. The xsl: apply - t em plat es elem ent w it hin t he TABLE block specifies t he insert ion of t he first xsl: elem ent w it hin t he second xsl: elem ent . This insert ion adds t he rows t o t he t able aft er t he t able heading. I t is cr it ical t hat t he xsl: apply t em plat es elem ent select t he My Root elem ent w it hin t he XML docum ent . This is because t his elem ent cont ains all t he Em ploy ees elem ent s w it hin t he source XML docum ent t o t ransform . I m properly specify ing t his select at t r ibut e can lead t o an em pt y t able.