Using XQuery to Merge Source File Data

As described in <xHyperlink>order.pipeline Requirements, the data required for our report comes from two files - a text file, booksXML.txt, and an EDI file, order.edi. Both files have the book's ISBN, and we will use that number to select matching data. This will provide us with the following information required by our report:

To join data from these different documents we will use an XQuery document created using the XQuery Mapper. Once we have defined that XQuery document, we can add the XQuery node to our XML pipeline.

Using Variables to Reference Data Sources

Because we want our XQuery to be easy to parameterize, we will create it using external variables to reference our two data sources, booksXML.txt and order.edi. Once the data sources for the XQuery are defined, we can use the XQuery Mapper to map desired nodes from these source documents to nodes in the XML Schema that represents the structure to which the resulting XML must conform.

If you open createFullOrder.xquery, which is in the pipelines\order folder of the examples project where you installed Stylus Studio, you can see how this was done. As shown in Figure 429, createFullOrder.xquery uses variable declarations, $ediOrder and $allBooks, for the non-XML data sources. DataDirect XML Converters, which are built in to Stylus Studio 2009 XML Enterprise Suite, convert these data sources to XML on-the-fly, any time the XQuery (or an XML pipeline that uses the XQuery) is executed. Thus, if the data in either booksXML.txt and order.edi changes, the report resulting from the XML pipeline will change, too.

To use non-XML as a source document for XQuery Mapper:

To specify a non-XML data source as an XML source for the XQuery Mapper, you

1. Click the Add Source Document button at the top of the XQuery Mapper tab.

This displays the Open dialog box.

2. You select the file you want to open, and then select the Open using XML Converter check box.

3. When you click Open, Stylus Studio displays the Select XML Converter dialog box (see Figure 427), which you use to select the built-in DataDirect XML Converter you want to use to convert your non-XML file to XML. Note that this is the same procedure we used to identify the data sources for the XML pipeline's two ConvertToXML nodes.
4. When you click OK on the Select XML Converter dialog box, Stylus Studio adds it as a data source in the Add Source Document pane of the XQuery Mapper.

Next, we need to create global variables for the two source documents. This way, the XQuery code that Stylus Studio generates will use variable declarations instead of document functions to reference our data sources.

To associate the source schema with a global variable:
1. Right click the source document name and select Associate With > Global Variable.

Stylus Studio displays the Associate Schema with Variable dialog box.

When a variable is created (allBooks, for example), Stylus Studio creates a declaration like the following in the XQuery source code:

declare variable $allBooks as document-node() external; 

Looking at the XQuery Code

Before moving on with the XML pipeline creation, let's a quick look at the XQuery code in createFullOrder.xquery:

declare variable $ediOrder as document-node() external; 
declare variable $allBooks as document-node() external; 
  
<root> 
	{ 
	for $GROUP_28 in $ediOrder/EDIFACT/ORDERS/GROUP_28, 
	    $row in $allBooks/table/row 
	where $GROUP_28/LIN/LIN03/LIN0301/text() = $row/isbn/text() 
	return 
	<book> 
		<title> 
			{$row/title/text()} 
		</title> 
		<quantity> 
			{$GROUP_28/QTY/QTY01/QTY0102/text()} 
		</quantity> 
		<ISBN> 
			{$GROUP_28/LIN/LIN03/LIN0301/text()} 
		</ISBN> 
	</book> 
	} 
</root> 

The first two lines contain the variable declarations for booksXML.txt and order.edi, our two source files. A FLWOR block (For, Let, Where, Order by, Return) matches ISBN numbers from order.edi with those in books.xml, and when it finds a match, it returns

All of this code was created automatically as a result of mapping nodes from our source documents to nodes in an XML Schema, fullOrder.xsd, which was provided by the organization in our enterprise that requested the inventory report - all XML resulting from the createFullOrder.xquery must conform to this XML Schema.

See Building an XQuery Using the Mapper for more information on using the XQuery Mapper.

 
Free Stylus Studio XML Training: