Implementing message flow security in IIB

For this purpose we need to complete below steps -

1. Set up a public key infrastructure 

2. Configuring Broker/integration server to use SSL

3. Create a message flow

4. Test the configuration


---------------------------------------------------

1. Set up a public key infrastructure 


To enable SSL communication, and Web Services Security, configure

• keystores, 
• truststores, 
• passwords, and 
• certificates 

uses the command line tool options, which are supplied with the IBM Integration Bus JVM, include the following:

• A command-line tool, keytool.
• A graphical tool, iKeyman.

To create the infrastructure, complete the following tasks:

1. Creating a keystore file or a truststore
2. Creating a self-signed certificate for test use
3. Importing a certificate for production use
4. Viewing details of a certificate
5. Extracting a certificate
6. Adding a signer certificate to the truststore
7. Listing all certificates in a keystore
8. Configuring PKI at broker level
9. Configuring PKI for the broker-wide HTTP listener
10. Configuring PKI at an integration server level

1. Creating a kestore file or a truststore :

runmqckm -keydb -create -db "C:\Program Files\IBM\IIB\10.0.0.12\ibkeystore.jks" -pw ibpass  -type jks

runmqckm -keydb -create -db "C:\Program Files\IBM\IIB\10.0.0.12\ibtruststore.jks" -pw ibpass -type jks

2. Creating a self-signed certificate for test use

runmqckm -cert -create -db "C:\Program Files\IBM\IIB\10.0.0.12\ibkeystore.jks" -pw ibpass -label ibcert -dn "CN=IIB.ABC.com, O=ABC, OU=

IIB, L=Vizag, C=IN"

3. Viewing details of a certificate

runmqckm -cert -details -db ibkeystore.jks -pw ibpass -label ibcert 

4. Extracting a certificate

Certificates can be extracted in two formats:

• Base64-encoded ASCII data (.arm). This format is convenient for inclusion in XML messages, and transmission over the Internet.
• Binary DER data (.der).

runmqckm -cert -extract -db ibkeystore.jks -pw ibpass -label ibcert -target ibcert.arm -format ascii

5. Adding a signer certificate to the truststore

runmqckm -cert -add -db ibtruststore.jks -pw ibpass -label ibcert -file ibcert.arm -format ascii

runmqckm -cert -details -db ibtruststore.jks -pw ibpass -label ibcert

6. Listing all certificates in a keystore

runmqckm -cert -list -db ibtruststore.jks -pw ibpass

---------------------------------------------------

7. Configuring PKI at broker level

Define the broker registry properties that identify the location, name, and password of the keystore and truststore files.

These settings are used as the default settings for the broker-wide HTTP listener and all embedded HTTP listeners in integration servers on the broker.

1. Start the broker:

mqsistart IBD01

2. Display the current settings of the broker registry properties:

mqsireportproperties IBD01 -o BrokerRegistry –r

C:\Program Files\IBM\IIB\10.0.0.12\tools>mqsireportproperties IBD01 -o BrokerRegistry -r

BrokerRegistry

  uuid='BrokerRegistry'

  brokerKeystoreType='JKS'

  brokerKeystoreFile=''

  brokerKeystorePass='brokerKeystore::password'

  brokerTruststoreType='JKS'

  brokerTruststoreFile=''

  brokerTruststorePass='brokerTruststore::password'

  brokerCRLFileList=''

  httpConnectorPortRange=''

  httpsConnectorPortRange=''

  brokerKerberosConfigFile=''

  brokerKerberosKeytabFile=''

  allowSSLv3=''

  allowSNI=''

  reenableTransportAlgorithms=''

  reenableCertificateAlgorithms=''

  mqCCDT=''

  modeExtensions=''

  operationMode='advanced'

  adminMessageLogging=''

  productFunctionality=''

  mqKeyRepository=''

  dataCapturePolicyUri='/apiv1/policy/DataCapture/default'

  shortDesc=''

  longDesc=''

BIP8071I: Successful command completion.

3. Set the keystore property:

mqsichangeproperties IBD01 -o BrokerRegistry -n brokerKeystoreFile -v "C:\Program Files\IBM\IIB\10.0.0.12\ibkeystore.jks"

C:\Program Files\IBM\IIB\10.0.0.12\tools>mqsichangeproperties IBD01 -o BrokerRegistry -n brokerKeystoreFile -v "C:\Program Files\IBM\IIB\10.0.0.12\ibkeystore.jks"

