hen Microsoft® SQL Server™ began supporting XML I thought it was cool, but somewhat frivolous. If you didn't need to display data on a Web page from SQL Server, was this feature all that useful?
      Why would anyone go to the trouble of retrieving data via XML if you can get a Recordset object using a simple SQL statement? In most cases, using XML means you still have to reparse the XML stream to make use of the data. These were my preconceptions. Then I began using BizTalk™, which has XML at its core. BizTalk uses XML as its primary language, so there is no need to translate anything into ADO. Now XML support in SQL Server was beginning to make sense to me.
      In this article I'll discuss the implementation of an updategram and the system components for an automated deal entry system using BizTalk. Real-life applications of such a setup might include systems for sales deals, loan deals, or any sales-oriented system used for entering new customer information for a sale. My deal entry system takes a positional flat file (a text file with data fields that are laid out in a fixed columnar format), and using BizTalk, transforms this into XML, maps this as an updategram, and posts it to the SQL Server database. The messages that pass from moving part to moving part are all XML.

Using XML for Database Access

      So, what can a developer do with XML and SQL Server 2000? You can do the following:

• Use a URL to access SQL Server
  
• Retrieve records from SQL Server via XML using a SELECT statement and the new FOR XML clause
  
• Save and retrieve XML records to SQL Server using the OPENXML resultset provider
  

      Shortly after the release of SQL Server 2000, SQL Server Web Release 1 (WR1) was released. It contains the newest of the XML features for SQL Server 2000, including:

• Updategrams for updating and inserting data into SQL Server
  
• The ability to use XML to bulk load large XML documents into SQL Server 2000
  
• Updates to the XDR schemas and changes to the XPath behavior against those schemas to improve performance
  
• Support for generated GUIDs using an updategram
  
• Bug fixes
  

      Unfortunately I don't have room to cover each feature in detail. For more information, please reference SQL Server Books Online or the new help file distributed with WR1. This release also included changes to the SQL Server 2000 OLE DB provider so you can use XML documents as command text and stream resultsets. I will cover many of these basic XML features in the next section.
      As I mentioned, I'll be discussing updategrams. But before I do, it is important that you understand some of the basic XML features in SQL Server 2000. Understanding basics such as accessing SQL Server using HTTP will give you the knowledge you need to troubleshoot issues that will arise.

Querying SQL Server with XML

      The largest difference between the way data is represented traditionally and the way it's represented in XML can be found in the rowset layout. Traditionally, table manipulation with SQL Server meant dealing with records in a relational fashion. With XML, data can be represented hierarchically. This idea will be familiar to Web developers who use XML for their data and to those who've experimented with hierarchical recordsets in ADO. For others, it may a take a bit of getting use to. Using XML for data transport requires the design of a data access layer that takes into account this layout difference.
      The best way to access SQL Server 2000 with XML is through your browser. To do so, you must first set up a virtual directory for SQL Server. You must use the MMC snap-in (sql iis admin.msc) that comes with SQL Server 2000 (shown in Figure 1). You can launch this from the SQL Server 2000 program group under Configure SQL XML Support in IIS.
      This virtual directory will be used to post updategrams to SQL Server. It's similar to a virtual Web directory, with a few differences.
      Like creating any virtual directory, you must first specify the directory name and path by selecting New | Virtual Directory under the Default Web Site (as you saw in Figure 1). Then, you must add the security credentials for the SQL Server data source you will be configuring. (For more information on the topic, see the Sidebar "Security Issues.")
      Next, just like setting up an ADO data source, you must specify the database server and database name that will be used to tie the newly created virtual directory to the datasource. Finally, you must select "Allow sql= � or template= � URL queries," under the Settings tab. To test the URL access, simply enter the following code in your browser, substituting the server, virtual directory, and table name in the URL that you are using:

  https://youriisservername/yourvirtualdirectoryname?
  

  sql=SELECT+*+FROM+Customer+FOR+XML+AUTO&root=root
  

