Merging XML Data from Multiple Sources

www.altova.com Expand/Collapse All Print this Topic Previous Page Up One Level Next page

Home >  SPS File: Structure >

Merging XML Data from Multiple Sources

XML data from multiple source XML files can be merged when XSLT 2.0 or 3.0 is used as the XSLT version of the SPS.

 

Typically, the merging of data will be based on a common piece of data, such as an ID. For example, an employee in a company, who is identified by a personal ID number, can have his or her personal data stored in multiple XML files held by the personnel department: (i) personal details, (ii) payroll, (iii) work and leave, (iv) courses attended, etc. Data from these different files can be merged in a single output document using the personal ID number as a key.

 

Note:The Enterprise Edition enables you to include multiple schema sources, so XML nodes from other schemas can be selected using the parameter name for the corresponding schema (as is the case in the example below). In the Professional and Basic Editions, the doc() function of XPath 2.0 can be used to locate the required XML file and the XML node within that file. The doc() function of XPath 2.0 provides access to the document root of external XML documents, and thus enables node content from external XML documents to be inserted in the output. An Auto-Calculation that uses the doc() function can, therefore, also be used to merge XML data (see example below).

 

Example

The (My) Documents folder, C:\Documents and Settings\<username>\My Documents\Altova\StyleVision2019\StyleVisionExamples, contains an example SPS file (MergeData_2_Files.sps) that shows how data from different source XML files can be merged. The SPS selects data from an order (MergeOrder.xml, listed below) that a fictitious customer places.

 

<?xml version="1.0" encoding="UTF-8"?>

<Order xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="MergeOrder.xsd">

   <Item partNum="238-KK" quantity="3" shipDate="2000-01-07" comment="With no inclusions, please."/>

   <Item partNum="748-OT" quantity="1" shipDate="2000-02-14" comment="Valentine's day packaging."/>

   <Item partNum="229-OB" quantity="1" shipDate="1999-12-05"/>

   <Item partNum="833-AA" quantity="2" shipDate="1999-12-05" comment="Need this for the holidays!"/>

</Order>

 

The value of the /Order/Item/@partNum attribute in this file (see above) is used to select the ordered products from the catalog of articles stored in another file, MergeArticles.xml (see listing below).

 

<?xml version="1.0" encoding="UTF-8"?>

<Articles xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="MergeArticles.xsd">

   <Article PartNum="833-AA">

      <ProductName>Lapis necklace</ProductName>

      <Price>99.95</Price>

   </Article>

   <Article PartNum="748-OT">

      <ProductName>Diamond heart</ProductName>

      <Price>248.90</Price>

   </Article>

   <Article PartNum="783-KL">

      <ProductName>Uncut diamond</ProductName>

      <Price>79.90</Price>

   </Article>

   <Article PartNum="238-KK">

      <ProductName>Amber ring</ProductName>

      <Price>89.90</Price>

   </Article>

   <Article PartNum="229-OB">

      <ProductName>Pearl necklace</ProductName>

      <Price>4879.00</Price>

   </Article>

   <Article PartNum="128-UL">

      <ProductName>Jade earring</ProductName>

      <Price>179.90</Price>

</Article>

 ...

</Articles>

 

The way the merging of the data is done is to set up a User-defined template within the /Order/Item template (see screenshot below) that selects the corresponding Article element in the MergeArticles.xml file by using the part number of the ordered item to identify the article. The XPath expression (which is in the /Order/Item context) is: $Articles//Article[@PartNum=current()/@partNum]

 

MergeXMLData

 

This template will produce output something like that shown in the screenshot below.

 

MergeXMLDataOutput

 

Notice that while the quantity ordered of each item is taken from the file MergeOrder.xml, the name of the ordered article is taken from the file MergeArticles.xml. Also notice how the ProductName node is selected within the context of the /Articles/Article template.

 

The same result as that obtained above could also be achieved using an Auto-Calculation (see screenshot below). Drag the quantity attribute from the Schema Tree window and create it as contents. Then add an Auto-Calculation as shown in the screenshot and give the Auto-Calculation an XPath expression as described below.

 

MergeXMLDataAutoCalc

 

The XPath expression of the Auto-Calculation could target the required node using either the parameter of another schema source or the doc() function:

 

 $Articles//Article[@PartNum=current()/@partNum]/ProductName

 

or

 

 doc('MergeArticles.xml')//Article[@PartNum=current()/@partNum]/ProductName

 

Notice that, while the first XPath expression above uses a parameter to refer to another XML Schema (a feature available only in the Enterprise Edition), the second expression uses the doc() function of XPath 2.0 (a feature available in the Professional and Basic editions as well).

 

 

 


© 2019 Altova GmbH