BIP8071I: Successful command completion.

4. Set the truststore property:

mqsichangeproperties IBD01 -o BrokerRegistry -n brokerTruststoreFile -v "C:\Program Files\IBM\IIB\10.0.0.12\ibtruststore.jks"

C:\Program Files\IBM\IIB\10.0.0.12\tools>mqsichangeproperties IBD01 -o BrokerRegistry -n brokerTruststoreFile -v "C:\Program Files\IBM\IIB\10.0.0.12\ibtruststore.jks"

BIP8071I: Successful command completion.

5. Stop the broker:

mqsistop IBD01

6. Set the password for the keystore:

mqsisetdbparms IBD01 -n brokerKeystore::password -u ignore -p ibpass

7. Set the password for the truststore:

mqsisetdbparms IBD01 -n brokerTruststore::password -u ignore -p ibpass

8. Start the broker:

mqsistart IBD01

9. Display and verify the broker registry properties:

mqsireportproperties IBD01 -o BrokerRegistry -r

C:\Program Files\IBM\IIB\10.0.0.12\tools>mqsireportproperties IBD01 -o BrokerRegistry -r

BrokerRegistry

  uuid='BrokerRegistry'

  brokerKeystoreType='JKS'

  brokerKeystoreFile='C:\Program Files\IBM\IIB\10.0.0.12\ibkeystore.jks'

  brokerKeystorePass='brokerKeystore::password'

  brokerTruststoreType='JKS'

  brokerTruststoreFile='C:\Program Files\IBM\IIB\10.0.0.12\ibtruststore.jks'

  brokerTruststorePass='brokerTruststore::password'

  brokerCRLFileList=''

  httpConnectorPortRange=''

  httpsConnectorPortRange=''

  brokerKerberosConfigFile=''

  brokerKerberosKeytabFile=''

  allowSSLv3=''

  allowSNI=''

  reenableTransportAlgorithms=''

  reenableCertificateAlgorithms=''

  mqCCDT=''

  modeExtensions=''

  operationMode='advanced'

  adminMessageLogging=''

  productFunctionality=''

  mqKeyRepository=''

  dataCapturePolicyUri='/apiv1/policy/DataCapture/default'

  shortDesc=''

  longDesc=''

BIP8071I: Successful command completion.

8. Configuring PKI for the broker-wide HTTP listener

Define the properties for the broker-wide HTTP listener to identify the location, name, and password of the keystore and truststore files.

These settings override any PKI configuration that is set at the broker level. 

If you enable SSL on the broker-wide HTTP listener but do not set the following properties, then the broker-level settings are applied

1. Start the broker.

mqsistart IBD01

2. Display the current settings of the broker-wide listener properties.

mqsireportproperties IBD01 -b httplistener -o HTTPSConnector -a

3. Set the keystore property.

mqsichangeproperties IBD01 -b httplistener -o HTTPSConnector -n keystoreFile -v "C:\Program Files\IBM\IIB\10.0.0.12\ibkeystore.jks"

4. Set the truststore property.

mqsichangeproperties IBD01 -b httplistener -o HTTPSConnector -n truststoreFile -v "C:\Program Files\IBM\IIB\10.0.0.12\ibtruststore.jks"

5. Set the password for the keystore.

mqsichangeproperties IBD01 -b httplistener -o HTTPSConnector -n keystorePass -v ibpass

6. Set the password for the truststore.

mqsichangeproperties IBD01 -b httplistener -o HTTPSConnector -n truststorePass -v ibpass

7. Display and verify the broker-wide listener properties.

mqsireportproperties IBD01 -b httplistener -o HTTPSConnector -a

9. Configuring PKI at an integration server level

Define the ComIbmJVMManager properties for the required integration server to identify the location, name, and password of the keystore and truststore files.

These settings override any PKI configuration that is set at the broker level. 

If you enable SSL on an embedded HTTP listener but do not set the following properties, then the broker-level settings are applied.

1. Start the broker.

mqsistart IBD01 

2. Display the current settings of the ComIbmJVMManager properties.

mqsireportproperties IBD01 -e Test -o ComIbmJVMManager -r

3. Set the keystore property.

mqsichangeproperties IBD01 -e Test -o ComIbmJVMManager -n keystoreFile  -v ""

