Storing data in an XML file can provide an acceptable format for importing that data into another system. Another use for an XML target is to create messages in a Message Queue to be retrieved by another system. See Capturing Processed XML Messages.
Using an XML file as a target requires some preliminary setup before loading data into that file. The process to setup and use an XML file as a target is as follows:

• Create an XDR file as a schema
• Create the XML Target Connection in the DTS
• Map the fields in the DTS file and run it to populate the XML file

Creating An XDR File
 

To use XML as a target, an XDR or XSD file is required. The easiest way to generate an XDR file is through the Query Publisher in the TIBCO Scribe® Insight Console. In this example the only reason for creating a query publisher is to generate the XDR file.

Create A Query Publisher
 

1. In the TIBCO Scribe® Insight Console, navigate to Integration Server > Publishers/Bridges.
2. Click Add at the top of the screen.
3. Step 1 Publisher/Bridge Type: In the Publisher/Bridge type drop-down select Query Publisher.
4. Give the Publisher a name.
5. Step 2 Source Connection: Click Source Connect and select a source connection. It can be an Adapter or SQL Server. In this example an Account table in SQL Server is used. Connect to SQL Server using ODBC or a Native SQL connection.

6. Step 3 Configure Source: Click Source Configure and select the entity, such as Account.

   

7. Step 4 Publisher/Bridge Properties: Enter a name in the Root Element Name field, such as Account.
8. Enter a message label for queue, such as SQL_Accounts.
9. Click the Grouping button and enter Group information. Refer to the Group The Data section below for steps.
10. Step 5 Activation: Set Status to Paused. You don’t need to run this publisher.
11. Click Finish to save.

Group The Data
 

To create a single XML file containing multiple Account records, the data must be grouped. Grouping is required to generate the proper XML and XDR files. Without grouping a single record is written to the output file.

1. Open your new Publisher and click on Step 4 Publisher/Bridge Properties.
2. Click the Grouping button.
3. Right-click on the Group Name, such as <Accounts>, and select Add Repeating Group.
4. Right-click on <RepeatingElement> and use the rename option to give the label a meaningful name.
5. Click on the top element/node to view the fields.
6. Select the appropriate fields and drag them to the newly created repeating group.
7. In this case, there is not a parent/child relationship so all fields are selected. In the case of a Sales Header/Sales Detail scenario, the sales detail line records would be moved to the repeating group and the sales header fields would remain where they are in the top element.

 

Generate The XDR File
 

The XDR file is crucial to defining the XML file to be created. Once grouping has been defined click the Generate XDR button to create an XDR file used by the DTS. If the XML file you generate later is not correct, return to the Grouping, modify it, and regenerate the XDR file.

 

Create The XML Target Connection In The DTS
 

1. Click the Connections button on the TIBCO Scribe® Insight Workbench Toolbar.
2. In the Connection Manager, click New.
3. Select XML and click OK.
4. On the Connection Wizard click Next, then Use as a Target.
5. Specify the File connection method, the location of the XDR/XSD file, and the name of the output file.

6. On the Workbench click Configure Source.
7. Select a Source database and select the Accounts entity.
8. Click OK to save it.
9. On the Workbench click Configure Steps.
10. Click Add and select the XML Target Connection.
11. Under XML Objects select Accounts.
12. Under Operation select Insert and click Add Insert Step.

13. On the XML Component Target Settings screen select the Accounts label in the tree that is not the top level, but the level that is the equivalent of the Repeating Group.
14. Click the Repeating Node checkbox.
15. In the Node Break box set the AccountID as the Node Break.
16. Click OK to save.

   

Map Fields And Run The DTS

Map the appropriate fields in the DTS and run it.
 
Verify that the XML file is correct.
 
Note On Repeating Nodes

It is important to understand how repeating nodes work in TIBCO Scribe® Insight when using XML files. A repeating node is used to specify which data should be grouped together within the XML file. Typically this is used when multiple records exist in the same XML file. In the example below, the repeating node would be set on the Account node if there were more than one account record in the XML file.
 
TIBCO Scribe® Insight's XML adapter does not handle repeating sibling nodes (nodes at the same level), but can handle multiple repeating child nodes (parent/child relationships).  Setting a Repeating Node on Node 1 and Node 2 on the following is fine.

Example 1

• Top Level
  • Node 1
    • Item
    • Item
    • Node 2
      • Item
      • Item
  • Node 3
  • Node 4

                                 
But the following is not:

• Top Level
  • Node 1
    • Item
    • Item
    • Item
  • Node 2
    • Item
    • Item
    • Item
  • Node 3
  • Node 4

Example 2

In this case there are several customers with multiple addresses and phone numbers. While you can specify Customer and Address as repeating nodes, you cannot also specify Phone Info as a repeating node, because Address and Phone Info are siblings.

Customers
--Customer
----Name
----Addresses
--------Address
------------Street
------------City
------------State
------------Zip
----Phone Numbers
--------Phone Info
------------Number
------------Type

 

Storing data in an XML file can provide an acceptable format for importing that data into another system. Use these instructions to create an XML file.

For additional information, see the following:

TIBCO Scribe® Insight Help: Working With XML Data
Knowledge Base: Capturing Processed XML Messages