Is this a "overkill" design to store XML data in a relational database? One point is that these web API REST services can later be used by other applications.
closed as unclear what you're asking by gnat, Blrfl, user22815, scriptin, 8bittree Aug 8 '17 at 18:26
Please clarify your specific problem or add additional details to highlight exactly what you need. As it's currently written, it’s hard to tell exactly what you're asking. See the How to Ask page for help clarifying this question. If this question can be reworded to fit the rules in the help center, please edit the question.
It depends on your data complexity and volume, and the importance of this "data ingest" or ETL (Extract, Transform, Load) service.
For very large datasets (100K or more of inbound XML-encoded objects), which will keep coming in over time, and which are an important part of a business process, you could argue this is "suited to the job."
For most "load the database" jobs, however, it's vast overkill. I've run multi-million record ingest systems with parallel workers yet no splitter service, only a simple queue, and no segmentation of the API type. The number of different kind of entities you have described here seems to go against Occam's Razor, which is often a wise guide for those building software systems.
Beyond the probable over-complexity of the pipeline, several specific concerns:
Do you really want to deserialize from XML only to re-serialize in JSON? Presumably you are not storing that JSON directly in a document-structured DBMS nor verbatim in a relational text or blob field. You're going to have to deserialize the JSON yet again, making for two semantic hops. Now, JSON's a good format in general for Web APIs, but XML is also perfectly cromulent for Web API workers to ingest directly. Not clear why there should be so many preparatory steps before those API workers. Seems likely the preparations greatly increase the opportunity for errors to be introduced.
The idea of splitting XML documents into separate entity types seems risky. Documents that serialize data often share a common context (related entities, implied relationship consistency, or entities that need to be added to the datastore atomically). Splitting so early in your pipeline and recombining later is fraught with opportunities for introducing errors and semantic mismatches. You would need to be extremely careful not to do so if your documents have any relationship complexity to speak of. And by splitting serialized documents so early, you would be ignoring and declining to use the facilities that are built into every last relational DBMS for managing such consistency requirements. While not impossible to imagine a situation where that would be a legitimate choice, it's much rarer than the simpler alternatives.
Update: Here's a diagram of what a simplified process might entail: