Export (0) Print
Expand All

Database Integration with Microsoft BizTalk Server 2002

Click here to download sample - BTS_WP_DBIntegration Samples.exe. (687 KB) Click here to download sample - XML for SQL.exe. (1.06 MB) Click here to download sample - setup.exe. (4.53 MB)
 

Scott Woodgate
Microsoft Corporation

May 2002

Applies to:
    Microsoft® BizTalk® Server 2002
    Microsoft SQL Server™ 2000

Summary: How to integrate BizTalk Server 2002 with databases in general, and with Microsoft SQL Server 2000 in particular, and how to process XML into and out of a database. (39 printed pages)

Download Bts_wp_dbintegration_samples.exe.

Contents

Introduction
BizTalk Server Database Integration
    Using ADO in BizTalk Server Database Integration
    Leveraging SAX2 for Large Data Files
Integration with SQL Server
    Overview of XML and SQL Server
    Using FOR XML and OPENXML
    Using Updategrams
    Using SQL Server XML View Mapper
    Leveraging Stored Procedures and DTS in SQL Server 2000
References

Introduction

Business-to-business data processing requires data interchange that uses XML as the ubiquitous, extensible, and platform-independent transform format. The challenge is how to reconcile the requirements of relational data stores and hierarchical XML data. This article focuses on providing reusable samples and techniques for integrating Microsoft® BizTalk® Server 2002 and databases, with specific reference to leveraging Microsoft SQL Server™ 2000.

A number of integration options are discussed. These include:

  • Using Microsoft ActiveX® Data Objects (ADO) for integration with generic databases
  • Leveraging the most recent version of the Simple API for XML (SAX2) for integrating large data files with generic databases
  • Using the FOR XML clause and the OPENXML keyword for integration with SQL Server 2000
  • Using Updategrams for integration with SQL Server 2000
  • Using SQL Server XML View Mapper for integration with SQL Server 2000
  • Leveraging Data Transformation Services (DTS) in SQL Server 2000

Throughout this paper, samples are provided to assist in demonstrating and explaining the concepts. These samples follow a common structure to enable you to make comparisons.

Included with this article is a Samples directory (contained in the download) that contains several folders. Each folder contains sample files for a specific section. For example, sample files for the section "Leveraging SAX2 for Large Data Files" are contained in the directory Samples\SAX2. The samples assume that BizTalk Server is installed on your C drive, and you should unzip the Samples directory to your C drive. If you have installed BizTalk Server on another drive, you need to unzip the Samples directory to that drive and update the file paths in the samples accordingly.

Most samples involve BizTalk Server, SQL Server, and service components such as application integration components or Microsoft Windows® Script Components. Microsoft Visual Basic® Scripting Edition (VBScript) and ADO applications are also used extensively in the samples.

Many samples use SQLXML 3.0 and SQL Server XML View Mapper 1.0. You will need to download both SQLXML 3.0 and SQL Server XML View Mapper 1.0 from the Downloads section of the MSDN® Web site and install them on your computer before running the samples.

Stored procedures have been used whenever possible. It is preferable to use stored procedures for data access because of their performance advantages. In addition, it is relatively simple to execute them from Visual Basic applications by using ADO Command objects.

All examples have been simplified to make the code and the process simple to understand and follow. They are not meant to represent examples of best practices for coding.

BizTalk Server Database Integration

BizTalk Server can integrate with many types of databases. One of the most useful tools for BizTalk Server database integration is ActiveX Data Objects, or ADO. Other tools, including the Microsoft Simple API for XML (SAX) and the XML Document Object Model (XML DOM), can also be leveraged in BizTalk Server database integration.

BizTalk Server can achieve the highest level of integration with SQL Server 2000 databases. This is due to the advanced XML support that SQL Server 2000 provides. The second part of this white paper focuses on BizTalk Server integration with SQL Server 2000.

Using ADO in BizTalk Server Database Integration

ADO is a fast, powerful, and convenient mechanism for interacting from any language with many different databases, such as SQL Server databases, Oracle databases (using the Microsoft OLE DB Provider for Oracle), and DB2 or VSAM data sources (for example, using the OLE DB drivers packaged in Microsoft Host Integration Server 2000).

ADO recordsets allow you to navigate the records easily, and to apply filters and bookmarks. They also provide sorting, automatic pagination, and persistence. Recordsets can be efficiently marshaled across tiers to their native and extremely compact binary format—the Advanced Data TableGram (ADTG) format.

ADO versions 2.5 and later also provide capabilities for retrieving data in XML format by using the adPersistXML option as well as for executing XML query templates to perform database insert, delete, and update operations. The ADO XML support can be leveraged in BizTalk Server database integration.

BizTalk Server 2002 uses ADO internally through the XLANG Scheduler Engine to save the state of XLANG schedules to the persistence database. You can also leverage ADO for BizTalk Server database integration through application integration components (AICs) inside BizTalk Messaging and through script or COM components inside BizTalk Orchestration. In BizTalk Server database integration, the ADO code is normally contained in AICs.

Retrieving XML data by using the adPersistXML switch

In ADO versions 2.5 and later, you can retrieve data by using normal Transact-SQL queries and persist the data as XML by using the adPersistXML option of the ADO recordset.

The ADO recordset provides a Save method that persists data to a destination such as a file or an Active Server Pages (ASP) page. By default, the ADO recordset persists data in the ADTG format using the adPersistADTG option. To persist data as an XML document you need to execute the Save method using the adPersistXML option.

The main steps for retrieving XML data are:

  • Connect to a data source using the ADO Connection object.
  • Instantiate the ADO Recordset object, and then open a SELECT query to retrieve data.
  • Persist the data as an XML document using the Save method of the Recordset together with the adPersistXML switch.

Sample: using ADO

This sample demonstrates how to retrieve XML data from the Customers table in the Northwind database by using the ADO adPersistXML option.

Note   All sample files for this section are included in the Samples\ADO directory.

Step 1: define a Windows Script Component

Open Notepad and type the following code:

<?xml version="1.0"?>
<component>
<?component error="true" debug="true"?>
<registration
    description="ADOXML"
    progid="ADOXML.WSC"
    version="1.00"
    classid="{71f81b28-4695-4220-bd77-c21abaca02cb}">
</registration>
<public>
    <method name="GetXML">
        <PARAMETER name="sCOnn"/>
        <PARAMETER name="sSQL"/>
        <PARAMETER name="sFileName"/>
    </method>