If everything was set up correctly, you should see something like the response in Figure 2.
      For more information about the URL syntax that is supported by SQL Server, and hence the SQL ISAPI extension it uses to provide this feature, please see the section on URL access and SQL Server in the SQL Server documentation. If you need additional help setting up the virtual directory, see Getting Started with Development, also in the documentation.
      Once you've tried directly accessing SQL Server over HTTP, you'll probably get tired of typing these long queries. A solution I implemented in my BizTalk sample is a template file.
      A template is an XML file containing one or more SQL statements or XPath queries. The template file contains the same SQL that you would enter into the address bar but it is much easier to read, edit, and maintain. Template file names can either be specified in the URL or the entire contents can be appended to the URL itself, which is much simpler. To create a template, simply use the following format and save it as an XML file.

  <ROOT xmlns:sql="urn:schemas-microsoft-com:xml-sql">
  

      <sql:query>
  

            SELECT  * FROM  tableofchoice
  

           FOR XML AUTO
  

      </sql:query>
  

  </ROOT>
  

The <ROOT> tag provides the top-level element for the XML results. This can be set to whatever element name you desire. For simplicity, I use the default ROOT.
      By default, the AUTO qualifier returns results as nested XML elements. This becomes more apparent when the SQL statement used contains a join with multiple tables where the order in which the tables are specified effects how the results are displayed. There are other modes such as RAW and EXPLICIT that also control the shape of the results. For my purposes, the AUTO mode will suffice. By default, all XML results are returned by mapping column values to attributes, as you saw in Figure 2. If results must be of element-only form, the ELEMENTS qualifier can be used like so:

  FOR XML AUTO, ELEMENTS
  

      The XML template file should reside in a subdirectory called template (or something similar) under the main virtual root that you set up using the provided snap-in. For example, if your virtual root is called Deal, as it is in my sample, then under the Virtual Names tab in the virtual root properties dialog you would select New, and enter "template" in the dialog provided. Then you would select template for the type. Next, you specify the subdirectory under the virtual root upon which you will place your template files (for example, \deal\template). Finally, you select Save.
      That's it. Now you can specify a template file directly in a URL, to get the results you saw in Figure 2 (assuming the SQL statement used in the template is the same):

  https://youriisservername/yourvirtualrootname/templatevirutalname/
  

     templatefilename.xml
  

Updating SQL Server with XML

      Now that you can retrieve data, how do you update it? Before, the updategram's release (in WR1) you were forced to use the OPENXML rowset provider in SQL Server 2000. In order to take advantage of OPENXML, you needed to be comfortable with Transact-SQL and with writing stored procedures. This isn't a problem for most developers, but if you want to avoid placing business rules into this tier, then OPENXML may not be your best solution. To give you a better appreciation for what the updategram feature brings to the table, I'll briefly explain in the following sections how OPENXML works.
      OPENXML is a SQL Server 2000 Transact-SQL extension that allows stored procedures to process XML and generate a resultset. The resultset can then be accessed from within the stored procedure using Transact-SQL statements. Any Transact-SQL statement that normally uses a rowset can be used with the newly created OPENXML keyword. This includes any INSERT, UPDATE, DELETE, and so on.
      OPENXML is a rowset provider and acts just like the TABLE or VIEW keyword by providing a rowset view of the XML document, like a rowset wrapper around the XML hierarchy. XPath queries can then be issued against a passed-in XML document from within a Transact-SQL stored procedure. The results of that query can then be used to do whatever the author of the stored procedure wants it to do. This can include straight queries, inserts, updates, and other kinds of data manipulation.

The Alternative: Updategrams

      Thankfully, the SQL Server team realized this was not as straightforward as it could be and released WR1 to the public. Updategram technology provides a simpler way to update data similar to that of the queries I just described. By using a specific namespace (xmlns="urn:schemas-microsoft-com:xml-updategram"), you can assemble an XML document just like the templates used to query SQL Server. This provides the ability to update or insert any data that is mapped to a virtual directory using the very same access method as before.
      To create an updategram, you build a XML document similar in structure to the following:

  <root xmlns="urn:schemas-microsoft-com:xml-updategram">
  

  <sync>
  

  <after>
  

    <Deal CustomerNumber="3" DealType="1"/>
  

    <Deal CustomerNumber="4" DealType="2"/>
  

    <Deal CustomerNumber="5" DealType="3"/>
  

  </after>
  

  </sync>
  

  </root>
  