4. Set the keystore password key property. The value for this property is in the format any_prefix_name::password. This value is used to correlate the password that is defined in the mqsisetdbparms command.

mqsichangeproperties IBD01 -e Test -o ComIbmJVMManager -n keystorePass -v TestKeystore::password

5. Set the truststore property.

mqsichangeproperties IBD01 -e exec_grp_name -o ComIbmJVMManager -n truststoreFile -v ""

6. Set the truststore password key property. The value for this property is in the format any_prefix_name::password. This value is used to correlate the password that is defined in the mqsisetdbparms command.

mqsichangeproperties IBD01 -e Test-o ComIbmJVMManager -n truststorePass -v TestTruststore::password

7. Stop the broker.

mqsistop IBD01 

8. Set the password for the keystore.

mqsisetdbparms IBD01 -n TestKeystore::password -u ignore -p keystore_pass

9. Set the password for the truststore.

mqsisetdbparms IBD01 -n TestTruststore::password -u ignore -p truststore_pass

10. Start the broker.

mqsistart IBD01 

11. Display and verify the ComIbmJVMManager properties.

mqsireportproperties IBD01 -e Test -o ComIbmJVMManager -r

***********************************************************

2. Configuring Broker/integration server to use SSL

Configuring the broker to use SSL

Complete the following steps:

1. Turn on SSL support in the broker, by setting a value for enableSSLConnector

mqsichangeproperties IBD01  -b httplistener -o HTTPListener -n enableSSLConnector -v true

C:\Program Files\IBM\IIB\10.0.0.12\tools>mqsireportproperties IBD01  -b httplistener -o HTTPListener -a

HTTPListener

  uuid='HTTPListener'

  enableSSLConnector='false'

  threadPoolSize=''

  traceOverrideLevel=''

  traceOverrideSize=''

  traceLevel='none'

  traceSize=''

  javaDebugPort=''

  allowSSLv3=''

  startListener='true'

BIP8071I: Successful command completion.

2. Optional: If you do not want to use the default port 7083 for HTTPS messages, specify the port on which the broker listens:

mqsichangeproperties IBD01 -b httplistener -o HTTPSConnector -n port -v Port 

On UNIX systems, only processes that run under a privileged user account (in most cases, root) can bind to ports lower than 1024.

For the broker to listen on these ports, the user ID under which the broker is started must be root.

3. Optional: Enable Client Authentication (mutual authentication):

mqsichangeproperties IBD01 -b httplistener -o HTTPSConnector -n clientAuth -v true

4. Restart the broker after changing one or more of the HTTP listener properties.

5. Optional: Use the following commands to display HTTP listener properties:

mqsireportproperties IBD01 -b httplistener -o AllReportableEntityNames -a 

mqsireportproperties IBD01 -b httplistener -o HTTPListener -a 

mqsireportproperties IBD01 -b httplistener -o HTTPSConnector  -a

Configuring an integration server to use SSL

Complete the following steps:

1. Optional: Specify a specific port on which the integration server listens for HTTPS requests, or leave the value unset to use the next available port number.

mqsichangeproperties IBD01 -e Test -o HTTPSConnector -n explicitlySetPortNumber -v port_number

On UNIX systems, only processes that run under a privileged user account (in most cases, root) can bind to ports lower than 1024. For the integration server to listen on these ports, the user ID under which the broker is started must be root.

If you do not complete this step, the first available port in the default range (7843 - 7884) is used.

2. Optional: Enable Client Authentication (mutual authentication):

mqsichangeproperties IBD01 -e Test -o HTTPSConnector -n clientAuth -v true

3. Optional: Change the SSL protocol. The default protocol for the integration server's HTTPS connections is TLS. Run the following command to change it to SSL:

mqsichangeproperties IBD01 -e Test -o HTTPSConnector -n sslProtocol -v SSL

4. Restart the broker after changing one or more of the listener properties.

5. Optional: Use the following command to display HTTPS properties:

mqsireportproperties IBD01 -e Test -o HTTPSConnector  -r

Handling SQL Exceptions

There are couple of ways --

1. Using ESQL to access database state indicators


DECLARE SQLState1 CHARACTER;   
DECLARE SQLErrorText1 CHARACTER;   
DECLARE SQLCode1 INTEGER;   
DECLARE SQLNativeError1 INTEGER;  
  
