How to give back constant if node does not exist in XPATH?

Question

I have a problem, consider the following XML:

<?xml version="1.0" encoding="UTF-16"?>
<APIDATA xmlns="api-com">
<ORDER EngineID="1" OrderID="66" OtherInfo="yes"><INSTSPECIFIER InstID="27" SeqID="17"/>     
</ORDER>
<ORDER EngineID="2" OrderID="67" OtherInfo="yes"><INSTSPECIFIER InstID="28" SeqID="18"/>    
</ORDER>
<ORDER EngineID="3" OrderID="68"><INSTSPECIFIER InstID="29" SeqID="19"/></ORDER>
</APIDATA>

I have to work with SSIS. I would like to get all data to SSIS variables in a for each loop for all Order entries. So far I can get data with a ForeachLoop in control flow in SSIS, with the following:

EnumerationType:  ElementCollection
OuterXPathString: //*[name() = 'ORDER']
InnerElementType: NodeText
InnerXPathString: @*[name() = 'EngineID'] | @*[name() = 'OrderID'] | child::node()/@*[name() = 'InstID'] | child::node()/@*[name() = 'SeqID']

How can I get the OtherInfo data in such a way, that it would always give back something, even if the node does not exist? Example, if the node does not exist, give back "No".

On the discussion side, I need this, because the SSIS mapping use integer indexing on the result set. If the result set can be 4 or 5 long, I get index out of bounds error. This is my idea to circumvent the problem, to always return a fixed length result set.

If this can't be done, the other idea would be to expand the XML with default values. So in case the other question is: Can you show me, how to add default values to an XML with XPATH? Example: Make the mentioned XML to this:

<?xml version="1.0" encoding="UTF-16"?>
<APIDATA xmlns="api-com">
<ORDER EngineID="1" OrderID="66" OtherInfo="yes"><INSTSPECIFIER InstID="27" SeqID="17"/>     
</ORDER>
<ORDER EngineID="2" OrderID="67" OtherInfo="yes"><INSTSPECIFIER InstID="28" SeqID="18"/>    
</ORDER>
<ORDER EngineID="3" OrderID="68" OtherInfo="defaultvalue"><INSTSPECIFIER InstID="29" SeqID="19"/></ORDER>
</APIDATA>

Or is there a more elegant way of solving this in SSIS?

Dimitre Novatchev · Accepted Answer · 2012-09-03T17:13:03.440

How can I get the OtherInfo data in such a way, that it would always give back something, even if the node does not exist? Example, if the node does not exist, give back "No".

Here is one way to produce the value of the OtherInfo attribute if this attribute exists or the string "no" otherwise:

      concat(@OtherInfo,
             substring('no',1 + 2*boolean(@OtherInfo)))

When this Xpath 1.0 expression is evaluated on the following element:

    <ORDER EngineID="1" OrderID="66" OtherInfo="yes">
            <INSTSPECIFIER InstID="27" SeqID="17"/>

the result is:

yes

But when the same expression is evaluated on:

    <ORDER EngineID="3" OrderID="68">
            <INSTSPECIFIER InstID="29" SeqID="19"/>

then the result is:

no

XSLT-based verification:

<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
 <xsl:output method="text"/>
 <xsl:strip-space elements="*"/>

 <xsl:template match="/*/*">
         <xsl:value-of select=
         "concat(@OtherInfo,
                 substring('no',1 + 2*boolean(@OtherInfo)))
         "/>
 </xsl:template>
</xsl:stylesheet>

When this transformation is applied on the provided XML document:

<APIDATA xmlns="api-com">
        <ORDER EngineID="1" OrderID="66" OtherInfo="yes">
                <INSTSPECIFIER InstID="27" SeqID="17"/>
        </ORDER>
        <ORDER EngineID="2" OrderID="67" OtherInfo="yes">
                <INSTSPECIFIER InstID="28" SeqID="18"/>
        </ORDER>
        <ORDER EngineID="3" OrderID="68">
                <INSTSPECIFIER InstID="29" SeqID="19"/>
        </ORDER>
</APIDATA>

The Xpath expression is evaluated against each ORDER element and the result of the evaluation is copied to the output:

yesyesno

Explanation:

The expression:

      concat(@OtherInfo,
             substring('no',1 + 2*boolean(@OtherInfo)))

produces the concatenation of two strings.

In case the context node has an attribute named OtherInfo, then the second string is the empty string and only the first string (the value of the attribute) is produced.

In case the context node has no attribute named OtherInfo, then the first argument of concat() is the empty string and the second argument is evaluated and output.

How is this subexpression evaluated in each of these two cases:

substring('no',1 + 2*boolean(@OtherInfo))

If @OtherInfo exists. Then 2*boolean(@OtherInfo) = 2*true() = 2*1 = 2 Therefore, the expression is equivalent to: substring('no',3) and this is the empty string, because "no" has a length of only 2.
If @OtherInfo doesn't exist. Then 2*boolean(@OtherInfo) = 2*false() = 2*0 = 0. Therefore, the expression is equivalent to: substring('no',1) and this evaluates to the string "no".

The explanation is very helpful, however, `2*boolean(@OtherInfo)` will return an error `Error: Arithmetic operator is not defined for provided arguments`. Instead I used something like this to get this to work `concat(@OtherInfo, substring('no', 1+2*number(boolean(string-length(@OtherInfo)>0))))` — Yadu, Nov 10 '21 at 23:29
@Yadu This answer was provided in the context of the question (XPath 1.0). In XPath 2.0 there is no problem to get the wanted result, for example use: `(@OtherInfo, 'no'[not(@otherInfo)])` — Dimitre Novatchev, Nov 11 '21 at 00:24

How to give back constant if node does not exist in XPATH?

1 Answers1