This is more of a question about a better way to achieve a result than an actual problem.

I have an XML file generated from an external system that I am attempting to load into a SQL Server table. Many of the XML elements have a dash in the name (i.e. 'group-id', 'is-public', etc.). I can't change the XML schema generated by the source system.

In iWay DQS, when writing the XML into a test SQL table, the Jdbc Writer inserts records into the SQL table successfully if the table column names ('group-id') match the XML schema names ('group-id').

However, the actual target SQL Server table has different field names than the XML schema element names so I need to map the XML elements/attributes('group-id') to the SQL table column name ('org_group-id').

I can map the XML attribute fields ('group-id') to the SQL columns ('org_group-id') by enclosing the dashed name square brackets ([group-id]) within the Jdbc Writer step...there are no 'red X' icons in the Workspace UI. But running it generates an INSERT error and shows any/all XML element names with double-quotes and other columns without double quotes (INSERT INTO "group-id", group_name, "is-public", ...) which looks like the dashed columns aren't being interpreted correctly by DQS.

Luckily, I found that I can create Shadow Columns for the dashed elements/attribute names and then create an expression using the XML attribute name (Shadow Column 'group_id_shdw' expression mapped to XML attribute '[group-id]'). Then in the Jdbc step, map the Shadow Column ('group_id_shdw') to the SQL column ('org_group-id') via an expression, and the DQS Plan runs successfully (it inserts all the XML records into the target SQL table). So crisis averted

Question: Is there another way to do this without using Shadow Columns?
(i.e. force DQS to interpret the XML attribute/dashed column names as literals rather than convert them to strings and wrap in double-quotes.)

(I am very new to iWay DQS.)

Many thanks.

This message has been edited. Last edited by: <Kathryn Henning>,

Would it be possible to open a ticket at techsupport.ibi.com including any plans, sample xml, ddl for the tables, etc? Please include a comment to include Craig Sanders in the case.

Sincerely,
Craig Sanders
Integrity Solutions Product Manager - DQS/MDS

Thanks Craig.

A support case has been created and sample files attached (XML sample, SQL Table DDL, DQS Plan).

Update:
The main issue was I was using ODBC to connect to the SQL Server target database.

After reconfiguring the JDBC Writer to use a SQL connection instead of ODBC, the Shadow Columns were no longer required to map the XML elements (with or without dashes) to columns in SQL server that a) did not match the XML element names and b) contained dashes.

Thanks Craig for your help with this!

- Dave