-- Make a database insert to a table that does not exist --
INSERT INTO Database.DB2ADMIN.NONEXISTENTTABLE (KEY,QMGR,QNAME) 
                              VALUES (45,'REG356','my TESTING 2');

--Retrieve the database return codes --
SET SQLState1 = SQLSTATE;
SET SQLCode1 = SQLCODE;
SET SQLErrorText1 = SQLERRORTEXT;
SET SQLNativeError1 = SQLNATIVEERROR;

--Use the THROW statement to back out the database and issue a user exception--
THROW USER EXCEPTION MESSAGE 2950 VALUES
( 'The SQL State' , SQLState1 , SQLCode1 , SQLNativeError1 , 
SQLErrorText1 );


2. Using  handler


DECLARE HANDLER statement creates an error handler for handling exceptions.


If processing of the handler's code completes without throwing further unhandled exceptions, execution of the normal code is resumed as follows:

• For EXIT handlers, the next statement processed is the first statement after the handler's scope.
• For CONTINUE handlers, it is the first directly-contained statement after the one that produced the exception.

Handlers absorb exceptions, preventing their reaching the input node and thus causing the transaction to be committed rather than rolled back. A handler can use a RESIGNAL or THROW statement to prevent this.




DECLARE retryCount INTEGER 0;
DECLARE afterCount INTEGER 0;

WHILE retryCount <= 10 DO
  DECLARE EXIT HANDLER FOR SQLSTATE VALUE 'U11222'
  BEGIN
    /* This demonstrates how to pass data to the HANDLER in the SQL 
       special registers */
    SET OutputRoot.XMLNSC.Top.WHILE.mySQLCODE = SQLCODE;
    SET OutputRoot.XMLNSC.Top.WHILE.mySQLSTATE = SQLSTATE;
    SET OutputRoot.XMLNSC.Top.WHILE.mySQLNATIVEERROR = SQLNATIVEERROR;
    SET OutputRoot.XMLNSC.Top.WHILE.mySQLERRORTEXT = SQLERRORTEXT;

    SET retryCount = retryCount + 1;

    /* If we are an EXIT HANDLER, control is now passed to back to the 
       WHILE statement */
  END;

  /* In a real scenario this could be a PROPAGATE statement, and the exception
     could be thrown by a 'downstream' node. In this case the HANDLER would 
     normally cope with a wider range of exception, for example, using LIKE '%' */
  THROW USER EXCEPTION VALUES( -1, 'U11222', 42, 'error text' );
          
  /* This is the next statement executed if it is a CONTINUE HANDLER */
  SET afterCount = afterCount + 1;
          
END WHILE;
        
SET OutputRoot.XMLNSC.Top.WHILE.retryCount = retryCount;
SET OutputRoot.XMLNSC.Top.WHILE.afterCount = afterCount;   

With EXIT (as above) the output is:
<Top>
  <WHILE>
    <mySQLCODE>-1</mySQLCODE>
    <mySQLSTATE>U11222</mySQLSTATE>
    <mySQLNATIVEERROR>42</mySQLNATIVEERROR>
    <mySQLERRORTEXT>error text</mySQLERRORTEXT>
    <retryCount>11</retryCOUNT>
    <afterCount>0</afterCOUNT>
  </WHILE>
</Top>

Changing the HANDLER to be CONTINUE (DECLARE CONTINUE HANDLER FOR SQLSTATE VALUE
'U11222') then the output is:
<Top>
  <WHILE>
    <mySQLCODE>-1</mySQLCODE>
    <mySQLSTATE>U11222</mySQLSTATE>
    <mySQLNATIVEERROR>42</mySQLNATIVEERROR>
    <mySQLERRORTEXT>error text</mySQLERRORTEXT>
    <retryCount>11</retryCOUNT>
    <afterCount>11</afterCOUNT>
  </WHILE>
</Top>



You see the difference in afterCount in the output message.







RESIGNAL statement rethrows the current exception, if one exists.

You can use RESIGNAL only in error handlers.

Typically, RESIGNAL is used when an error handler catches an exception that it cannot handle. The handler uses RESIGNAL to rethrow the original exception so that a handler in higher-level scope has the opportunity to handle it.

Because the handler throws the original exception, rather than a new (and therefore different) one:
The higher-level handler is not affected by the presence of the lower-level handler.
If no higher-level handler is present, you get a full error report in the event log.


RESIGNAL;