</public>
<script language="VBScript">
<![CDATA[
function GetXML(sConn, sSQL, sFileName)
    Dim cn, rs
    Const adPersistXML = 1
    'Connect to DB and run SQL
    Set cn = CreateObject("adodb.Connection")
    cn.Open sConn
    'Retrieve Data
    set rs = CreateObject("ADODB.Recordset")
    rs.Open sSQL, cn
    'Persist data as XML
    if len(sFileName) > 0 then
        rs.Save sFileName, adPersistXML
    else
        rs.Save "c:\ADOXMLOut.xml", adPersistXML
    end If
    rs.Close
    cn.Close
    set rs = nothing
    set cn = nothing
end function

Save the file as ADOXML.wsc. Then register the Windows Script Component by right-clicking the .wsc file and clicking Register. You should see a dialog box saying that the component was registered successfully.

Step 2: define a .vbs file

Open Notepad and type the following code:

Dim o
Dim sSQL, sFile

Set o = CreateObject("ADOXML.WSC")
sSQL = "SELECT CustomerID, CompanyName, ContactName, Country FROM
  Customers WHERE CustomerID < 'B'"
sFILE = inputbox("Enter a file name to save the XML document:")
o.GetXML "Provider=sqloledb; Data Source=(local); Initial
  Catalog=Northwind; Trusted_Connection=Yes;", sSQL, sFile
msgbox "XML string saved to: " & sFile
Set o = nothing

Save the file as TestADOXML.vbs.

Step 3: run the program

Now you can run the program by double-clicking the TestADOXML.vbs file, and entering a unique file name and path for saving the XML data. You can then view the XML data by browsing to the file, which will contain the following XML document:

<xml xmlns:s='uuid:BDC6E3F0-6DA3-11d1-A2A3-00AA00C14882'
    xmlns:dt='uuid:C2F41010-65B3-11d1-A29F-00AA00C14882'
    xmlns:rs='urn:schemas-microsoft-com:rowset'
    xmlns:z='#RowsetSchema'>
    <s:Schema id='RowsetSchema'>
    <s:ElementType name='row' content='eltOnly' rs:CommandTimeout='30'>
    <s:AttributeType name='CustomerID' rs:number='1'
      rs:writeunknown='true'>
        <s:datatype dt:type='string' dt:maxLength='5'
          rs:fixedlength='true' rs:maybenull='false'/>
    </s:AttributeType>
    <s:AttributeType name='CompanyName' rs:number='2'
      rs:writeunknown='true'>
    <s:datatype dt:type='string' dt:maxLength='40' rs:maybenull='false'/>
    </s:AttributeType>
    <s:AttributeType name='ContactName' rs:number='3' rs:nullable='true'
      rs:writeunknown='true'>
    <s:datatype dt:type='string' dt:maxLength='30'/>
    </s:AttributeType>
    <s:AttributeType name='Country' rs:number='4' rs:nullable='true'
      rs:writeunknown='true'>
    <s:datatype dt:type='string' dt:maxLength='15'/>
    </s:AttributeType>
    <s:extends type='rs:rowbase'/>
    </s:ElementType>
    </s:Schema>
<rs:data>
  <z:row CustomerID='ALFKI' CompanyName='Alfreds Futterkiste'
  ContactName='Maria Anders' Country='Germany'/>
  <z:row CustomerID='ANATR' CompanyName='Ana Trujillo Emparedados y
  helados'
  ContactName='Ana Trujillo' Country='Mexico'/>
  <z:row CustomerID='ANTON' CompanyName='Antonio Moreno Taquería'
  ContactName='Antonio Moreno' Country='Mexico'/>
  <z:row CustomerID='AROUT' CompanyName='Around the Horn'
  ContactName='Thomas Hardy' Country='UK'/> </rs:data>
</xml>

Sample: consuming ADO-generated XML by using BizTalk Server

The XML data retrieved by the ADO recordset uses a predefined format that BizTalk Server does not recognize. You can, however, convert the ADO-generated XML data into a format that can be consumed by BizTalk Server applications.

ADO-generated XML combines schema and data in one XML file. From that XML file you can derive an XML data file and a schema that can be consumed by BizTalk Server. The following steps describe the conversion process, using as an example the ADO XML data shown in step 3 of the preceding section.

Note   All sample files for this section are included in the Samples\ADO\ADOforBTS directory.

Step 1: create an XML data file

Assume that you have saved retrieved XML data in a file named RetrievedADOXML.xml, and you want to create an XML data file with a root node of <Root>. Perform the following steps:

  • Open RetrievedADOXML.xml in Notepad. Remove all non-data portions of the file.
  • Change the tag <rs:data> to <Root>. Change the tag <z:row …> to <row …>. The contents now look like:
    <Root>
      <row CustomerID='ALFKI' CompanyName='Alfreds Futterkiste'
      ContactName='Maria Anders' Country='Germany'/>
      <row CustomerID='ANATR' CompanyName='Ana Trujillo Emparedados y
      helados' ContactName='Ana Trujillo' Country='Mexico'/>
      <row CustomerID='ANTON' CompanyName='Antonio Moreno Taquería'
      ContactName='Antonio Moreno' Country='Mexico'/>
      <row CustomerID='AROUT' CompanyName='Around the Horn'
      ContactName='Thomas Hardy' Country='UK'/>
    </Root>
    
    
  • Save the contents to a unique file, for example, ADOXMLData.xml. You have created an XML data file.

Step 2: create an XML schema

To create an XML schema, do the following:

  • Open BizTalk Editor. On the Tools menu, click Import, and then double-click the XDR Schema icon. In the Import XDR Schema dialog box, browse to the XML document RetrievedADOXML.xml and click Open. A schema with a root node <row> appears in the BizTalk Editor window.
  • Save the schema to a new file, for example, ADOXMLSchema.xml. This process creates a schema that BizTalk Server can recognize. The schema looks like:
    <?xml version="1.0"?>
    <!-- Generated by using BizTalk Editor on Sat, Nov 10 2001 04:45:40 PM –
      ->
    <!-- Microsoft Corporation (c) 2000 (http://www.microsoft.com) -->
    <s:Schema name="row" b:BizTalkServerEditorTool_Version="1.0" 
      b:root_reference="row" b:standard="XML" xmlns:dt="urn:schemas-
      microsoft-com:datatypes" xmlns:s="urn:schemas-microsoft-com:xml-data" 
      xmlns:b="urn:schemas-microsoft-com:BizTalkServer" 
      xmlns:rs="urn:schemas-microsoft-com:rowset" xmlns:z="#RowsetSchema">
    <b:SelectionFields/>
    
    <s:ElementType 
    name="row" content="eltOnly" rs:CommandTimeout="30">
    <b:RecordInfo/>
    ... 
    </s:ElementType>
    </s:Schema>
    
    

    Note that the schema has a root node <row>. We need to insert a new root node in the schema to match the XML data file. BizTalk Editor does not allow insertion of a new root node into an existing schema. However, we can work around this restriction by using a text editor such as Notepad. Close BizTalk Editor.

  • Open the schema ADOXMLSchema.xml in Notepad. In the <s:Schema . . .> line, change "Schema name" and "root_reference" from "row" to "Root". Then insert the following text into the schema:
    <s:ElementType name="Root" content="eltOnly">
    <b:RecordInfo/>
    <s:element type="row"/>
    </s:ElementType>
    
    

    The new schema looks like:

    <?xml version="1.0"?>
    <!-- Generated by using BizTalk Editor on Sat, Nov 10 2001 04:45:40 PM –
      ->
    <!-- Microsoft Corporation (c) 2000 (http://www.microsoft.com) -->
    <s:Schema name="Root" b:BizTalkServerEditorTool_Version="1.0"
      b:root_reference="Root" b:standard="XML" xmlns:dt="urn:schemas-
      microsoft-com:datatypes" xmlns:s="urn:schemas-microsoft-com:xml-data"
      xmlns:b="urn:schemas-microsoft-com:BizTalkServer" 
      xmlns:rs="urn:schemas-microsoft-com:rowset" xmlns:z="#RowsetSchema">
    <b:SelectionFields/>
    <s:ElementType name="Root" content="eltOnly">
    <b:RecordInfo/>
    <s:element type="row"/>
    </s:ElementType>
    
    <s:ElementType 
    name="row" content="eltOnly" rs:CommandTimeout="30">
    <b:RecordInfo/>
    ...
    </s:ElementType>
    </s:Schema>
    
    
  • Save the schema to a file and then open the modified schema file in BizTalk Editor. Use the Validate Instance option on the Tools menu to validate the new schema against the XML data file (ADOXMLData.xml). Ensure that no errors occur.

You have now created a data file and a schema that can be consumed by BizTalk Server.

Executing XML queries by using ADO

ADO can also be used to insert, update, and delete data against Microsoft SQL Server 2000 by using XML query templates. This is useful for BizTalk Server database integration because data flows through BizTalk Server in the form of XML documents.

When executing XML queries using ADO, be aware of the following:

  • You use the ADO Command object to execute XML query templates. An ADO Command object supports three dialects: Transact-SQL query, XML template query, and XPath query. The default dialect is Transact-SQL. To tell the SQLOLEDB provider that the submitted query is an XML template query, you need to set the Dialect property of the Command object to the globally unique identifier (GUID) value {5D531CB2-E6Ed-11D2-B252-00C04F681B71}. Other GUIDs are {C8B521FB-5CF3-11CE-ADE5-00AA0044773D} for a Transact-SQL query and {EC2A4293-E898-11D2-B1B7-00C04F680C56} for an XPath query.
  • To receive the XML results, you need to use the ADO Stream object. Open the Stream object and assign it to the CommandStream property of the ADO Command object. (You can access the CommandStream property through the Properties collection of the Command object.) Note that the CommandStream property is a provider-specific property and is supported only by the SQLOLEDB provider.
  • To retrieve data as an XML document using ADO, you can use a FOR XML clause in an XML query template that contains a reference to the Microsoft XML-SQL namespace. You then assign the XML query template to the CommandText property of the ADO Command object.

    You must also specify how the resulting XML fragment should be rendered as a well-formed XML document. The SQL Server 2000 OLE DB provider will use the root element of the XML query template as the root element in the resulting XML document. The following is a sample XML query template:

    <Order xmlns:sql='urn:schemas-microsoft-com:xml-sql'>
        <sql:query>
            SELECT OrderID, OrderDate, Freight
            FROM Orders 
            WHERE OrderID = 10248
            FOR XML AUTO
        </sql:query>
    </Order>
    
    
  • To insert, update, or delete data using XML instead of a Transact-SQL query, you can use the OPENXML clause in the XML query template, or use the Updategram and Bulk Load features provided in SQLXML 3.0.

The second part of this paper, Integration with SQL Server, contains several samples that demonstrate how to access data by using the XML query and ADO.

Leveraging SAX2 for Large Data Files

The Simple API for XML (SAX) is an interface that allows you to write applications or application components to read data in an XML document. The SAX2 implementation, the most recently released version of SAX, provides both Microsoft Visual Basic and Microsoft Visual C++® interfaces. All examples in this section are presented in Visual Basic.

The XML Document Object Model (DOM) is one of the most commonly used technologies for processing XML documents. While XML DOM works well for smaller XML documents, it becomes less efficient when handling large documents. This is due to the fact that the DOM needs to break an XML document into individual objects (including elements, attributes, and comments) and create the entire tree structure in memory before the document can be manipulated.

SAX offers a simpler, faster, lower-overhead, and more memory-efficient alternative to the DOM for processing XML documents. A SAX parser does not load an entire XML document into memory. Instead, it starts parsing at the beginning of a document and generates events as it encounters the various elements in the file. As a result, SAX works much better with larger documents, or with documents in which you want to perform a single operation, such as a search.

The distinction between SAX and the DOM is best illustrated by comparing them to the traditional database cursor. SAX is similar to a traditional serial cursor providing read-only and forward-only, while the DOM is representative of a standard database cursor that allows random traversal and both read and write updates.

Best uses of SAX

SAX is best used in the following situations:

  • When your documents are large. The biggest advantage of SAX is that it requires significantly less memory than the DOM to process an XML document. With SAX, memory consumption does not increase with the size of the file. If you need to process large documents, SAX is the better alternative, particularly if you do not need to change the contents of the document because it has already been mapped by BizTalk Server.
  • When you need to stop parsing or document processing. SAX allows you to stop processing at any time. Due to the nature of the data stream, you can create applications that fetch a specific piece of data and then stop processing the file. As a result, the resources required to perform the operation are reduced.
  • When you want to retrieve small amounts of information. Many XML-based solutions require that you retrieve a specific piece of information or data element. It is not necessary to read the entire document to achieve the desired results. With SAX, your application can scan the data stream for specific contents. After the required data component is isolated, it can be passed on as a smaller document.

Limitation of SAX

The limitation of SAX is that it provides no random access to the document. Because the document is not in memory and the data is presented as a stream, you must handle data in the order in which it is processed.

Sample: using SAX

The following sample demonstrates a way to integrate SAX with BizTalk Server through an application integration component (AIC). The sample also shows the key performance benefits of using SAX to process both small (100–200 records) and large (10,000–20,000 records) XML files.

In the sample a BizTalk Server receive function is used to collect XML documents from a predefined location, C:\Temp\SAXSample. The documents are then passed to a standard BizTalk Server channel and port, which are associated with a SAXSample AIC. The AIC uses SAX to sequentially process the XML documents, and uses ADO to insert the records into a SQL Server database table. The following illustration shows this process.

Ee265633.bts_dbintegration_01(en-US,BTS.10).gif

Figure 1.

The main steps for setting up the sample are:

  • Define a SQL Server database table
  • Register the AIC
  • Define a BizTalk Server port and channel
  • Define a BizTalk Server receive function
  • Run the sample

These steps are summarized in the following paragraphs.

Note   All sample files for this section are included in the Samples\SAX2 directory.

Step 1: define a SQL Server database table

The SAX sample uses a Contacts table in the Pubs database to store data provided in the XML documents.

The following script creates the required database table. The script is included in the file CreateContactsTable.sql in the Samples\SAX2\SQLScript samples directory.

use pubs

if exists (select * from dbo.sysobjects where id = 
  object_id(N'[dbo].[Contacts]') and OBJECTPROPERTY(id, N'IsUserTable') = 
  1)
drop table [dbo].[Contacts]
Go

CREATE TABLE [dbo].[Contacts] (
    [CompanyCode] [char] (20), 
    [Name] [varchar] (50),
    [Tel] [varchar] (50),
    [Email] [varchar] (50),
    [RecordTimeStamp] [datetime] not NULL default (GetDate()) 
) ON [PRIMARY]
Go

Using SQL Query Analyzer, define the database and table using the preceding script. Then verify that the table has been created correctly.

Step 2: register the AIC

  1. Click Start, point to Programs, point to Administrative Tools, and then click Component Services. The Component Services Microsoft Management Console (MMC) window appears.
  2. In the Component Services console tree, expand Component Services, expand Computers, expand My Computer, and then click COM+ Applications.
  3. Right-click COM+ Applications, point to New, and then click Application. The COM Application Install Wizard opens
  4. On the Welcome to the COM Application Install Wizard page, click Next.
  5. On the Install or Create a New Application page, click Create an empty application. In the Enter a name for the new application box, type SAXAIC. In the Activation type area, click Server application and then click Next.
  6. On the Set Application Identity page, in the Account area, click Interactive user-the current logged on user, and then click Next.
  7. Click Finish.
  8. In the console tree, expand COM+ Applications and expand the new package you created, called SAXAIC. Click the Components folder.
  9. Browse to the directory Samples\SAX2 and drag AIC4SAX2.dll to the Components folder of package SAXAIC in Component Services.
  10. Close Component Services.
    Note   The Visual Basic source code for AIC4SAX2.dll is provided in the same directory as AIC4SAX2.dll.

Step 3: define a BizTalk Server port and channel

  1. Open Windows Explorer and create the following new directory:

    C:\Program Files\Microsoft BizTalk Server\BizTalkServerRepository\DocSpecs\SAX2

  2. Copy the document specification file Contacts.xml from the Samples\SAX2\SAXSpecsandSamples directory to the new directory.
  3. Click Start, point to Programs, point to Microsoft BizTalk Server 2002, and then click BizTalk Messaging Manager.
  4. On the File menu, point to New, point to Messaging Port, and then click To an Application.
  5. On the General Information page, in the Name box, enter SAXSamplePort, and then click Next.
  6. On the Destination Application page, select Application and click New. The Organization Properties dialog box appears.
  7. In the Organization Properties dialog box, click the Applications tab, click Add, and then enter SAXSampleApplication in the Name box. Click OK twice to return to the Destination Application page.
  8. On the Destination Application page, under Application, select SAXSampleApplication from the drop-down list. In the Primary transport frame, click Browse. The Primary Transport dialog box appears.
  9. In the Primary Transport dialog box, select Application Integration Component from the Transport type list and then click Browse. In the Select a Component dialog box, select AIC4SAX2 Class1 from the list. Click OK twice to return to the Destination Application page. Click Next twice to enter the Security Information page.
  10. On the Security Information page, select Create a channel from this messaging port, and then select From a application from the Channel type list. Click Finish. The New Channel wizard appears.
  11. On the General Information page, enter SAXSampleChannel as the channel name, and then click Next twice to enter the Inbound Document page.
  12. On the Inbound Document page, click New. In the New Document Definition dialog box, type SAXSampleDoc in the Document definition name box. Select Document Specification and click Browse. In the Select a Component dialog box, double-click the SAX2 folder, click Contacts.xml, click Open, and click OK to return to the Inbound Document page. Click Next to enter the Outbound Document page.
  13. On the Outbound Document page, click Browse, select SAXSampleDoc from the list, and then click OK.
  14. Click Next twice, and then click Finish. Finally, close the BizTalk Messaging Manager console.

Step 4: define a BizTalk Server receive function

  1. Open Windows Explorer and create a new directory, C:\Temp\SAXSample, as the inbound directory.
  2. Click Start, point to Programs, point to Microsoft BizTalk Server 2002, and then click BizTalk Server Administration.
  3. In the console tree, expand Microsoft BizTalk Server 2002, expand BizTalk Server Group, and then click Receive Functions.
  4. Right-click Receive Functions, point to New, and then click File Receive Function. The Add a File Receive Function dialog box appears.
  5. In the Add a File Receive Function dialog box, type SAXSampleReceiveFunction in the Name box. Type *.xml in the File types to poll for box. Type C:\Temp\SAXSample in the Polling location box.
  6. Click Advanced and click the Channel name drop-down list. Select SAXSampleChannel from the list. Click OK twice to finish the process.

Step 5: run the sample

  1. Place the sample document SAXSample_Small.xml in the inbound directory C:\Temp\SAXSample. Verify that the new records defined in the XML document are inserted in the Contacts table of the Pubs database.
  2. Repeat step 1 for the document SAXSample_Large.xml and ensure that all records are inserted in the SQL Server database.
Note   The current logged-on user must be a member of the BizTalk Server Administrators group and must remain logged on while running this sample.

Integration with SQL Server

The focus of this part of the article is to discuss methods for integrating BizTalk Server and SQL Server 2000. These methods combine tools to create a way to obtain XML data from SQL Server in a highly scalable manner and with minimal coding effort. It is worth noting that these techniques are generally applicable to other database technologies.

Overview of XML and SQL Server

SQL Server 2000 introduces many built-in features for XML support. These include:

  • The ability to use HTTP publishing functionality
  • The FOR XML clause for querying database tables and receiving the results as an XML document
  • The OPENXML keyword for updating database tables from XML documents

SQL Server 2000 can return the results of SELECT statements as XML documents. The SQL Server 2000 Transact-SQL SELECT statement supports the FOR XML clause, which returns an XML document instead of a relational result set. The OPENXML keyword allows an XML document to be treated in the same way as a table or view in the FROM clause of a Transact-SQL statement. This allows inserting, updating, or deleting data by using an XML document.

The entire SQL Server XML functionality is implemented in SqlXml3.dll. The template files, annotated schema files, Extensible Stylesheet Language (XSL) files, and XPath queries are handled on the Internet Information Services (IIS) server. SqlXml3.dll translates the XPath queries against the annotated schema into SQL commands.

SQL Server HTTP publishing

To use the SQL Server HTTP publishing functionality, you must set up an appropriate virtual directory. You can do this by using the "Configure SQL XML Support in IIS" utility to define and register a new virtual directory. This utility is shipped with SQL Server 2000, and instructs IIS to create an association between the new virtual directory and an instance of Microsoft SQL Server.

A limitation of this approach is that to access SQL Server by using HTTP, XML views of SQL Server 2000 databases must be defined by annotating XML-Data Reduced (XDR) schemas to map the tables, views, and columns associated with the elements and attributes of the schema. The XML views can then be referenced in XPath queries, which retrieve results from the database and return XML documents. This task is a tedious manual process that requires time and effort. There is no out-of-box tool available in SQL Server 2000 to automate this task. Also, this solution is based on the Internet Server Application Programming Interface (ISAPI) and is not scalable because querying SQL Server through ISAPI yields low throughput.

The following illustration shows how HTTP requests are handled.

Ee265633.bts_dbintegration_02(en-US,BTS.10).gif

Figure 2.

You should also consider the potential limitation of this approach. Constructing a FOR XML clause to obtain a hierarchical XML document based on SQL relational tables/views with multiple joins is a time-consuming and error-prone process. Hierarchical XML documents can get very complicated because multiple tables/views must be joined to produce the desired XML document. Some FOR XML statements are more than 30 kilobytes (KB) long. Automating this process requires a tremendous amount of resources.

Loading XML to SQL Server 2000

Microsoft provides the following three ways to update SQL Server 2000 by using data from XML files:

  • The OPENXML keyword. OPENXML ships with SQL Server 2000. It is used for updating data in databases, and is natively supported by SQL Server 2000.
  • XML Updategrams. Updategrams ship with SQLXML 3.0. They give developers an XML-based approach to data modification.
  • XML Bulk Load. XML Bulk Load ships with SQLXML 3.0. It is for loading large amounts of XML data into a database.

To load small amounts (typically 100 KB) of XML data into SQL Server, OPENXML and XML Updategrams are good choices. To load large amounts (typically 100 MB) of XML data, the XML Bulk Load feature is more efficient.

XML Bulk Load is similar in functionality to the bulk copy (bcp) utility and the Transact-SQL BULK INSERT statement. Unlike bcp and BULK INSERT, which accept only tabular data representations, XML Bulk Load supports loading XML hierarchies into one or more database tables. Also unlike OPENXML and XML Updategrams, XML Bulk Load uses Microsoft XML Core Services (MSXML)—formerly called Microsoft XML Parser—to process data rather than parsing the entire XML dataset into memory before processing it. Using the streaming interface lets XML Bulk Load process datasets larger than 100 MB without running out of memory.

Both XML Bulk Load and XML Updategrams are based on annotated schema; these ISAPI-based solutions must go through IIS to complete the task of interchanging XML with SQL Server. Therefore, they are not scalable due to the performance issue.

XML Bulk Load has another limitation. When bulk-loading XML into multiple tables, the rule is that the XML document must include the primary/foreign key values. In practice, these key values are usually missing. For example, the XML purchase order file from customers (which contains Order and Order Details) usually does not contain primary/foreign key values such as OrderID.

Using FOR XML and OPENXML

SQL Server 2000 provides the FOR XML clause and the OPENXML keyword that allow an XML document to be translated into a data format used by a relational database and vice versa. These functions allow XML data to be retrieved from or inserted into a SQL Server table.

FOR XML

Traditionally, the ActiveX Data Objects (ADO) recordset has been widely used to retrieve data from relational databases. SQL Server 2000 extends the SELECT statement to enable the retrieval of data as an XML document through the FOR XML clause.

To use the FOR XML functionality, simply append the keywords FOR XML to a SELECT statement. This indicates to the SQL Server query processor that you want the results to be returned as an XML stream instead of as a recordset. In addition to including the FOR XML keywords, you must also specify a mode to indicate the format of the XML that should be returned. This mode can be specified as RAW, AUTO, or EXPLICIT. For details about the mode, refer to the SQL Server Books Online in the MSDN library.

Within the BizTalk Server context, the FOR XML clause provides a way to retrieve data as XML documents directly without coding. This greatly simplifies the integration between BizTalk Server and SQL Server.

In practice, one of the challenges in using the FOR XML clause is to create an SQL query that will return an XML document that complies with a predefined XML document schema.

In the Using SQL Server XML View Mapper section, we describe an approach that simplifies the task of creating complex FOR XML queries by using the SQL Server XML View Mapper tool.

OPENXML

The OPENXML keyword is used primarily to insert data directly from an XML document into database tables.

Before the OPENXML functionality can be utilized, the XML document provided by BizTalk Server needs to be parsed, validated as XML, and mapped into a tree structure that represents the nodes and elements of the document. This is done through the sp_xml_preparedocument stored procedure.

After the document has been prepared, the SQL Server OPENXML keyword is used to create an in-memory rowset from the data tree created by sp_xml_preparedocument. This rowset can be used anywhere a table or view is used and is therefore ideal for updating or inserting data using an UPDATE or INSERT statement.

After the XML data has been updated or inserted into the database, the sp_xml_removedocument stored procedure should be executed to reclaim the memory used by the node tree.

The OPENXML keyword offers the following benefits:

  • Efficiency is increased as a result of fewer network round trips.
  • The data tier is conceptually simple and easy to code. It no longer needs to be aware of the underlying database structure by sending XML to SQL Server as a single input parameter for a stored procedure. That knowledge now lies in the stored procedures that use OPENXML to map XML nodes to tables, rows, and columns.

Process description

Within the BizTalk Server context, an application integration component (AIC), COM component, or Windows Script Component (WSC) can be used to invoke the XML support in SQL Server. This allows an XML document to be processed through BizTalk Orchestration and BizTalk Messaging and inserted directly into a database. The following illustration shows the overall process.

Ee265633.bts_dbintegration_03(en-US,BTS.10).gif

Figure 3.

In the process, an XML document is passed to BizTalk Server by an AIC, a COM component, or a WSC. ADO is then used to invoke a custom stored procedure, supplying the XML document as a parameter. The custom stored procedure leverages the OPENXML functionality to insert the document into the database.

Sample: using OPENXML

This section presents a BizTalk Server implementation that inserts XML data into SQL Server 2000 by using the OPENXML keyword. The main steps are as follows:

  • Create a stored procedure for generating a SQL Server database table by using the OPENXML keyword and an XML document.
  • Create a stored procedure for inserting XML data into the database table by using the OPENXML keyword.
  • Create a WSC, BTS2SQLOPENXML.wsc, which will be called from BizTalk Orchestration. The WSC accesses the stored procedure to insert XML data into the SQL Server database.
  • Create a BizTalk Orchestration schedule, TestBTS2SQLOPENXML.skv, which passes an XML document to the BTS2SQLOPENXML.wsc component. The WSC inserts the XML data into the database table.
Note   All sample files for this section are included in the Samples\OpenXML directory.

Step 1: define and test the CreateNewTableAndPopulate stored procedure

The OPENXML keyword can be used to create and populate a new database table based on an XML document. The columns of the database table correspond to the elements or attributes of the XML document.

Assume that we have the following purchase order XML document:

<?xml version="1.0"?>
    <Order OrderID="1001" CustomerID="ABC-001"   OrderDate="01/02/2001"
      EmployeeID="001"/>

We want to create a PurchaseOrders table in the Pubs database according to the preceding XML document. Using SQL Query Analyzer, define the Pubs database and use the following script, CreatePOTable.sql to create the PurchaseOrders table. The script is included along with the other SQL scripts for this section in the Samples\OpenXML\SQLScripts directory.

if exists (select * from dbo.sysobjects where id =
  object_id(N'[dbo].[PurchaseOrders]') and OBJECTPROPERTY(id,
  N'IsUserTable') = 1)
drop table [dbo].[PurchaseOrders]
GO

CREATE TABLE [dbo].[PurchaseOrders] (
  [OrderID] [int] NULL ,
  [CustomerID] [nchar] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
  [EmployeeID] [int] NULL ,
  [OrderDate] [datetime] NULL 
) ON [PRIMARY]
GO

Using SQL Query Analyzer, run the following script, CreatePOTableAndPopulate.sql to create a new stored procedure, CreatePOTableAndPopulate.

...
CREATE PROCEDURE CreatePOTableAndPopulate @xmlOrder VARCHAR(2000) AS
DECLARE @iTree INTEGER
EXEC sp_xml_preparedocument @iTree OUTPUT, @xmlOrder
If exists (select * from dbo.sysobjects where id = 
  object_id(N'[dbo].[PurchaseOrders]') and OBJECTPROPERTY(id,
  N'IsUserTable') = 1) drop table [dbo].[PurchaseOrders]
SELECT * INTO PurchaseOrders
FROM OPENXML(@iTree, 'Order', 1)
    WITH (OrderID INTEGER,
        CustomerID nCHAR(8),
        EmployeeID INTEGER,
        OrderDate DATETIME)
EXEC sp_xml_removedocument @iTree
...

Then run the stored procedure, TestCreatePOTableAndPopulate.sql, passing in an XML document as follows:

Exec CreatePOTableAndPopulate '
    <?xml version="1.0"?><Order OrderID="1001" CustomerID="ABC-001" 
      OrderDate="01/02/2001" EmployeeID="001"/>'

After running the stored procedure, browse the Pubs database to verify that a new PurchaseOrders table has been created and the XML data has been inserted correctly.

Step 2: define and test the InsertPurchaseOrder stored procedure

The OPENXML keyword can also be used to insert XML data into columns of a SQL Server database table. In this case the XML data and the database table format must match each other.

For example, we want to insert the following XML data into the PurchaseOrders table created in step 1:

<?xml version="1.0"?>
    <Order OrderID="1012" CustomerID="ABC-005"   OrderDate="08/01/2001"
      EmployeeID="003"/>

We can create the following stored procedure (InsertPurchaseOrder.sql), InsertPurchaseOrder, to perform the task:

...
CREATE PROCEDURE InsertPurchaseOrder @xmlOrder VARCHAR(2000)AS
DECLARE @iTree INTEGER
EXEC sp_xml_preparedocument @iTree OUTPUT, @xmlOrder
INSERT PurchaseOrders (OrderID, CustomerID, EmployeeID, OrderDate)
SELECT * FROM 
    OPENXML(@iTree, 'Order', 1)
    WITH ( OrderID INTEGER,
        CustomerID nCHAR(8),
        EmployeeID INTEGER,
        OrderDate DATETIME)
EXEC sp_xml_removedocument @iTree
...

We then test the InsertPurchaseOrder procedure by using SQL Query Analyzer and the following script, TestInsertPurchaseOrder.sql:

Exec InsertPurchaseOrder '<?xml version="1.0"?><Order OrderID="1012"
  CustomerID="ABC-005" OrderDate="08/01/2001" EmployeeID="003"/>'

After running the preceding script, verify that the XML data has been correctly inserted into the PurchaseOrders table.

Step 3: create and test the BTS2SQLOPENXML.wsc component

To access the SQL Server stored procedure from BizTalk Orchestration, we can either use a WSC, a COM component, or an AIC. In this sample we use a WSC, BTS2SQLOPENXML.wsc, to run the InsertPurchaseOrder stored procedure.

This WSC has one method, ExecSQL_Param, which takes three parameters—a connection string, an SQL query string, and an XML document string. The following code implements the WSC:

<?xml version="1.0"?>
    <component>
    <?component error="true" debug="true"?>
    <registration
        description="BTS2SQLOPENXML"
        progid="BTS2SQLOPENXML.wsc"
        version="1.00"
        classid="{94f80128-269a-4220-bd77-c21abaca4ed3}">
    </registration>
    <public>
        <method name="ExecSQL_Param">
        <PARAMETER name="sCOnn"/>
        <PARAMETER name="sSQL"/>
        <PARAMETER name="sXML"/>
        </method>
    </public>
    <script language="VBScript">
    <![CDATA[
    function ExecSQL_Param(sConn, sSQL, sXML)
        Dim cn
        Dim cmd
        
        'Connect to DB and run SQL
        Set cn = CreateObject("adodb.Connection")
        cn.Open sConn
        
        'Execute procedure
        Set cmd = CreateObject("adodb.command")
        cmd.ActiveConnection = cn
        cmd.CommandText = sSQL & " '" & sXML & "'"
        cmd.Execute
        
        'Clean up
        set cmd = nothing
        set cn = nothing
        ExecSQL = ""
    end function
    ]]>
    </script>
</component>

After using Notepad to define the .wsc file, save it as BTS2SQLOPENXML.wsc to the Sample\OPENXML folder.

Register the component by using regsvr32.dll or by right-clicking the .wsc file in Windows Explorer and clicking Register.

Now test the WSC by using the following Visual Basic Scripting Edition (VBScript) code. Open Notepad, enter the code, and save the file as TestBTSOPENXMLInsertData.vbs.

Dim o
Dim sXML

sXML = "<?xml version=""1.0""?><Order OrderID=""1012"" CustomerID=""ABC-
  002"" OrderDate=""11/01/2001"" EmployeeID=""102""/>"

Set o = CreateObject("BTS2SQLOPENXML.wsc")
o.ExecSQL_Param "Provider=sqloledb; Data Source=(local); Initial
  Catalog=Pubs; Trusted_Connection=Yes;", "exec InsertPurchaseOrder", sXML
MsgBox "Job done."
Set o = nothing

Test the activation and execution of BTS2SQLOPENXML.wsc by running TestBTSOPENXMLInsertData.vbs, and verify that a new row has been added to the PurchaseOrders table in the Pubs database.

Step 4: define and test a BizTalk Orchestration schedule

Now we will build a simple BizTalk Orchestration schedule that passes an XML document to the WSC, which then calls the stored procedure to insert the XML data into the SQL Server database table.

The BizTalk Orchestration schedule has only one action, as shown in the following illustration. The schedule instantiates the BTS2SQLOPENXML.wsc component and calls the ExecSQL_Param method, passing in the three parameters required by the method. One of the parameters is the XML document to be inserted into the SQL Server database.

Click here for larger image

Figure 4. Click thumbnail for larger image.

The main steps for building the sample orchestration schedule are:

  1. Drag an Action shape onto the process and name it Test SQL OPENXML. Add an End shape to the flowchart, and then complete the flowchart as shown in the illustration.
  2. Bind the BTS2SQLOPENXML.wsc to the Action shape by using the Windows Script Component Wizard.
  3. Add three constants in the orchestration data page. Details of the constants are defined in the following table. The XMLString constant contains the XML document to be inserted into the database.
    Constant nameConstant value
    ConnectionStringProvider=sqloledb; Data Source=(local); Initial Catalog=Pubs; Trusted_Connection=yes;
    SQLStringExec InsertPurchaseOrder
    XMLString<?xml version="1.0"?><Order OrderID="1004" CustomerID="ABC-006" OrderDate="01/03/2001" EmployeeID="108"/>
  4. Connect each constant to the corresponding ExecSQL_Param_In message fields by drawing a line from the constant to the input parameter. The following illustration shows the data page of the sample schedule.

    Click here for larger image

    Figure 5. Click thumbnail for larger image.
  5. Save the BizTalk Orchestration schedule as TestBTS2SQLOPENXML.skv to the Sample\OPENXML folder. Compile the schedule to generate the executable file TestBTS2SQLOPENXML.skx. Save the file to the same folder.

To test the orchestration schedule, we can create a .vbs file to launch the schedule as we did in previous sections. We can also use the XLANGMon.exe utility to launch the schedule. Here we use the latter approach for testing.

To test the orchestration schedule

  1. Open Windows Explorer and browse to the folder C:\Program Files\Microsoft BizTalk Server\SDK\XLANG Tools. Double-click XLANGMon.exe. The XLANG Event Monitor window appears.
  2. Right-click the TestBTS2SQLOPENXML.skx file and drag it onto the XLANG Scheduler node. This will run the orchestration schedule.
  3. Verify that a new row of data has been inserted into the PurchaseOrders table in the Pubs database.

Using Updategrams

SQLXML 3.0 includes a major feature, the Updategram, which allows changes to an XML document as database inserts, updates, and deletes. An XML Updategram can be used as the source for a command against the Microsoft OLE DB Provider for SQL Server 2000.

The Updategram is a way to specify an update to a SQL Server 2000 database through XML. You specify what the XML data looks like now and what you want it to look like when the Updategram is executed. The Updategram processor automatically generates and executes the SQL statements required to produce the desired change. Inserts, updates, and deletes can be specified with Updategrams.

The Updategram uses annotated schemas to map XML data to database tables. The annotated schema was introduced in SQL Server 2000 and is supported by BizTalk Server. Leveraging the power of annotated schemas, the Updategram is more flexible and versatile to use than the OPENXML keyword.

Updategrams are submitted for processing through the same mechanisms as all XML SQL templates; that is, they are posted to ISAPI, read from a file specified in a URL, or submitted with an ADO or OLE DB command.

To perform standard SQL commands on the database, the following rules are applied to Updategrams:

  • If a record instance appears only in the <before> block with no corresponding instance in the <after> block, the Updategram performs a DELETE operation.
  • If a record instance appears only in the <after> block with no corresponding instance in the <before> block, the Updategram performs an INSERT operation.
  • If a record instance appears in the <before> block and has a corresponding instance in the <after> block, it is an UPDATE operation. In this case, the Updategram updates the record instance to the value specified in the <after> block.

Updategrams and annotated schemas

An Updategram is based on the xml-updategram namespace and contains one or more sync elements. Each sync element represents a transactional unit of database modifications. Updategrams describe a desired change by specifying what the relevant portion of an XML document looks like before and after the change is made. The mechanism for specifying the change is a <sync> block. Each of these update blocks defines a group of changes that are treated as an atomic unit of work. In terms of atomicity the <sync> block defines the transaction scope for an update.

A <sync> block consists of a <before> block and an <after> block as follows:

  • The <before> block contains the data values that currently exist in an XML document, and is similar to the WHERE clause in a standard SQL statement. As in the SQL WHERE clause, the before information is used to locate which data is to be updated
  • The <after> block contains the new or updated values for the relevant data fields in the database.

The following is an example of an Updategram:

<?xml version="1.0"?>
<employeeupdate xmlns:updg="urn:schemas-microsoft-com:xml-updategram">
    <updg:sync updg:mapping-schema="EmployeeSchema.xml">
        <updg:before>
            <Employee EmpID="1"/>
        </updg:before>
        <updg:after>
            <Employee Phone="555-112233"/>
        </updg:after>
    </updg:sync>
</employeeupdate>

Because an Updategram is an XML document, it can easily be submitted to BizTalk Server as an interchange, and then processed to perform the required database changes.

Sample: using Updategrams

The use of Updategrams is incorporated in the example in the next section.

Using SQL Server XML View Mapper

Microsoft SQL Server XML View Mapper, or XML View Mapper, is a tool that automates the process of generating annotated schema for retrieving XML data from SQL Server.

XML View Mapper, together with other SQL Server utilities, is used to create XML schemas and database queries that retrieve XML data directly from SQL Server. XML View Mapper is also a useful tool for integrating BizTalk Server and SQL Server.

Using XML View Mapper to generate an annotated schema

XML View Mapper is a mapping tool that relates XML-Data Reduced (XDR) schemas and SQL Server database schemas to generate an XML annotated schema. The XML annotated schema enables SQL Server to interact with the database based on established XDR schemas.

Within XML annotated schemas, a set of predefined annotations is used to define the links and relationships between elements and attributes in the XDR schema and tables and fields in a database. XML annotated schemas can be created by using a text editor, but the process is tedious, error prone, time consuming, and difficult to debug.

XML View Mapper provides a simple, visual, declarative, and integrated environment for defining XML views on a database. No coding is required to generate annotated mapping schemas, and utilities are available to validate, test, and export the schemas that are generated. The annotated schemas can be used to support ad hoc queries. They can also be used as the basis of an application that retrieves XML data from a SQL Server database by using the XPath navigation language.

Getting XML data through an annotated schema requires IIS. This approach has a significant performance disadvantage when the volume of data exchange is high. The problem can be overcome by using a stored procedure against SQL Server to generate a targeted XML file instead of passing the annotated schema through the IIS server. This stored procedure contains the FOR XML EXPLICIT clause in the SELECT statement to obtain XML data directly from SQL Server.

Retrieving XML from SQL Server

The FOR XML EXPLICIT clause is one of the most efficient ways to retrieve XML data directly from a SQL Server database. It is, however, not an easy task to create SELECT . . . FOR XML EXPLICIT queries that return the desired XML data when the relationships between data tables become complex.

XML View Mapper can be used to create annotated XML view schemas easily and visually. The XML view schemas generated in XML View Mapper are equivalent to SQL Server mapping schemas. After a schema is generated we can test the schema by executing an XPath query against the schema in the XML View Mapper environment. We then obtain the complex FOR XML query by using the SQL Profiler tool. The following steps summarize the process:

  1. Use XML View Mapper to create an annotated mapping schema for an XDR schema and a database. In practice, you usually get the XDR schema based on the target XML document from BizTalk Server.
  2. Test the mapping schema by using the XPath query tool in the XML View Mapper environment.
  3. Capture the SELECT . . . FOR XML EXPLICIT query by running a SQL Profiler trace and the XPath query.
  4. Extract the SELECT . . . FOR XML EXPLICIT query and insert it into a stored procedure or an ADO query.
  5. Incorporate the stored procedure or the ADO query into a BizTalk Server application through an AIC or WSC.

The following illustration shows the preceding steps.

Ee265633.bts_dbintegration_06(en-US,BTS.10).gif

Figure 6.

Sample: using XML View Mapper

Before running this sample, you need to install XML View Mapper on your computer.

This sample demonstrates using the Updategram and retrieving XML data by using a complex SELECT . . . FOR XML EXPLICIT query from the Northwind database. The query is obtained by using the XML View Mapper tool and SQL Profiler. Both the Updategram and the FOR XML query are created and stored in .xml files.

A WSC component and a BizTalk Orchestration XLANG schedule are also generated for testing the BizTalk Server integration with the SQL Server database.

Note   All sample files for this section are included in the Samples\XMLViewMapper directory.

Step 1: create an annotated mapping schema by using XML View Mapper

If you have not used XML View Mapper, it is a good idea to go through the XML View Mapper tutorial before proceeding with this sample. Because the mapping schemas used in this sample are the same as those in the tutorial, we will bypass the process of creating the mapping schema and open the mapping provided in this sample package.

  1. Browse to the folder Samples\XMLViewMapper and double-click OrderForm.smp. XML View Mapper is launched with the OrderForm project loaded.
  2. In the Project Explorer window, expand Map Modules and double-click OrderForm-map. A mapping screen appears.
  3. Expand the SQL Modules node, right-click Northwind, and then click Database Connection. The Data Link Properties dialog box appears.
  4. Enter the name of the SQL Server computer that you will use and the log on information, and then click Test Connection. Ensure that the test connection is successful.
  5. Click OK to close the dialog box.

Step 2: test the schema by using XPath Query Tester

XPath Query Tester is a utility that comes with XML View Mapper. XPath Query Tester uses the XDR view schema that is opened in XML View Mapper. The results of the query are returned using the element hierarchy specified in the XDR view schema, enclosed in a <root> element. Use the following procedure to test the mapping schema:

  1. On the XML View Mapper Tools menu, click XPath Query Tester. The Schema Load Log dialog box appears.
  2. Click OK. The XPath Query Tester input dialog box appears.

    If the database has not been connected, you will be prompted with a message box. Click OK to enter the Data Link Properties dialog box, enter the SQL Server name and logon password, and then click OK. The XPath Query Tester input dialog box appears.

  3. Type the query Order[@OrderID="10248"] in the XPath Query box, but do not click Execute yet because we need to run SQL Profiler to capture the SQL query string.
  4. Click Start, point to Programs, point to Microsoft SQL Server, and then click Profiler.
  5. Start a new trace in the SQL Profiler screen and accept all the defaults for the new trace.
  6. Click Execute in XPath Query Tester to trigger the SQL trace. The XML data will be retrieved and displayed.

Step 3: get the FOR XML EXPLICIT query and create an XML query

  1. Stop the trace. In the SQL Profiler window, find and select the application name SQL Server XML Mapper. The trace result shows the desired SELECT . . . FOR XML EXPLICIT command that was generated by the XPath query.
  2. Open Notepad and enter the following text:
    <Order xmlns:sql='urn:schemas-microsoft-com:xml-sql'>
        <sql:query>
            BODY
        </sql:query>
    </Order>
    
    
  3. Cut and paste the trace result from the SQL Profiler window to replace the BODY text in the Notepad file.
  4. Save the Notepad file as C:\RetrieveXMLOrder.xml. This XML query file will be used in the sample to retrieve order data (OrderID = 10248) as an XML document. By globally replacing the OrderID "10248" in the query with another OrderID, the query can be used to retrieve details of another order.
  5. Close all open application screens.

Step 4: create an Updategram

  1. Open Notepad and type the following Updategram:
    <?xml version="1.0"?>
    <updateorder xmlns:updg="urn:schemas-microsoft-com:xml-updategram">
        <updg:sync>
            <updg:before>
                <_x005B_Order_x0020_Details_x005D_  OrderID="10249"
                  ProductID="14" />
        </updg:before>
        <updg:after>
            <_x005B_Order_x0020_Details_x005D_ UnitPrice="$15.88" />
        </updg:after>
        </updg:sync>
    </updateorder>
    
    

    This Updategram updates the unit price for order item OrderID = 10249 and ProductID = 14. It is equivalent to the following Transact-SQL statement:

    Update [Order Details] Set UnitPrice=15.88
    Where OrderID=10249 And ProductID=14
    
    
  2. Save the file as C:\UpdateOrder10249.xml.

Step 5: create a WSC component

A WSC component, XMLOrder.wsc, has been created and provided with the sample in the Samples\XMLViewMapper folder. You need to register the component by using regsvr32.dll or by right-clicking the WSC file in Windows Explorer and clicking Register.

The WSC component has three methods:

  • UpdateOrderDetail(UpdategramFile). Executes the Updategram contained in the file UpdateOrder10249.xml
  • GetXMLOrder(OrderID, XMLQueryFile). Retrieves the XML order for a given OrderID using the XML query contained in the file RetrieveXMLOrder.xml
  • WriteToFile(Document, FileName). Writes the retrieved data to a text file specified in the FileName parameter

Step 6: create a BizTalk Orchestration schedule

A BizTalk Orchestration schedule will be created that performs three tasks:

  • Run an Updategram to modify the unit price for order item OrderID=10249 and ProductID=14
  • Retrieve all order items for OrderID=10249 by using a SELECT . . . FOR XML EXPLICIT query
  • Write the retrieved XML data into a file

The following illustration shows the BizTalk Orchestration schedule.

Click here for larger image

Figure 7. Click thumbnail for larger image.

The main steps for building the sample orchestration schedule are:

  1. Drag three Action shapes onto the process and name them Updategram, Retrieve XML Data, and Write Document to File. Add an End shape to the flowchart, and then connect the Action shapes to complete the business process flowchart as shown in the preceding illustration.
  2. Bind XMLOrder.wsc to the Action shapes by using the Windows Script Component Wizard.
  3. Add four constants on the orchestration data page. The following illustration shows details of the constants.

    Ee265633.bts_dbintegration_08(en-US,BTS.10).gif

    Figure 8.

  4. On the data page, connect the constants to the corresponding input parameters of the three data tables. The following illustration shows the completed data flow page.

    Click here for larger image

    Figure 9. Click thumbnail for larger image.

  5. Save the orchestration schedule to the Sample\XMLViewMapper folder as TestXMLOrder.skv.
  6. Compile the orchestration schedule to generate the executable file TestXMLOrder.skx.
  7. Save the file to the Sample\XMLViewMapper folder.

Step 7: run the sample

  1. Click Start, point to Programs, point to Microsoft SQL Server, and then click Enterprise Manager. Ensure that the item [OrderID=10249, ProductID=14] in the Order Details table in the Northwind database has a unit price different from 15.88 (this is the value to be changed by the Updategram in the orchestration). Alter the value if it already has a value of 15.88.
  2. Open Windows Explorer and browse to the folder C:\Program Files\Microsoft BizTalk Server\SDK\XLANG Tools\. Double-click XLANGMon.exe. The XLANG Monitor screen appears.
  3. Open Windows Explorer and browse to the TestXMLOrder.skx file. Right-click the file and drag it onto the XLANG Scheduler node in the XLANG Monitor screen. This runs the orchestration schedule.
  4. Verify that a new XML file, C:\XMLOrderDetails.xml has been created. This file contains XML data retrieved by using the XML query in the file RetrieveXMLOrder.xml. The XML data represents the order with OrderID=10249.
  5. Verify that the unit price for the order item [OrderID=10249, ProductID=14] has been modified to 15.88.

Leveraging Stored Procedures and DTS in SQL Server 2000

This section describes how to use BizTalk Server 2002 to orchestrate workflow among disparate processes. By using the BizTalk Server 2002 Orchestration Designer, you can sequence tasks implemented in SQL Server stored procedures and Data Transformation Services (DTS) packages.

Sample: calling stored procedures from BizTalk Orchestration

This section demonstrates how to call SQL Server stored procedures from BizTalk Orchestration. Because of the number of components involved, we will use a structured example. The following steps are outlined:

  1. Define a SQL Server database table and a stored procedure to call from BizTalk Orchestration.
  2. Use the Windows Script Component Wizard to create a component file, BTS2SQL.wsc, which encapsulates the script code to access SQL Server. This component will have one method, ExecSQL, which will be called from BizTalk Orchestration.
  3. Create a BizTalk Orchestration schedule, TestSQL.skv, which has one action. This action will be bound to an implementation that calls the ExecSQL method in the BTS2SQL.wsc component.
  4. Run the sample.
Note   All sample files for this section are included in the Samples\StoredProcsandDTS\BTS2SQL directory.

Step 1: define and test the LogIt stored procedure

Dependencies for the sample include a SQL Server stored procedure to call and a mechanism for verifying that the procedure was called. Specifically this includes:

  • SQL Server database table—WorkflowMessages
  • SQL Server stored procedure—LogIt

The following script, CreateTableAndLogIt.sql creates the required database table and stored procedure in Pubs, and is included in the Samples\StoredProcsandDTS\BTS2SQL directory:

use pubs
go

create table WorkflowMessages 
(
message varchar(1024) not null,
logged datetime not null default (GetDate())
)
go

Create PROCEDURE LogIt
@strMsg varchar(1024)
AS
insert into WorkFlowMessages (message) values (@strMsg)

grant select on WorkflowMessages to public
grant execute on LogIt to public
go

  1. Using SQL Query Analyzer, define the databases, table, and stored procedure by using the preceding script.
  2. Using SQL Query Analyzer, run the LogIt stored procedure, passing in a string as a parameter:
    EXEC LogIt 'Test String'
    
    
  3. Browse the WorkflowMessages table to verify that the procedure worked properly.

Step 2: define and test the BTS2SQL component

To access SQL Server from BizTalk Orchestration, we will define a Windows Script Component, BTS2SQL.wsc, with one method, ExecSQL. This method takes two parameters, a connection string and an SQL query. It uses ADO to connect to the database and execute the query. The following code implements the script component. The shell was generated with the Windows Script Component Wizard. The ExecSQL code was added by hand.

<?xml version="1.0"?>
<component>

<?component error="true" debug="false"?>

<registration
description="BTS2SQL"
progid="BTS2SQL.WSC"
version="1.00"
classid="{71f80b28-2695-4220-bd77-c21abaca02cb}"
>
</registration>
<public>
<method name="ExecSQL">
<PARAMETER name="sConn"/>
<PARAMETER name="sSQL"/>
</method>
</public>
<script language="VBScript">
<![CDATA[
function ExecSQL(sConn, sSQL)
Dim cn
Dim cmd

'Connect to the db and execute the SQL

    Set cn = CreateObject("ADOdb.connection")
    cn.Open sConn

    Set cmd = CreateObject("ADOdb.command")
    cmd.ActiveConnection = cn
    cmd.CommandText = sSQL
    cmd.Execute

'Clean up
    Set cmd=nothing
    Set cn=nothing
    ExecSQL = ""
end function
]]>
</script>
</component>

  1. Define the WSC file and add the code to the ExecSQL method.
  2. Register the component by using regsvr32.dll or by right-clicking the WSC file in Windows Explorer and clicking Register.
  3. Now we can test the component to ensure that it is functioning properly. The following VBScript code will test the component. Using Notepad, enter the following code and save the file as TestBTS2SQL.vbs:
    dim o
    set o = CreateObject("BTS2SQL.WSC")
    o.ExecSQL "Provider=sqloledb; Data Source=(local);Initial Catalog=Pubs;
      Trusted_Connection=yes;", "exec LogIt 'hello world!' "
    set o = nothing
    
    
  4. Test the activation and execution of BTS2SQL.wsc by running TestBTS2SQL.vbs and then verifying that a row has been added to the WorkflowMessages table in the Pubs database

Step 3: create and test the BizTalk Orchestration schedule

A simple BizTalk Orchestration that has one action is suitable.

This schedule should direct the run-time environment to instantiate a BTS2SQL.wsc component and call the ExecSQL method, passing in parameter values defined in the ExecSQL message. In this test, the values defined for the parameters (sConn and sSQL) on the data page within Orchestration Designer are connected to the values of the parameters in the ExecSQL message.

When creating the sample orchestration, the following points are important to making the sample functional:

  • Make sure that the Implementation shape is attached to the BTS2SQL.wsc component. This can be done by using the Script Component Binding Wizard.
  • Two constants should be included on the orchestration data page: ConnectString and SQLString. These should be mapped to the ExecSQL_in message by drawing a line from the constant to the input parameter.
    Constant nameConstant value
    ConnectionStringProvider=sqloledb; Data Source=(local); Initial Catalog=Pubs; Trusted_Connection=yes;
    SQLStringexec LogIt 'Sample Insert from BizTalk'

The result should look like the included sample in the TestSQL.skv file and the compiled form in TestSQL.skx.

Step 4: Run the BizTalk Orchestration schedule

The following VBScript code is used to run the XLANG schedule. The code is provided in the sample as TestSQLSKV.vbs.

Dim objSked
Dim strPath

strPath = WScript.ScriptFullName
strPath = Mid(strPath, 1, InStrRev(strPath, "\"))
strPath = "sked:///" & strPath & "TestSQL.skx"
set objSked = GetObject(strPath)
msgbox "Started " & strPath

Set oSked = Nothing

To test the component from the BizTalk Orchestration schedule, perform the following steps:

  1. In Windows Explorer, double-click TestSQLSKV.vbs.
  2. After the message box appears, click OK and then go into SQL Enterprise Manager or SQL Query Analyzer to verify that Sample Insert from BizTalk was inserted in the WorkFlowMessages database table.

Sample: calling DTS from BizTalk Orchestration

One of the more efficient ways to access DTS packages from outside SQL Server Enterprise Manager is through the SQL Server DTS package object model. This object model ships with SQL Server 2000 and is a complete set of objects to define and run DTS packages through COM interfaces. To demonstrate how to call DTS packages from BizTalk Orchestration, the following steps will be outlined in this section:

  • Define a DTS package named Copy Titles that copies data from one table to another.
  • Use the Windows Script Component Wizard to create a component file, BTS2DTS.wsc, which encapsulates the script code to access DTS. This component will have one method, RunDTSPackage, which will be called from BizTalk Orchestration.
  • Create a BizTalk Orchestration schedule, TestDTS.skv, which has one action. This action will be bound to an implementation that calls the RunDTSPackage method in the BTS2DTS.wsc component.
  • Create a VBScript file to run the BizTalk Orchestration schedule.
  • Test the BizTalk Server to SQL Server communication.
Note   All sample files for this section are included in the Samples\StoredProcsandDTS\BTS2DTS directory.

Step 1: define and test the DTS package

For testing and demonstration purposes, a DTS package is required to run along with a mechanism for verifying that it was called. This package will be defined as Copy Titles. Functionality provided by the Copy Titles package will duplicate rows from the Titles table of the Pubs database to the Northwind database. The package will have two connection objects, one pointing to the Pubs database and the other pointing to the Northwind database. The package will also have two tasks. First, a Transform Data task will copy the data from the Pubs database to the Northwind database, and then an Execute SQL task will log an entry into our WorkFlowMessages table in the Pubs database to indicate that the data was copied.

Perform the following steps to create the package:

  1. Click Start, point to Programs, point to Microsoft SQL Server, and then click Enterprise Manager.
  2. In the tree control in the left pane, expand Microsoft SQL Servers, expand SQL Server Group, expand [your computer name], expand Data Transformation Services, and then click Local Packages.
  3. In the right pane, right-click and then click New Package.
  4. Create a Microsoft OLE DB Provider for SQL Server and use the Connection Properties to name it Pubs and connect it to the Pubs database.
  5. Repeat the preceding steps to create a second Microsoft OLE DB Provider for SQL Server object, name it Northwind, and connect it to the Northwind database.
  6. Drag a Transform Data task to the design, where Pubs is the source and Northwind is the destination. On the property page, set the Source tab to the Titles table, the Destination tab to the Titles table (which might need to be created), and use the Transformation tab to select all records.
  7. Drag an Execute SQL task to the design surface. On the property page, select the Pubs database, enter Log the Count for the description, and enter the following code as the SQL statement. This will count the number of rows in the Titles table in the Northwind database and write a message to the WorkFlowMessages table in the Pubs database.
    declare @nrows int
    select @nrows = count(*) from Northwind..titles
    declare @sMsg varchar(80)
    select @sMsg = 'There are ' + convert(varchar(8), @nrows) + ' in the
      Northwind titles table'
    exec pubs..LogIt @sMsg
    
    
  8. Click OK to close the property page.
  9. From the design surface, select the Northwind connection object and the Log the Count task. On the Workflow menu, click On Completion.
  10. Save the package in SQL Server, as Copy Titles.
  11. For testing, run the package by clicking Execute on the Package menu. After it is complete, go into SQL Query Analyzer and verify that a message was written to the WorkFlowMessages table and that the Titles table in the Northwind database is populated. Each time you run the package, you should see another message in the WorkFlowMessages table and the size of the Titles table in the Northwind database should grow.

Step 2: define and test the BTS2DTS component

To access DTS from BizTalk Orchestration, we will define a Windows Script Component that encapsulates the code to run DTS tasks. We will define a component named BTS2DTS.wsc with one method, RunPackage. This method takes one parameter, the name of the package to run. The following code implements the script component. The shell was generated with the Windows Script Component Wizard. The RunPackage code was added by hand.

The only part of this script you might need to change is the UseTrustedConnection property on the objExecPkg object. If you typically use a user name and password to gain access to SQL Server, it might be more convenient to set the UseTrustedConnection property to False, and set the ServerUserName and ServerPassword properties of the objExecPkg object to the appropriate values. Similarly, if the instance of SQL Server is on another computer, set the ServerName property appropriately.

<?xml version="1.0"?>
<component>

<?component error="true" debug="false"?>

<registration
description="BTS2DTS"
progid="BTS2DTS.WSC"
version="1.00"
classid="{a9a7f917-35ef-4d45-93f4-3bc935ec75d0}"
>
</registration>

<public>
<method name="RunPackage">
<PARAMETER name="sPackageName"/>
</method>
</public>

<script language="VBScript">
<![CDATA[
function RunPackage(sPackageName)

Dim objPackage
Dim objStep
Dim objTask
Dim objExecPkg

'Create the step and task. Specify the package to be run, and link the
  step to the task.

    Set objPackage = CreateObject("DTS.Package2")
    Set objTask = objPackage.Tasks.New("DTSExecutePackageTask")
    Set objExecPkg = objTask.CustomTask
    objExecPkg.UseRepository = False
    objExecPkg.UseTrustedConnection = True

    objExecPkg.PackageName = sPackageName
    objExecPkg.Name = "ExecPkgTask"

    Set objStep = objPackage.Steps.New
    objStep.TaskName = objExecPkg.Name
    objStep.Name = "ExecPkgStep"
    objStep.ExecuteInMainThread = True

    objPackage.Steps.Add objStep
    objPackage.Tasks.Add objTask

'Run the package

    objPackage.FailOnError = True
    objPackage.Execute

'Release references. Releases must be done before UnInitialize.

    Set objExecPkg = Nothing
    Set objTask = Nothing
    Set objStep = Nothing
objPackage.UnInitialize

end function

]]>
</script>

</component>

To register and add code to the component

  1. Define the WSC file and add the code to the RunPackage method.
  2. Register the component by using regsvr32.dll or by right-clicking the WSC file in Windows Explorer and clicking Register.

Step 3: create and test a BizTalk Orchestration schedule to exercise BTS2DTS

To create this workflow in Orchestration Designer:

  1. Draw the business process action ("Test DTS Package") by dragging the Action shape from the left palette to the design surface. Add an End shape to the design surface and sequence the three steps: Begin, Test DTS Package, End.
  2. Drag the Script Component shape onto the implementation side and browse to BTS2DTS.wsc by using the Script Component Binding Wizard.
  3. Connect the business process action to the implementation port ("Port_1") by using the Method Communication Wizard to create a new message and to make a synchronous method call.

    This schedule directs the run-time environment to instantiate a BTS2DTS.wsc component and call the RunPackage method, passing in parameter values defined in the RunPackage message. For this test, we will define values for the parameter (PackageName) on the data page of the Orchestration Designer and then connect the values to the parameters in the RunPackage message.

  4. Go to the data page in Orchestration Designer and right-click the Constants block. From there, add one constant:
    Name:  DTSPackageName
    Value:Copy Titles
    
    
  5. Map this constant to the RunPackage_in message by drawing a line from the constant to the input parameter.
  6. This file should be saved as, and appears in the sample directory as, the TestDTS.skv file and is compiled into the TestDTS.skx file.

Step 4: create a VBScript file to run the BizTalk Orchestration schedule

The following VBScript code will request the XLANG runtime to start the orchestration. Sample file TestDTSSKV.vbs is provided.

Dim objSked
Dim strPath

strPath = WScript.ScriptFullName
strPath = Mid(strPath, 1, InStrRev(strPath, "\"))
strPath = "sked:///" & strPath & "TestDTS.skx"
set objSked = GetObject(strPath)
msgbox "Started " & strPath

Set oSked = Nothing

Step 5: test the BizTalk Server to SQL Server communication

Now you are ready to test the component from the BizTalk Orchestration schedule.

  1. In Windows Explorer, double-click TestDTSSKV.vbs.
  2. After the message box appears, click OK, and then go into SQL Server Enterprise Manager or SQL Query Analyzer to verify that a message was inserted in the WorkFlowMessages table and the number of rows in the Titles table has increased.

References

Implement SAX2 Classes in VB.

Malcolm, Graeme. Programming Microsoft SQL Server 2000 with XML. Redmond, WA: Microsoft Press, 2001.

XML for SQL Documentation in Microsoft SQL Server 2000 Web Release 1 online documentation.

XML View Mapper Documentation in Microsoft SQL Server XML View Mapper online documentation.

Show:
© 2014 Microsoft