KingswaySoft offers XML Source/Extract components that can easily parse out an XML structure to a tabular format, either from an API endpoint response, a file, or a column. The components work on the basis of a pre-imported document designer, which would recognize XML structures in the source data, and parse them out accordingly. There are cases in which the XML that we receive from an API response/file is a mix of regular XML characters, as well as escaped characters. In such cases, it is important that they are properly handled and transformed, to then parse through them successfully. The problem lies in the fact that the document designer is static, and we would need an additional XML extract component to recognize the escaped XML design and parse that node out successfully. In this blog post, we will be looking at an example of this situation. Below you can see the components you will need, all available as part of our SSIS Productivity Pack:
Design
Let's take an XML body that is a mix of escaped and un-escaped characters, as shown below. This is a preview of the response body using our HTTP Connection manager.
The first step would be to have the XML design, the original one, imported into the document designer of the XML Source component. Open the component, and on the document designer page, choose the import from XML(web) option. As you can see, the "Result" node is detected as a "ntext" column, as it has the escaped portion.
The next thing to do is use the Premium Derived Column component in the data flow, and un-escape the XML node, Result. This can be achieved by using the function as shown below. Then, provide a name to the derived column; this is what you would be using in the XML Extract component later to parse the values out.
Now, further down, you would need the XML Extract component. But before that, we need a design for it. That is, in order to have the document designer for the XML extraction from the Result node, its un-escaped form needs to be provided. To achieve this, either the Premium Derived column function, "WriteTextContent( «file_path», «text» )" can be used to write the un-escaped node to a file path.
Another way to do this would be by using an online tool to un-escape the XML. This can be copied and pasted to a file, and saved with an XML extension.
This file can be imported to the XML Extract component, by using the "XML (Local File)" option, which will have the child nodes in the Result node detected as expected. Based on this, you could modify the node types, have them as columns or outputs, and have them parsed out. Then, once you have the design, click on the "Additional Settings: tab on the 'Document Designer" page, and choose the new Derived Column that you had set up in the previous step. This would make sure that you have the right input coming in.
The final design flow would look as shown below:
Another case worth mentioning is: if you have other parent nodes in the original XML Source component that need to be joined with the "Result" node that is handled in the XML extract - This can be easily achieved by using the out of the box Sort and Merge join components, or by using a Premium Lookup component. The design may look similar to the one shown below. We have more details in the following blog post: Joining Parent and Child Records Efficiently.
Conclusion
By following the above process, varying XML designs, such as the one discussed, can easily be handled. XML Source and XML Extract work in a similar pattern except for the fact that XML Extract is a transformation component that requires an input.
We hope this has helped!