Working with Databases in IIB

Can we access multiple databases from a single IIB or WMB compute node?

1. Ensure that all of the databases are of the same type (since they will all be called through the same driver)
2. In the compute node, you can use ESQL like -
    
   Database.{Datasource}.{SchemaName}.{Table} in the FROM clause

Note - sometimes (when single DB interaction, and DSN property is set) we do not need to mention the Datasource name like in below - 

INSERT INTO Database.Table1(Id, Name, Description) VALUES(Id, Name, Description);

        UPDATE Database.Table1 AS PD SET Id=Id, Name=Name, Description=Description

WHERE PD.Id = Id;

----------------------------------------------

Using SELECT statement :


SET PartsTable.Part[] = SELECT
  P.PartNumber,
  P.Description,
  P.Price

 FROM Database.DSN1.Shop.Parts AS P;



Using PASSTHRU function:



SET OutputRoot.XML.Data.SelectResult.Row[] =
  PASSTHRU('SELECT R.* FROM Schema1.Table1 AS R WHERE R.Name = ? OR R.Name = 
           ? ORDER BY Name'
   TO Database.DSN1
   VALUES ('Name1', 'Name4'));


Using Java - JDBC driver :

 Connection conn1 = getJDBCType4Connection("DSN1",JDBC_TransactionType.MB_TRANSACTION_AUTO);
 Connection conn2 = getJDBCType4Connection("DSN2",JDBC_TransactionType.MB_TRANSACTION_AUTO); 



Using PASSTHRU statement -

Used for DDL (administration queries)

PASSTHRU 'DROP TABLE Shop.Customers' TO Database.DSN1;

Java Architecture for XML Binding (JAXB) in IIB

Java Architecture for XML Binding (JAXB)

• enables Java applications to work with a model of message data as Java object classes.
• This Java object model can be accessed and manipulated by using getter and setter methods.

Use 4th option while generating java classes

 Process via Java Architecture for XML Binding(JAXB) class

---------------------------------------------------------------------------------

The user wants to process the message data using Java object classes that were generated by a Java Architecture for XML Binding (JAXB) binding compiler.

The user can access the Java objects containing the unmarshalled input message and update them or create new Java objects to be marshalled into the output message.

Generated code should be adapted to use specific Java object JAXB classes compiled by binding the message schema.

Methods to initialize the JAX context and optionally copy the message headers are also generated.

generates classes for --

1. one class for data manipulation(main class as starting point).
2. one class for each complex type defined in the schema file.
3. one Object Factory class containing Factory methods for each type definition

Generates getters and setters for each element and attribute defined in the schema.


1. class for data manipulation(main class as starting point) :

contains methods -

a) public void onInitialize() throws MbException {
try {
// Initialize JAXB context with com.mss
// Java object classes that were generated by a Java Architecture for XML
// Binding (JAXB) binding compiler 
jaxbContext = JAXBContext.newInstance("com.mss");
} catch (JAXBException e) {
// This exception will cause the deploy of this Java compute node to fail
//  Typical cause is the JAXB package above is not available
throw new MbUserException(this, "onInitialize()", "", "",
e.toString(), null);
}
}

b)  public void evaluate(MbMessageAssembly inAssembly) throws MbException {
MbOutputTerminal out = getOutputTerminal("out");
MbOutputTerminal alt = getOutputTerminal("alternate");

// obtain the input message data
MbMessage inMessage = inAssembly.getMessage();

// create a new empty output message
MbMessage outMessage = new MbMessage();
MbMessageAssembly outAssembly = new MbMessageAssembly(inAssembly,
outMessage);

// optionally copy input message headers to the new output
copyMessageHeaders(inMessage, outMessage);

try {
// unmarshal the input message data from the Broker tree into your Java object classes 
Object inMsgJavaObj = jaxbContext.createUnmarshaller().unmarshal(
inMessage.getDOMDocument());

// ----------------------------------------------------------
// Add user code below to build the new output data by updating
// your Java objects or building new Java objects

// TODO - Replace or modify following which simply copies input to output message
Object outMsgJavaObj = inMsgJavaObj;

// End of user Java object processing
// ----------------------------------------------------------

// TODO set the required Broker domain to for the output message, eg XMLNSC
Document outDocument = outMessage
.createDOMDocument(MbXMLNSC.PARSER_NAME);
// marshal the new or updated output Java object class into the Broker tree
jaxbContext.createMarshaller().marshal(outMsgJavaObj, outDocument);

// The following should only be changed if not propagating message to
// the node's 'out' terminal
out.propagate(outAssembly);
} catch (JAXBException e) {
// Example Exception handling
throw new MbUserException(this, "evaluate()", "", "", e.toString(),
null);
}
}