The first difference you'll notice from that of the query template document you saw earlier, is the namespace declaration. WR1 brings with it a new namespace and therefore a few new tags to use to get an updategram to work. The namespace, in this example, is declared as the default namespace. The reason for this will be clear later when I describe my deal entry system sample. For now, you should just realize that no other namespaces are required, and therefore I can make the updategram namespace the default.
      Also realize the tags that originate from the updategram namespace, such as <sync> and <after>, are not prefaced by a namespace qualifier. If, in the future, you have any ambiguities in tag naming, then you would have to qualify each tag with something like the following:

  <root xmlns:updg="urn:schemas-microsoft-com:xml-updategram">
  

  <updg:sync>
  

  <updg:before>
  

  </updg:before>
  

  <updg:after>
  

    <Deal CustomerNumber="3" DealType="1"/>
  

    <Deal CustomerNumber="4" DealType="2"/>
  

    <Deal CustomerNumber="5" DealType="3"/>
  

  </updg:after>
  

  </updg:sync>
  

  </root>
  

      In fact, this is how most of the updategram samples are shown. Could I have used the updategram prefix? Sure, but by using the updategram namespace as the default, not only is it a bit easier to read, but it makes it simpler when I integrate this with BizTalk. As will become clear, this updategram will be generated by what I've defined as a BizTalk specification. More specifically, I will generate this updategram on the fly using the mapping capabilities in BizTalk and a little bit of elbow grease in my code.

Mapping Updategrams

      There are two ways to map an updategram: implicit mapping and explicit mapping. I will be using implicit mapping since my database example is rather simple. Implicit mapping does not use a mapping schema (see the section "A Brief Look at Mapping Schemas" later in this article), but instead relies on the default mapping schema.
      The default mapping schema in my case maps the Deal element to the Deal table and all of its attributes directly to each column of that table. Keep in mind that I am using an attribute-centric updategram. Each attribute represents each column of the database. You are free to use an element-centric updategram. But again, take heed, as it will become clear as to why I use attributes, and not elements, to represent the database columns when using BizTalk.
      With implicit mapping you must exactly match the XML element names to that of the database objects (tables and columns, for instance). If the database uses a somewhat cryptic naming convention, I suggest you use a mapping schema (which is a little more work if you are using BizTalk). Explicit mapping is just that: it explicitly uses a mapping schema in the updategram to map each database element. I'll describe explicit mapping in more detail in the next section.

Tags Used in Updategram Apps

      Next I'll go through a few of the tags I used in my updategram application. An updategram template is simply a document with a set of <sync>, <before>, and <after> tag blocks. That's it; all of the database elements reside inside of these tag blocks.
      The nesting of those blocks is important, however. The <before> block identifies the state of the database before the update. (This is only used during updates.) The <after> block contains the new state and is used for updates and inserts. In my sample application, I only demonstrate database inserts.
      The first updategram-specific tag you will work with is the <sync> tag. The <sync> block contains the <before> and <after> blocks. The <sync> block can contain more than one set of <before> and <after> blocks, but they need to be specified as a pair (when doing updates). If you are only doing inserts, as in my example, then you can omit the <before> blocks altogether. The format of the updategram, especially when using implicit mapping, is fairly flexible. You can have multiple <after> blocks, one after the other, or simply have one <after> block. In my case I use a single <after> block and add each XML element node inside of the <after> tag to represent each row I will be inserting.
      Finally, the last important point to remember is that everything in the <sync> block acts as a single unit of transaction. Everything gets done in that single <sync> block, or nothing gets done at all. You can specify multiple <sync> blocks in a single XML updategram, but the failure of one <sync> block will not affect any other <sync> block. Each block is treated as its own separate unit of work.
      But, here's the rub. If you need to support completely distributed transactions, you must come up with your own scheme. You can run updategrams in the middle tier. You can even run them within a COM+ component. But you cannot create a single transaction boundary among separate updategrams or even separate <sync> blocks as part of the same updategram and have it be a part of one transaction boundary. Hopefully, this support will come later.
      Executing an updategram is simple and is done exactly the same way I executed a query earlier using a template file. In fact, I will be placing my updategram documents in the same subdirectory as my template query documents. Now before I jump into BizTalk, let's take a brief look at mapping schemas.

