Focal Point Banner


As of December 1, 2020, Focal Point is retired and repurposed as a reference repository. We value the wealth of knowledge that's been shared here over the years. You'll continue to have access to this treasure trove of knowledge, for search purposes only.

Join the TIBCO Community
TIBCO Community is a collaborative space for users to share knowledge and support one another in making the best use of TIBCO products and services. There are several TIBCO WebFOCUS resources in the community.

  • From the Home page, select Predict: WebFOCUS to view articles, questions, and trending articles.
  • Select Products from the top navigation bar, scroll, and then select the TIBCO WebFOCUS product page to view product overview, articles, and discussions.
  • Request access to the private WebFOCUS User Group (login required) to network with fellow members.

Former myibi community members should have received an email on 8/3/22 to activate their user accounts to join the community. Check your Spam folder for the email. Please get in touch with us at community@tibco.com for further assistance. Reference the community FAQ to learn more about the community.


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

Read-Only Read-Only Topic
Go
Search
Notify
Tools
[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, 2015Report 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, 2014Report 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, 2015Report 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, 2015Report This Post
  Powered by Social Strata  

Read-Only Read-Only Topic

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-2020 Information Builders