Usually, API endpoints exposed are either based on SOAP (XML) or REST (JSON) service calls. These are straightforward to handle when it comes to either read or write to the endpoints. You could use our respective JSON, XML, or Web Service (when working with a WSDL-based service) components, to create a data flow based on your requirements. However, there are cases where the responses might be in a hybrid format - That is, for instance, an XML code snippet within a root JSON structure. In such situations, you need a proper strategy for working with the embedded XML snippet. In this blog post, we will show you how this can be achieved effectively. For this, we will use the following components, all of which are part of our SSIS Productivity Pack.
For demonstration purposes, we are using a dummy endpoint, which already has such a response available for us to try and handle using our components. You can click this link to open and view it in your browser.
Setting up the JSON Source component
To get started, we first need to create an HTTP connection manager that connects to the the URL that we just mentioned above (Please note that the URL may have an error when opening in some browsers, such as Firefox, but this should be considered as a false positive). Later in this blog post, we will discuss how to extract data from the service endpoint. As you can see, the document has a root JSON, with XML within it.
{
"XmlDoc": "<?xml version=\"1.0\"?>\n
<DocumentRoot>\n
<data>\n
<Employee_Number>12</Employee_Number>\n
<Person_Number>123</Person_Number>\n
<ESS_Authority_Level>6</ESS_Authority_Level>\n
<Position_Number>12</Position_Number>\n
<Position_Title>Team Lead</Position_Title>\n
<Authority_Level>2</Authority_Level>\n
<Attendance_Type_Code>FULL</Attendance_Type_Code>\n
<Employee_Status>ACTIVE</Employee_Status>\n
<Preferred_Name>John</Preferred_Name>\n
<Surname>Smith</Surname>\n
</data>\n\n
<data>\n
<Employee_Number>25</Employee_Number>\n
<Person_Number>856</Person_Number>\n
<ESS_Authority_Level>6</ESS_Authority_Level>\n
<Position_Number>1438</Position_Number>\n
<Position_Title>Team Manager Information System</Position_Title>\n
<Authority_Level>1</Authority_Level>\n
<Attendance_Type_Code>FULL</Attendance_Type_Code>\n
<Employee_Status>ACTIVE</Employee_Status>\n
<Preferred_Name>Jane</Preferred_Name>\n
<Surname>Austin</Surname>\n
</data>
</DocumentRoot>",
"status": 1
}
Therefore, right after we have set up the HTTP connection manager, we need to use the JSON Source component, to read the JSON document. To achieve so, we head to the Document Designer Page of the JSON Source component and import the design from "web", as shown below.
Once done, you should see that the root object has the "XmlDoc" and "status" parsed out as column values. As we have seen in the JSON document structure above, the "XmlDoc" node would contain the XML document within it. It is important to note that we have set this column to use DT_NTEXT data type so that it can accommodate and process large XML documents, which could happen in a real-life situation.
Now that we have the raw XML document in the SSIS pipeline buffer, we are ready to further extract data from it for the purposes of consuming data in the XML code snippet. To achieve so, we need to set up the transformation part, where the XML will be parsed.
XML transformations
To extract the values from within an XML structure in an SSIS column, we can use the XML Extract component. Here, we would first need to import a design by saving the XML structure in a notepad as an XML file and then import it as shown below. Note that when we save the file, we would unescape the text string.
Once done, we need to make sure that we choose the input field in the Additional Settings page.
The overall data flow should look as shown below.
When we execute the data flow, you can notice that the XmlDoc column has the XML document (which will display as <Long Text> due to the XML datatype), and once passed through the XML Extract component, it will parse out through each node in the XML structure. This will also have the "status" from the JSON source prefixed as "_upstream." in the columns.
Please note that the Dummy Destination Component is just to showcase the data using data viewers. In a real business case, this would be replaced by your actual service destination component.
Conclusion
KingswaySoft components can be easily configured and used to handle complex scenarios within your SSIS data flow, without occurring parsing errors. This is not only useful while dealing with web service responses, but can also be used for parsing such documents from files, or even from column values, depending on how your integration receives such values.
We hope this has helped!