Focal Point Banner
Community Center Education Summit Technical Support User Groups
Let's Get Social!

Facebook Twitter LinkedIn YouTube
Focal Point    Focal Point Forums  Hop To Forum Categories  iWay Software Product Forum on Focal Point    [SOLVED] XML Element name with dashes generates error if not using Shadow Columns
Go
New
Search
Notify
Tools
Reply
  
[SOLVED] XML Element name with dashes generates error if not using Shadow Columns
 Login/Join
 
Member
posted
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 Sweating

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>,


WebFOCUS 8
Windows, All Outputs
 
Posts: 6 | Registered: June 17, 2015Reply With QuoteReport This Post
Member
posted Hide Post
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


all things iWay
 
Posts: 8 | Registered: August 13, 2014Reply With QuoteReport This Post
Member
posted Hide Post
Thanks Craig.

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


WebFOCUS 8
Windows, All Outputs
 
Posts: 6 | Registered: June 17, 2015Reply With QuoteReport This Post
Member
posted Hide Post
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


WebFOCUS 8
Windows, All Outputs
 
Posts: 6 | Registered: June 17, 2015Reply With QuoteReport This Post
  Powered by Social Strata  
 

Focal Point    Focal Point Forums  Hop To Forum Categories  iWay Software Product Forum on Focal Point    [SOLVED] XML Element name with dashes generates error if not using Shadow Columns

Copyright © 1996-2018 Information Builders, leaders in enterprise business intelligence.