c)  public void copyMessageHeaders(MbMessage inMessage, MbMessage outMessage)
throws MbException {
MbElement outRoot = outMessage.getRootElement();

// iterate though the headers starting with the first child of the root
// element and stopping before the last child (message body)
MbElement header = inMessage.getRootElement().getFirstChild();
while (header != null && header.getNextSibling() != null) {
// copy the header and add it to the out message
outRoot.addAsLastChild(header.copy());
// move along to next header
header = header.getNextSibling();
}
}




Some points to be aware of  ---

1. JAXBContext

Fetching or selecting or adding attribute value in esql IIB

How to fetch or select attribute value in esql IIB ?

Input xml:
-------------

<parent id="12345">
    <id>ABCDE</id>
</parent>

esql Code :
--------------

SET value = FIELDVALUE(InputRoot.XMLNS.parent.(XML.Element)id);
Result : value is 'ABCDE'

---To fetch attribute value

SET value = FIELDVALUE(InputRoot.XMLNS.parent.(XML.Attr)id);
Result : value is '12345'

How to set attribute value in esql IIB ?

Input XML
--------------

<Invoice>
 <Purchases>
    <Item>
      <Title Category='Computer' Form='Paperback' Edition='2'>The XML Companion</Title>
      <ISBN>0201674866</ISBN>
      <Author>Neil Bradley</Author>
      <Publisher>Addison-Wesley</Publisher>
      <PublishDate>October 1999</PublishDate>
      <UnitPrice>27.95</UnitPrice>
      <Quantity>2</Quantity>
    </Item>
 </Purchases>
</Invoice>


esql Code :
-------------

-- Set the cursor to the first XML.Attribute of the Title.
-- Note the * after (XML.Attribute) meaning any name, because the name might not be known  
DECLARE cursor REFERENCE TO InputRoot.XMLNS.Invoice.Purchases.Item[1].Title.(XML.Attribute)*;  

WHILE LASTMOVE(cursor) DO     
-- Create a field with the same name as the XML.Attribute
-- and set its value to the value of the XML.Attribute                                             
  SET OutputRoot.XML.Data.Attributes.{FIELDNAME(cursor)} = FIELDVALUE(cursor);  
-- Move to the next sibling of the same TYPE to avoid the Title value        
-- which is not an XML.Attribute                                                 
  MOVE cursor NEXTSIBLING REPEAT TYPE; 
END WHILE;

Result :
----------

<Data>
  <Attributes>
    <Category>Computer</Category>
    <Form>Paperback</Form>
    <Edition>2</Edition>
  </Attributes>
</Data>



Note : 

Field type constants with prefix ‘XML.' are for use with the XMLNS and XML parsers only; 

they do not work with the XMLNSC or MRM parsers.

--------------------------------------------------------
                            XMLNS Field Type constant
--------------------------------------------------------
Tag          ---       XML.Element
---------------------------------------------------------
Attribute  ---      XML.Attribute
                           XML.Attr
---------------------------------------------------------

Use of SELECT statement in esql

                      Using ESQL SELECT statement

The SELECT function combines, filters, and transforms complex message and database data.

SELECT function in esql

Input xml:

-------------

<Person>

 <Details>

  <ID>1</ID>

  <Name>Arun</Name>

 </Details>

 <Details>

  <ID>5</ID>

  <Name>Shyam</Name>

 </Details>

 <Details>

  <ID>1</ID>

  <Name>Arun</Name>

 </Details>

 <Details>

  <ID>3</ID>

  <Name>Priyesh</Name>

 </Details>

</Person>

Scenario :

------------

From above input we need name when ID=5.

To get this, the best approach in esql is bellow -

Solution :

------------

--Declare a reference to the root.

DECLARE InRef REFERENCE TO InputRoot.XMLNSC.Person;

--Now assign the value to output using select function 

SET OutputRoot.XMLNSC.Data.Person.Name = THE(select item fieldvalue(r.Name) from InRef.Details[] as r where r.ID = 5);