A Brief Look at Mapping Schemas

      You can specifically map each element of an updategram not only to specific SQL data types, but also to specific fields of the database. I guess they are called mapping schemas since they map an XML document's structure with that of the columns and tables of the database. This gives you a tighter reign of control for those database operations that may be more complex, such as updategrams containing operations too complex to be efficiently performed using implicit mapping. In fact, if you need the best performance of your updategrams, it is recommended that you use explicit mapping. By using explicit mapping, the ISAPI extension does not have to perform any unnecessary conversions as it does when using the default schema for implicit mapping.
      Schemas simply act as a view on the database you will be working with. These views can then be queried using XPath queries and can be used directly in a URL similar to my previous examples. This is like creating views using CREATE VIEW statements and executing SQL statements against the view. A mapping schema would look something like this:

  <?xml version="1.0" ?>
  

     <Schema xmlns="urn:schemas-microsoft-com:xml-data" 
  

           xmlns:dt="urn:schemas-microsoft-com:datatypes" 
  

           xmlns:sql="urn:schemas-microsoft-com:xml-sql">
  

       <ElementType name="Deal" sql:relation="Deal" >
  

        <AttributeType name="CustomerNumber" />
  

        <AttributeType name="DealType" />
  

  

        <attribute type="CustomerNumber" sql:field="CustomerNumber" />
  

        <attribute type="DealType" sql:field="DealType" />
  

       </ElementType>
  

     </Schema>
  

      An XML schema not only describes the structure of an XML document, but also, as in this case, describes how each database element will map to the element types in the schema. In SQL Server 2000, the language used to describe the schema is referred to as XML-Data Reduced (XDR).
      Schemas are much more flexible and easier to create than Document Type Definitions (DTDs). The XDR language is just XML, unlike a DTD. Once the schema is complete, it can then be referenced in the updategram using the mapping-schema attribute in the <sync> tag like this:

  <Root xmlns="urn:schemas-microsoft-com:xml-updagegram">
  

      <updg:sync mapping-schema="sampleschema.xml">
  

      •••
  

The BizTalk Tool Belt

      Running my sample application does not necessarily require BizTalk. In fact, the BizTalk-based tester application I use to test my application can work directly with the Visual Basic component in my application to send the updategram to the database. However, using BizTalk demonstrates how using XML as your primary data language really makes sense.
      As I mentioned earlier, this article describes many parts. I recommend that you familiarize yourself with each part individually before trying to get everything to work as a whole. In fact, the scope and complexity of the configuration management involved with BizTalk is my primary criticism of the product. If you intend to use each piece of my example, or others similar to it, I also recommend that you have a large monitor and plenty of RAM since you will have several windows running during diagnostics. Hopefully, the BizTalk Tester utility I've provided in the code download will allow you to break up the functionality during debugging. In fact, when doing any development with BizTalk, I recommend you build as many debugging tools as possible.
      Before I put each piece together, I will briefly describe the BizTalk-based components I used to build my sample. My explanations assume you understand how BizTalk works conceptually.

The BizTalk Editor

      The BizTalk Editor is used to create what BizTalk calls a specification. This is nothing more than a BizTalk-specific XML Schema. It is called a specification because it is slightly different from the XDR schemas I mentioned earlier. In fact, you can export an XDR-formatted schema from the BizTalk Editor (although not a very descriptive one). The specifications created are used to describe the documents it uses to manage its messages throughout the system.
      For example, these specifications are used for receiving a flat file that needs parsing and translation into XML. They can also be used for mapping one specification and transforming XML into another format, as is the case for my sample application. I use the editor to create both the source and destination specification (see Figure 3).
      The source specification is used to define the envelope I use to determine the XML format of the flat file that will be received (containing the Deal input data). The source specification can also be used from within BizTalk itself as described in the following section.
      The destination specification is the schema used to represent the updategram. It will match my database tables for those fields that will be inserted into the database via the updategram. In fact, the destination specification becomes the updategram after it is mapped, <sync> tags and all. Once the specifications are created, I move over to the BizTalk Mapper to map the source with the destination.

The BizTalk Mapper

      The BizTalk Mapper is used to create a mapping between the source specification and the destination specification (see Figure 4). It controls which fields will be transformed and how they will be formatted. The mapper generates XSLT code to take what is on the left-hand side of the screen (the source specification) and transform it into what is on the right-hand side of the screen (the destination specification). The transformation actually occurs at runtime when the map and all involved specifications are configured in the BizTalk Messaging Manager (as I will describe in next section).
      To design a transfer of a field from the source to a destination, you simply select that field on the mapper screen and drag it over to the right, connecting the fields on the screen. The saved map file is nothing more than a BizTalk-formatted XML file with XSLT code embedded within it.
      To control how the field is formatted, and thus transformed, you can use functoids. Functoids are simply utility methods (like you would find in Visual Basic) that come in several varieties from very specific functionality (for example, string, mathematical, or logical) to more general varieties (such as database and script).
      This may be because I'm a developer, but I've found that the more specific functoids are a bit cumbersome. Designing any complex logic requires the use of too many different functoids on the screen so I limit them, especially for complicated mappings. One good use of functoids is for simple format and validation rules such as the unit of measure and currency conversion which I demonstrate in my example. I save any complex validations for the code that processes the XML file once it has been mapped and transformed. I've found this provides me with the greatest control. Those leaning away from code may have a different perspective.

