How to return a 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 need to work with SSIS. I would like to get all the data for SSIS variables in for each loop for all Order records. So far I can get data from ForeachLoop in the 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 OtherInfo data so that it always returns something, even if node does not exist? Example, if node does not exist, return "No".

On the discussion side, I need this because SSIS mapping uses integer indexing in the result set. If the result set can be 4 or 5 long, I get the index outside of the error. It is my idea to work around the problem to always return a fixed-length result set.

If this is not possible, another idea is to extend the XML with default values. So, in case another question is: Can you show me how to add default values ​​to XML using XPATH? Example: Make the mentioned XML as follows:

 <?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 to resolve this issue in SSIS?

Answer 1

How can I get OtherInfo data so that it always returns something, even if node does not exist? Example, if node does not exist, return "No".

Here is one way to get 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 evaluates to the following element:

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

result:

 yes 

But when the same expression is evaluated by:

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

then the result:

 no 

XSLT Based Validation :

 <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 conversion is applied to 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 calculation is copied to the output :

 yesyesno 

Explanation

Expression:

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

creates a concatenation of two strings.

In case there is an attribute with the name OtherInfo in the node context , then the second line is an empty line and only the first line is created (attribute value).

If the node context does not have an attribute named OtherInfo , then the first argument to concat() is an empty string, and the second argument is evaluated and displayed.

How this subexpression is 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 an empty string because "no" is only 2 in length.

• If @OtherInfo does not 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" .