Reduce statements with SELECT

The following example uses SET statements, and is not recommended:

SET rXMLRoot.ota:parentFolder.ota:childFolder. = ota:field1 = rLocInfo.fielda;
SET rXMLRoot.ota:parentFolder.ota:childFolder. = ota:field2 = rLocInfo.fieldb;
SET rXMLRoot.ota:parentFolder.ota:childFolder. = ota:field3 = rLocInfo.fieldc;

However, the following example, although obviously very simple, has performance benefits that become substantial when you have more fields to map:

SET rXMLRoot.ota:parentFolder.ota:childFolder =
  (SELECT
    rLocInfo.fielda AS ota:field1,
    rLocInfo.fieldb AS ota:field2,
    rLocInfo.fieldc AS ota:field3
   FROM rRQD.LOCATION_INFO AS rLocInfo);

SELECT statements can also be embedded within another SELECT statement to allow nesting of the output structure as shown in the following example:

SET rSoapEnv =
  (SELECT
    nsSoapenv  AS  (XMLNSC.NamespaceDecl)xmlns:"soap,
    nsXsi  AS  (XMLNSC.NamespaceDecl)xmlns:"xsi",
    nsXsd  AS  (XMLNSC.NamespaceDecl)xmlns:"xsd",
    ''  AS  nsSoapenv:Header,
    (SELECT
        nsRnt  AS  (XMLNSC.NamespaceDecl)xmlns:"rnt",
        fs.up  AS  nsSch:pickup,
        fs.off  AS  nsSch:dropoff,
        cDate  AS  nsSch:"date"
    FROM rRent AS fs)  AS  nsSoapenv:Body.nsSch:Rental
  FROM rRental);

To include logic within the SELECT statement, you can add a CASE statement as shown in the following example:

SET rData.Original =
  (SELECT
    'XYZ'  AS  SystemID,
    (CASE
        WHEN rDetails.Error = 'A' THEN '001'
        WHEN rDetails.Error = 'B' THEN '002'
        WHEN rDetails.Error = 'Z' THEN '027'
        ELSE 'N/A'
    END)  AS  ErrorCode
  FROM rEnv.Details AS rDetails);