BizTalk Messaging Manager

      The BizTalk Messaging Manager is where XML documents are processed and sent to and from. I think of it as an XML document highway where you define, configure, and glue the parts together. Those parts include the source and destinations specifications as well as the map I've created.
      In the Messaging Manager I name my envelope, which will be used to specify the XML format to map all the files received from customers in my sample (see Figure 5).


Figure 5 Envelope Properties

      The envelope simply points to the source specification I created in the editor. I also configure the document definitions, which will point to both the source and destination specifications. These document definitions simply name the objects that will travel on the document highway.
      Next I must configure the highway itself. To do so, I create what are known as messaging ports (see Figure 6). A message port defines where the document, once it is in XML format, goes and how it is secured and transported. This can be configured to send a document to a message queue, a file, or another defined BizTalk organization. The message ports in my example are configured to simply travel to a file for debugging and to a queue for production.


Figure 6 Configure Destination Application

      Once the message port is defined, you use the Messaging Manager to define the channel upon which it travels (see Figure 7). The channel, which is usually assigned to a message port, is used to tell BizTalk how to process the XML document it receives. This is where the map I've created is specified. The channel will tell BizTalk to map the received XML document to the destination specification via the created map and send that transformed document to either one of the message ports I've defined. Which port depends on the stage of development I am in (I'll explain why in the next section). Once you understand the concept of messaging ports and channels, creating them is simple. The Messaging Manager's wizards will step you through the process.


Figure 7 Configure Source Application

BizTalk Server Administration

      The BizTalk Server Administrator (see Figure 8) is simply where BizTalk as a whole is managed. This is where services are started and stopped and working queues are managed. The queues are not to be confused with MSMQ; they represent the database tables BizTalk itself uses to manage the data that flows within its system.


Figure 8 Configure Receive Function

      The only thing I will be configuring using the Administration tool is the file receive function. This defines how my deal entry system picks up the files to process. The file receive function specifies which files to receive and which BizTalk specification to use to parse the file into XML. Before any files can be sent through the system, they must first be in XML format. This is where that process happens.

The BizTalk Orchestration Designer

      Unfortunately, I don't have the space to adequately explain the BizTalk Orchestration Designer (see Figure 9) and the schedules it creates. In short, the orchestration sche-dule provides the option of adding business rules without changing any of the code.
      In my example, the Orchestration Schedule picks up the XML destination document from the queue and immediately sends it to my COM+ component. Once in the COM+ component, the XML destination document will be validated and sent to the SQL Server deal entry database via updategrams.
      This is not the only way to process documents from a message queue and call the component (an AIC component could be used, for example), but it provides the facility to add business rules to my schedule as the application becomes more complex without the need for further development. For more information on using the Orchestration Designer, refer to BizTalk Server Help (https://msdn.microsoft.com/library/en-us/startsql/portal_7ap1.asp) or read the article on BizTalk Patterns in MSDN® Magazine, October 2001 ("BizTalk: Implement Design Patterns for Business Rules with Orchestration Designer").

Developing My Deal Entry System

      Now that I've explained the different parts of BizTalk, it is time to assemble a running application. My deal entry system is a system that receives positional flat files as standard text documents. The text documents are received via FTP (or some other method) and eventually transformed and inserted into my database using updategrams to SQL Server. Once running, this will be a completely automated system involving the steps shown in Figure 10.


Figure 10 Data Update Architecture

      To run my sample, you must install and configure the following:

1. Windows® 2000 with Service Pack 1 or 2 from https://www.microsoft.com/windows2000/downloads/default.asp
   
   • SQL Server 2000
     
   • XML for SQL Server Web Release 1 (WR1) from https://www.microsoft.com/sql/downloads/default.asp
     
   • BizTalk Server 2000 (Service Pack 1 is recommended)
     
   • Visio® 2000
     
   MSXML 3.0 Release Parser (Service Pack 1 is optional) from here
   
2. Visual Basic 6.0 (Service Pack 4 is recommended, Service Pack 5 is optional)
   

      Once each of the components has been installed, you can begin pulling together the solution. Now for my token consultant statement: the deal entry solution is not a production-ready system by any means and should only be used as the starting point for the development of a full scale solution. It should, however, give you an idea of how you could a take a flat file, turn it into an XML updategram, and insert records into SQL Server using BizTalk.
      By no means does the solution require the deal entry files to be flat files. The files could already be in XML, in which case you could scale down step one (as I'll describe later) and simply create a specification modeled against the XML file you receive. This would make your job a little easier. For this example, I am going to assume you are starting with a flat file with deal information that needs to be converted, transformed, and inserted, and that looks something like the following data from the dealX.txt document that can be found in the code download:

  0ET The eTier Grp 2024 Fairview Houston   TX77019
  

  1ET 00001       9001      99951
  

  1ET 00001       8001      89951
  

  1ET 00001       7001      79951
  

      In the next sections, I'll describe these five steps, which you'll need to follow to develop the solution:

1. Create the specifications using the BizTalk Editor
   
2. Create the map file using the BizTalk Mapper
   
3. Create the channels and ports using the BizTalk Messaging Manager
   
4. Create the orchestration schedule using the BizTalk Orchestration Designer
   
5. Create the BizTalk Server updategram component using COM+
   

Creating the Specifications

      Unless you're a computer, the data from dealX.txt at the end of the previous section doesn't make much sense. It is a very simple example of the flat file input that will be received by the deal entry system. To receive a flat file, I need to first create a source specification that will be configured as the BizTalk envelope for files of this exact format.
      I won't describe each property that has been set, but to load the specification I've created for this flat file, open the BizTalk Editor, and open the file called DealFlatFileSpec.xml under the \spec directory of the sample code from the download.
      It won't take long to inspect how this specification, which you saw earlier in Figure 4, has been formatted. It has been defined as a positional flat file with each of its attributes containing a start and end position under the reference tab in the BizTalk Editor. It will be helpful to use this file as a starting point for any positional flat file specification you build. Each attribute has been defined with a start and end position with its parent element showing that this node should be positional.
      The only other important setting to understand is the root node which designates the document type, standard, and record delimiter. Again, please reference BizTalk Server Help for more information on this topic.
      The other specification I use is the destination specification. This is much simpler than the flat file specification I created for the source. This specification should be created to model the database as an updategram. It will contain all of the database elements that will be sent to an updategram in one specification. Later I will parse this into more than one updategram.
      The specification standard property on the reference tab should be set to XML for the root node. The root node can be named anything; later this will be generated as the root node of the updategram. The next two nodes are important. First there should be a sync node and, nested within that, the after node. If you've read the updategram section carefully, this should be self-explanatory.
      The rest of the specification includes one element for every table you will be inserting data into, and an attribute to represent each of the columns that make up that table. Attributes were used in the event that column names were repeated using multiple tables, as they frequently are in cases when foreign keys match the name of the primary key to which they are linked. The BizTalk Editor will not allow the addition of multiple attributes with same name whether they are in different nodes or not. That is why I am using an attribute-based specification.
      The last thing to add is the namespace, which is set to urn:schemas-microsoft-com:xml-updategram at the root node level. This adds this namespace as the default namespace, which will be required in the updategram. Open the specification called DealDatabaseSpec.xml under the spec directory to review the settings. Now I'll go through the map.

Creating the Map File

      Open the file DealFileToDatabaseMAP.xml from the BizTalk Mapper. The map for my sample is rather simple. It contains the source specification on the left and the destination specification on the right. Most of the fields are mapped directly to their counterparts on the right. The same names were used in the specification for clarity only.
      Notice that the first field, DocumentType, is mapped directly to the ContactNumber field. This is simply used to fill that field with a value. If any fields are not mapped in the mapper, the field will be left out of the final transformed XML document once the system runs. This makes more work for you when you want to send this as an updategram, so I would suggest preparing all updatable fields with values first. The simpler you make your mappings, the better.
      The element values can then be filled by writing code (which I will show shortly) which is sent directly to SQL Server as an updategram. I added a few functoids to show how they may be incorporated into this example, but they play no major role in the end result other than to spice up my map.

Create the Channels and Ports

      Once I have the specifications and the map, it is time to create the ports and channels. First, however, I must name the document definitions and envelope since I will be referencing them in the channel configuration. I created two document definitions and named them DealEntrySource and DealEntryDestination and assigned them to the source and destination specifications, respectively. Next, I created a new envelope and assigned it to the source specification. Now I can create the actual ports and channels.
      I only need one port to send the resulting XML file to an MSMQ queue. However, it will make life a little simpler to first create a port to a file and view the results before proceeding. Initially sending an XML file directly to a queue will not make the results very readable from within the queue, and this can create debugging nightmares. Each port will be created using the "to an application" menu command for "Creating a Port" in the Messaging Manager. Besides the file settings you use (such as file://c:\TEMP\deal.xml), keep the defaults for each property in the wizard.
      Once the port to a file has been created, you can create the channel from that same application. The channel should assign the source specification for the inbound document, the destination specification for the outbound document, and the map created in the previous section. All other settings should use defaults.
      Once this has been created, you can test the port and channel along with the specifications using the BizTalk Tester tool I've included with the sample code. You do this by opening the "DealX.txt" input file and specifying the name you gave your channel along with the name of the envelope you set for the source specification. If the text document was parsed correctly, you should receive a confirmation dialog along with the XML document output to the file location you just set.
      The XML output file will be the updategram file that my Visual Basic component processes and eventually sends to SQL Server. The second message port is configured exactly the same as the "to a file" port, except the transport will be Message Queuing in the port wizard. It must be set to an MSMQ queue that you've already set up (for example, direct=OS:ETIER3\private$\dealentry).

Creating the Orchestration Schedule

      For my example, this step is simple. Open the provided schedule called DealEntry.skv in the schedules subdirectory of the sample code in the Orchestration Designer. Since I can't compile until I compile or register my Visual Basic sample code, I can go directly to the next step.

Creating the BizTalk Server Updategram Component

      I've made sending an updategram easier by providing the wrapper methods to prepare and send updategrams. To view the code, open the Visual Basic project file called BTSUpdateGram.vbp. This code is rather straightforward, with one main component and one main method called Execute. A compiled orchestration schedule will pick a transformed XML document out of the configured MSMQ queue, call this method, and send the string containing the entire document.
      Inside the Execute method, I use a DOM object for all XML manipulation and call my updategram helper methods from here. The PrepareUpdategram method is called to first create a DOM with only the customer records from the entire DOM document, which is the document I built from the XML string that was sent from the schedule.
      The customer record is then inserted into SQL via the updategram by calling the SendUpdategram method, passing in the prepared DOM object. I then query the database, retrieve the generated primary key, and add this key to the deal records. The deal records are then sent to SQL Server in the same fashion. Figure 11 shows the SendUpdateGram code from BTSUpdategram.vbp in MUtilities. The following are the important lines from the Execute method:

  'Load the WHOLE contract in its XML Format
  

  Set oDOM = GetDOMFromString(sXML)
  

  •••
  

  'Prepare UpdateGram Template
  

  Set oDOMCustomers=PrepareUpdateGram(oDOM,"Customer",_sTemplatePath)
  

  •••
  

  sResponseXML=SendUpdateGram(m_sWebPath,_ sCustomerUpdategramFile,_
  

                              oDOMCustomers)
  

  •••
  

      All communication with SQL Server, including the SQL queries, happens through XML; there is no need to include any ADO code in the sample. Once you've got a handle on the sample code, compile the project and compile the DealEntry.skv file. Run the compiled DealEntry.skx from the XLANG Monitor (see Figure 12) and, assuming you have a few XML documents waiting for you in your message queue, your component should be called.


Figure 12 XLANG Event Monitor

      To save some time, I would suggest also running this from within the BizTalk tester once you have an XML destination file that you can send directly to the Visual Basic component. Some destination files (updategram candidates) have been included in the sample code. You can open an XML file from the tester as well. If you do, make sure Test Component Only is checked in the BizTalk tester GUI. That's many steps with many moving parts, but it all works; I promise.

Conclusion

      This article, along with my sample code, should shed some light on the usefulness of the XML features in SQL Server 2000. XML is no longer industry hype; it's a powerful messaging format that is flexible, scalable, and useful across platforms.