One of the added accepted tasks for which XML is acclimated for is to automate invoicing. This is what a simple argument balance looks like.
This is what an akin XML balance looks like.
Creating an XML certificate like an balance is added art than science. There is no distinct actual way to do it. Having a cardboard certificate to use as a archetypal helps a lot. You can decompose the archetypal certificate into its alone sections and body their XML analogs one area at a time, again stitch them all calm into a distinct unit.
Execute the afterward SQL calligraphy in SQL Server Management Studio to actualize the audience tables absolute the abstracts acclimated to accomplish the above-mentioned invoices.
The SQL calligraphy creates three tables; Vendor, Applicant and Sales.
The afterward SQL calligraphy was acclimated to actualize the antecedent XML balance for Applicant #1, Jiffee Stop Gas, from the aloft data.
Execute the calligraphy in SQL Server Management Studio to aftermath argument and XML results.
The two SELECT statements at the end of the calligraphy acquiesce the XML cipher to be displayed as a VARCHAR(MAX) argument datatype, and as an XML abstracts type. The argument datatype is what will be captured to a book by the SSIS package. You can bang on the articulation of the XML abstracts blazon achievement to genererate the XML certificate in a new tab of Management Studio.
The aboriginal area of the XML balance (Invoice Number and Balance Date) was stored in the VARCHAR(MAX) capricious ‘@InvoiceInfo’ by the afterward allocation of the SQL script.
The additional area of the XML invoice, Vendor, was stored in the ‘@Vendor’ VARCHAR(MAX) capricious by this allocation of the SQL script.
The third area of the XML invoice, Client, was stored in the ‘@Cliient’ VARCHAR(MAX) capricious by this allocation of the SQL script.
The fourth area of the XML invoice, Charges, was stored in the ‘@Items’ VARCHAR(MAX) capricious by this allocation of the SQL script.
The aftermost area of the XML invoice, Balance Due, was stored in the ‘@BalanceDue’ VARCHAR(MAX) capricious by this allocation of the SQL script.
Finally, the bristles VARCHAR(MAX) variables we created aloft are stitched calm to actualize the capacity of the XML balance document.
Now that we accept the SQL calligraphy bare to accomplish the invoice, we are accessible to actualize an SSIS amalgamation that executes the calligraphy and saves the achievement of the to a file.
Actualize an SSIS amalgamation in Business Intelligence Development Studio with an Execute SQL Task, a Calligraphy Task, a Precedence Constraint abutting the two tasks, and amalgamation variables called client_id, output_path_template and xml_doc as apparent in the awning attempt below.
Double-click the Get Applicant ID calligraphy task, accept Microsoft Visual Basic 2008 from the ScriptLanguage card list, access User::client_id in the ReadWriteVariables argument box, again bang the Edit Calligraphy button
Right-click the Affiliation Managers console and baddest New OLE DB Connection…
Bang the New button in the Configure OLE DB Affiliation Administrator chat box.
Access the name of the server and database area the audience abstracts was created and bang the OK button.
Confirm that the abstracts affiliation has been created and bang the OK button.
Change the name of the affiliation administrator to article allusive if you wish, again double-click the Actualize XML Balance agreeable SQL Task.
Bang on the SQL Statement band to accompany up the button. Bang on the button..
…to accompany up the Access SQL Query box…
…and adhesive this SQL cipher into it.
Bang the OK button of the Access SQL Query argument box.
Bang the OK button of the Execute SQL Assignment Editor.
Double-click the Write XML balance to book calligraphy assignment component.
Bang on the ReadOnlyVariables line, again on the button to accompany up the Baddest Variables chat box, baddest the User::xml_doc analysis box, again bang the Edit Calligraphy button.
Access the follwing code…
…into the calligraphy assignment editor, again save and abutting it, and bang the OK button in the Calligraphy Assignment Editor agreement button.
The VB.NET cipher aloft creates the achievement book name from the SSIS amalgamation variables client_id and output_path, embedding the applicant ID in the aisle name. It again strips out the <ROOT> and </ROOT> aspect tags that were amid by the System.Xml.XmlDocument class, followed by abacus the encoding and stylesheet advertence tags to the XML certificate so it can be rendered in a browser back invoked.
Actualize a agenda called C:xml4 and archetype the absorbed Invoice.xsl book to it.
Execute the SSIS amalgamation and access the adapted Applicant ID in the chat box…
…to actualize an XML file..
…in the C:xml4 directory.
Double-click on the Invoice.xml book to transform and affectation it in your absence browser.