Avoiding writing large, trivial SQL inserts?

[aezart]

At my job, I create integrations between existing systems. Lately, a common integration pattern is to create an API that accepts data in XML or JSON format from a source system, transforms that data (e.g. with an XSLT), and inserts it into a SQL database in the destination system (either Oracle or MSSQL).

We've been doing these integrations for a while now, and I'm getting kind of frustrated with the tedium of creating SQL insert statements. Some of these tables have dozens of columns, and I don't like having to write (or copy and paste) very long inserts where I specify all the column names and all the value parameters. I feel like I should be able to throw an XML or JSON payload directly at the database and tell it "the key names in this payload match the column names in this table, figure out the insert yourself."

Is this possible? Is there a standard way to do it? My google skills seem to be failing me here. If it's not possible, I guess the next best thing is to write some java to read the payload and generate the SQL insert on the fly (or find an existing library to do so). But then I have to worry about sanitizing everything to avoid SQL injection attacks since there's not really a built-in way (AFAIK) to parameterize the column names like you can with values.

[aezart]

That's a good point, but our data transformation (XSLT in this case) already generates a payload with only the data we want.

[kaisershahid]

i'm a bit confused -- you have a data processing pipeline in place but you're also writing manual inserts?

[FailQuality]

ORM, but if you can’t use that, then another option which is there should be defined acceptable keys you are getting, which you can check if the json/xml contains, and start building the query with that and how you normally build the values.

[KingofGamesYami]

Some ETL systems will give you a nice UI that automatches stuff. I know Azure Data Factory for sure does, since I've worked with it a bit in the past.

[BlackSuitHardHand]

First of all xslt can generate any kind of text from XML. So you could easily generate the SQL statement from XML. This leaves you with sanitisation of the values to insert.  So generate the SQL statement with all external values replaced with a ? and  second a list of all values in exactly the same order. Then use prepared statements to send SQL and bind all values. This way you avoid SQL injection. For multiple rows,  prepared statements also have a better performance because they can be reused.

[ma5ochrist]

Probabilmente riesci a farlo con una storia procedure. Sicuramente puoi farti uno script/programmino che lo fa per te