Walkthrough: Disassembling Result Sets Using the SQL Adapter
Walkthrough: Disassembling Result Sets Using the SQL Adapter

The SQL adapter does not provide a native way in which to disassemble result sets. BizTalk Server 2006 provides a common way to disassemble results sets for all transports.

In this walkthrough, you will do the following:

  1. Create a BizTalk project.

  2. Use the SQL adapter to generate a schema.

  3. Create an envelope schema.

  4. Deploy the project.

Note that this procedure relies on the sample Northwind database installed with SQL Server.

To create an empty BizTalk Project

  1. Click Start, point to Programs, point to Microsoft Visual Studio 2005, and then click Microsoft Visual Studio 2005.

  2. On the File menu, point to New, and then click Project.

  3. In the New Project dialog box, do the following.

    Use this To do this

    Project Types

    Click BizTalk Projects.

    Templates

    Click Empty BizTalk Server Project.

    Name

    Type DisassembleResults.

  4. Click OK.

    The new DisassembleResults project appears in Solutions Explorer.
To add metadata to the project

  1. In Visual Studio 2005, in Solution Explorer, right-click DisassembleResults, point to Add, and then click Add Generated Items.

  2. In the Add Generated Items – DisassembleResults dialog box, in the Template window, click Add Adapter, and then click Open.

  3. In the Add Adapter Wizard, on the Select Adapter page, select SQL from the list of registered adapters, and click Next.

    You can leave the other entries on the page blank or with their default values, unless you are connecting to a remote SQL Server. If connecting to a remote SQL Server, type the name of the server in the SQL Server box, and the name of the database in the Database box. Leave the Port box empty.

  4. On the Database Information page, click Set.

  5. In the DataLink Properties dialog box, on the Connection tab, do the following.

    Use this To do this

    Select or enter a server name

    Type localhost.

    Enter information to log onto the server

    Select the type of authentication you want.

    Select the database on the server

    Select Northwind.

  6. Click OK.

    This sets the connection string for the SQL adapter.

    Aa562098.note(en-us,BTS.20).gifNote
    This connection is stored only during this wizard. The actual connection will be formed later during the port binding process.

  7. Click Next.

  8. On the Schema Information page, do the following.

    Use This To do this

    Target namespace

    Type http://SchemaTargetNamespace.

    Select the port type

    Select Receive Port.

    Document root element name

    Type RootToRemove.

    Aa562098.note(en-us,BTS.20).gifNote
    Write this information down. You will need it later. Take note that everything you type is case-sensitive, for example, SQLAdapter is not the same as SQLadapter.

  9. Click Next.

  10. On the Statement Type Information page, select Select Statement, and then click Next.

  11. On the Statement Information page, in the SQL Script box, type the following SQL statement: 

    select * from Categories for xml auto, elements
    Aa562098.Important(en-us,BTS.20).gifImportant
    Ensure that you use the for xml auto, elements statement. If you specify only for xml auto without specifying elements then an error will occur when the result set from the SQL statement is returned.

  12. Click Next.

  13. Click Finish to complete the wizard.
To add a new schema to the project

  1. In Solution Explorer, right-click the DisassembleResults project, point to Add, and then click Add New Item.

  2. In the Add New Item - DisassembleResults dialog box, select Schema, in the Name box, type CategoriesEnvelope.xsd, and then click Open.

  3. In the Schema Tree, select the <Schema> node.

  4. In the Properties window, do the following.

    Use this To do this

    Envelope

    Select Yes.

    Target Namespace

    Type http://SchemaTargetNamespace.

  5. Select the Root element.

  6. In the Properties window, do the following.

    Use this To do this

    Node Name

    Type CategoriesEnvelope, which is the same root element as the SQL generated schema.

    Body XPath

    Click the ellipsis (…) button and then click OK. Set the Body XPath to /*[local-name()='CategoriesEnvelope' and namespace-uri()='http://SchemaTargetNamespace']

  7. In the Schema Tree, right-click CategoriesEnvelope, point to Insert Schema Node, and then click Any Element.

  8. In the Properties window, do the following.

    Use this To do this

    Namespace

    Type ##any.

    Process Contents

    Select Lax from the drop-down list.
To modify the SQL adapter generated schema

  1. In Solution Explorer, double-click SQLService.xsd.

  2. Drag and drop, or cut and paste, all of the attributes in the Categories node into the RootToRemove node

  3. Right-click the Categories node, and click Delete.

  4. In the BizTalk Editor dialog box, click Yes to verify that you want to delete the selected node.

  5. Right-click the RootToRemove node, and then click Rename.

    Rename RootToRemove to Categories.
    The schema now looks like the original schema, just the top level RootToRemove node is missing.

  6. Set the Group Max Occurs property to 1.

  7. Set the Group Min Occurs property to 1.
To create a strong key name for the project

  1. Click Start, point to Programs, point to Microsoft Visual Studio 2005, point to Visual Studio Tools, and then click Visual Studio 2005 Command Prompt.

  2. At the command prompt, browse to the DisassembleResults project directory, where you want to create the strong key name.

  3. At the command prompt, type sn -k DisassembleResults.snk, and then press ENTER.

    The confirmation message "Key pair written to DisassembleResults.snk." appears at the command line.

  4. Associate the new .snk file with the assembly by completing the following steps:

    • In Solution Explorer, right-click the DisassembleResults project and click Properties.

    • Click to expand Common Properties and click to select Assembly.

    • Click to select Assembly Key File in the right hand pane of the DisassembleResults Property Pages dialog box.

    • Click the ellipses (…) button to display the Assembly Key File dialog box, browse to DisassembleResults.snk, and click OK.
To exclude the orchestration from the project

  • In Solution Explorer, right-click BizTalk Orchestration.odx, and then click Exclude from Project.
To build and deploy the solution

  1. In Visual Studio 2005, on the Build menu, click Build Solution.

  2. In the Clean Up Global Data Types dialog box, check CategoriesType and then click OK.

  3. In Solution Explorer, right click Solution 'Disassemble Results' (1 project), and click Configuration Manager.

  4. In the right pane, in the Deploy column, select the checkbox to add this project to your solution, and then click Close.

  5. On the Build menu, click Deploy Solution.
To create a receive port and receive location

  1. In Visual Studio 2005, on the View menu, click BizTalk Explorer.

  2. In BizTalk Explorer, right-click BizTalk Configuration Databases, and then click Refresh.

    Aa562098.note(en-us,BTS.20).gifNote
    The BizTalk Management database is also referred to as the BizTalk Configuration database.

  3. Right-click Receive Ports, and then click Add Receive Port.

  4. In the Create New Receive Port dialog box, select One-Way Port, and then click OK.

  5. On the One-Way Receive Port Properties - Configurations - General dialog box, in the Name box, type DisassembleResultsReceivePort, accept the defaults, and then click OK.

    The DisassembleResultsReceivePort receive port now appears in BizTalk Explorer.

  6. In the new DisassembleResultsReceivePort node, right-click Receive Locations, and then click Add Receive Location.

  7. In the Receive Location Properties - Configurations - General dialog box, do the following:

    Use this To do this

    Name

    Type DisassembleResultsReceiveLocation.

    Transport Type

    Select SQL.

    URI

    Click the ellipsis (...) button and then proceed to step 8.

  8. In the SQL Transport Properties dialog box, do the following:

    Use this To do this

    Polling Unit of Measure

    Select Hours from the Polling Unit of Measure drop-down menu.

    Connection String

    Click the ellipsis (...) button to open the Data Link Properties dialog box.

  9. In the Data Link Properties dialog box, on the Connection tab, do the following:

    Use this To do this

    Select or enter a server name

    Type localhost.

    Enter information to log onto the server

    Select Use Windows NT Integrated security.

    Select the database on the server

    Select Northwind from the drop-down list.

  10. Click OK.

  11. Back in the SQL Transport Properties dialog box, do the following:

    Use this To do this

    SQL Command

    Click the ellipsis (...) to open the Import information from a generated schema dialog box.

  12. On the Import information from a generated schema dialog box, do the following:

    Use this To do this

    Project

    Select DisassembleResults.

    Schema

    Select DisassembleResults.SQLService.

  13. Click OK to close the Import information from a generated schema dialog box.

  14. Back in the SQL Transport Properties dialog box, do the following:

    Use this To do this

    Document Root Element Name

    Type CategoriesEnvelope.

    Document Target Namespace

    Ensure the value is set to http://SchemaTargetNamespace.

  15. Click OK to close the SQL Transport Properties dialog box.

  16. On the Receive Location Properties – Configurations – General dialog box, do the following:

    Use this To do this

    Receive Handler

    Select BizTalkServerApplication.

    Receive Pipeline

    Select Microsoft.BizTalk.DefaultPipelines.XMLReceive.

  17. Click OK.
To create a destination folder

  • Use Windows Explorer to create an empty folder that you will use as the destination location. For example, C:\Output.
To create a send port

  1. In BizTalk Explorer, right-click Send Ports, and then click Add Send Port.

  2. In the Create New Send Port dialog box, select Static One-Way Port, and then click OK.

  3. On the Static One-Way Send Port Properties - Configurations - Transport - Primary dialog box, do the following.

    Use this To do this

    Name

    Type DisassembleResultsSendPort.

    Transport Type

    Select File.

    Address (URI)

    Click the ellipsis (...) to open the File Transport Properties dialog box.

  4. In the File Transport Properties dialog box, do the following.

    Use this To do this

    Destination folder

    Enter the path to the destination folder, for example C:\Output.

  5. Click OK.

  6. In the Static One-Way Send Port Properties - Configurations - Transport - Primary dialog box, in the left pane, expand the Send folder, and then click General.

  7. On the Static Solicit-Response Send Port Properties - Configurations - Send - General dialog box, do the following.

    Use this To do this

    Send Pipeline

    Select Microsoft.BizTalk.DefaultPipelines.PassThruTransmit.

  8. In the Static One-Way Send Port Properties - Configurations - Transport - Primary dialog box, in the left pane, expand the Filters & Maps folder, and then click Filters.

  9. In the right pane, do the following:

    Use this To do this

    Property

    Select BTS.ReceivePortName.

    Operator

    Select ==.

    Value

    Type DisassembleResultsReceivePort.

  10. Click OK.
To start the send port

  • In BizTalk Explorer, right click DisassembleResultsSendPort, and then click Start.
To enable the receive location

  • In BizTalk Explorer, right click DisassembleResultsReceiveLocation, and then click Enable.

    Shortly after enabling the receive location, messages will be received by the destination folder you specified.

    The following code shows what the beginning of the document would have looked like without disassembly: 

      <?xml version="1.0" encoding="utf-16" ?> 
<U>-</U> <CategoriesEnvelope xmlns="http://SchemaTargetNamespace">
<U>-</U> <Categories>
  <CategoryID>1</CategoryID> 
  <CategoryName>Beverages</CategoryName> 
  <Description>Soft drinks, coffees, teas, beers, and ales</Description> 
  <Picture>dbobject/Categories[@CategoryID='1']/@Picture</Picture> 
  </Categories>
<U>-</U> <Categories>
  <CategoryID>2</CategoryID> 
  <CategoryName>Condiments</CategoryName> 
  <Description>Sweet and savory sauces, relishes, spreads, and seasonings</Description> 
  <Picture>dbobject/Categories[@CategoryID='2']/@Picture</Picture> 
  </Categories>
<U>-</U> <Categories>
  <CategoryID>3</CategoryID> 
  <CategoryName>Confections</CategoryName> 
  <Description>Desserts, candies, and sweet breads</Description> 
  <Picture>dbobject/Categories[@CategoryID='3']/@Picture</Picture> 
  </Categories>
<U>-</U> <Categories>
  <CategoryID>4</CategoryID> 
  <CategoryName>Dairy Products</CategoryName> 
  <Description>Cheeses</Description> 
  <Picture>dbobject/Categories[@CategoryID='4']/@Picture</Picture> 
  </Categories>

    The following code shows the same document after disassembly: 

    - </Categories xmlns="http://SchemaTargetNamespace">
  <CategoryID>8</CategoryID> 
  <CategoryName>Seafood</CategoryName> 
  <Description>Seaweed and fish</Description> 
  <Picture>dbobject/Categories[@CategoryID='8']/@Picture</Picture> 
  </Categories>
© 2009 Microsoft Corporation. All rights reserved.   Terms of Use | Trademarks | Privacy Statement
Page view tracker
Rate the Lightweight library
x
Lightweight builds on ScriptFree (loband) by adding features you've requested: a SearchBox and default code language selection.
Do you like the SearchBox?
Do you like the tabbed code blocks?
How useful is this topic?
Tell us more.
Thanks
x
You're helping to improve MSDN Online.
Feedback
Switch View
x
Classic
Lightweight Beta
ScriptFree
Switch View