XML Stage Convert from Old v6.1 to new V8.1

mandyli · Post by **mandyli** » Mon Sep 12, 2011 2:44 pm

Hi

As per our requirements I have to convert Data stage job from Old version to New Version of Datastage with XML stage.

I have changed and modified XML out stage instead of XML writer stage.

I ran the job and ran fine but when I try to open the XML file from IE or XML Viewer I am getting following error.

"Only one top level element is allowed in an XML document. Error processing resource 'file:///C:/Sears_BI_CS/ETL_SUPPORT/ETL...

<?xml version="1.0" encoding="UTF-8"?><!--Generated by Ascential Software Corporation, DataStage - XMLOutput stage ..."

My old job XML writer stage XML column description as follows

Column Name Description
TPIINFO tpi-info/archive-flag
claim-audit-information/TotalNumberOfRecords
claim-audit-information/NumberOfRecordsinFile

But for NEW V8.1 XML out stage I defined following way
Column Name Description
TPIINFO /claims/tpi-info/archive-flag/text()
/claims/claim-audit-information/TotalNumberOfRecords/text()
/claims/claim-audit-information/NumberOfRecordsinFile/text()

I am not sure this format is correct or not.

Thanks
Man

eostic · Post by **eostic** » Mon Sep 12, 2011 2:52 pm

It looks like you are getting close! ....make one of the lowest level elements (that repeat at the innermost part of the document) a "key" and then be sure to use the "aggregate" option that you will see in the tabs at the output link.

Ernie

mandyli · Post by **mandyli** » Mon Sep 12, 2011 2:58 pm

I am not able to understand and also I am not abl e to view your message.

Thanks
Man

chulett · Post by **chulett** » Mon Sep 12, 2011 4:06 pm

Then it would seem you need to renew your Premium membership.

DIVIHN · Post by **DIVIHN** » Mon Sep 12, 2011 4:21 pm

I can able to see the message.

But still which one is I have to make key

mandyli · Post by **mandyli** » Mon Sep 12, 2011 9:04 pm

Still I am getting same error.

chulett · Post by **chulett** » Mon Sep 12, 2011 9:09 pm

Still? Did you change something?

mandyli · Post by **mandyli** » Tue Sep 13, 2011 5:31 am

I changed key.

eostic · Post by **eostic** » Tue Sep 13, 2011 5:43 am

check the descriptions carefully...they should all be identical in xpath at the beginning..... does your first column have TPIINFO in the beginning as in your entry below? ...every column's description needs to start consistently..... like with /claims/.../etc.

And in addition to the key, perhaps on the NumberOfRecords column (put it on one of the lowest level repeating elements), make sure you check "Aggregate")

Ernie

kommven · Post by **kommven** » Tue Sep 13, 2011 10:20 am

Place a transformer before target XML and generated unique values using @OUTROWNUM derv. to debug grouping issues.

kommven · Post by **kommven** » Tue Sep 13, 2011 10:21 am

Place a transformer before target XML and generated unique values using @OUTROWNUM derv. to debug grouping issues.

mandyli · Post by **mandyli** » Tue Sep 13, 2011 10:46 am

Thanks for all

Old version of Jobs Descripation FROM XML writer stage

columnName Key sqltype dataelement description
------------------------------------------------------------------------------------------------------------------
TPIINFO NO char(5) /tpi-info/archive-flag
CA_TNO no char(20) /claim-audit-information/TotalNumberOfRecords
CA_NOREC no char(20) /claim-audit-information/NumberOfRecordsinFile
SLICE_NUM no char(20 /claim-audit-information/SliceNumber
PK_FLAG no char(5) /claim-audit-information/PackedFlag
GEN_BY no char(30) /claim-audit-information/GeneratedBy
SERVICEORDER YES char(8) XML /service-order/-
SVC_UN_NO YES char(8) /service-order/service-order-data/-
SERVICEMONETARY YES char(2) /service-order/service-order-data/-
SVC_UN_NO1 NO char(7) XML /service-order/service-monetary/-
SERVICEATTEMPTS YES char(2) /service-order/service-attempts/-
SVC_CAL_DT NO VARCHAR(1024) XML /service-order/service-attempts/-
JOBCODES YES CHAR(5) /service-order/job-codes/-
SVC_UN_NO3 NO CHAR(7) XML /service-order/job-codes/-
PARTS YES CHAR(5) /service-order/parts/-
SVC_UN_NO4 NO CHAR(7) XML /service-order/parts/-
MERCHCOMPONENTS YES CHAR(5) /service-order/merchandise-components/-
SVC_UN_NO5 NO CHAR(7) XML /service-order/merchandise-components/-
UDF YES CHAR(10) empty
udf_fld_id_no NO CHAR(10) XML /service-order/udf-component/-

I have modified after i changed from XML output stage in new V8.1 job

columnName Key sqltype dataelement description
------------------------------------------------------------------------------------------------------------------
TPIINFO NO char(5) /claims/tpi-info/archive-flag/text()
CA_TNO no char(20) /claims/claim-audit-information/TotalNumberOfRecords/text()
CA_NOREC no char(20) /claims/claim-audit-information/NumberOfRecordsinFile/text()
SLICE_NUM no char(20 /claims/claim-audit-information/SliceNumber/text()
PK_FLAG no char(5) /claims/claim-audit-information/PackedFlag/text()
GEN_BY no char(30) /claims/claim-audit-information/GeneratedBy/text()
SERVICEORDER YES char(8) XML /claims/service-order/text()
SVC_UN_NO YES char(8) /claims/service-order/service-order-data/text()
SERVICEMONETARY YES char(2) /claims/service-order/service-monetary/text()
SVC_UN_NO1 NO char(7) XML /claims/service-order/service-monetary/text()
SERVICEATTEMPTS YES char(2) /claims/service-order/service-attempts/text()
SVC_CAL_DT NO VARCHAR(1024) XML /claims/service-order/service-attempts/text()
JOBCODES YES CHAR(5) /claims/service-order/job-codes/text()
SVC_UN_NO3 NO CHAR(7) XML /claims/service-order/job-codes/text()
PARTS YES CHAR(5) /claims/service-order/parts/text()
SVC_UN_NO4 NO CHAR(7) XML /claims/service-order/parts/text()
MERCHCOMPONENTS YES CHAR(5) /claims/service-order/merchandise-components/text()
SVC_UN_NO5 NO CHAR(7) XML /claims/service-order/merchandise-components/text()
UDF YES CHAR(10) empty
udf_fld_id_no NO CHAR(10) XML /claims/service-order/udf-component/text()

when I see out from old sever I can view the XML file in notepad and IE but when I open new XML file I am getting error.

eostic · Post by **eostic** » Tue Sep 13, 2011 3:13 pm

Hi Mandyli...

I appreciate your efforts to use your job and do the conversion from xmlWriter to xmlOutput "as is"...but it won't work that simply...there are too many dependencies.

You are close, yet there are still various things that I suspect may be causing difficulties. It's hard to tell, but editing the Descriptions may not be the best way to go --- and while Data Element XML behaves just about the same way in both stage types, it's too risky to play with until you first have a solid document being created and "then" can get fancy.

You need to have a whole new link going into the xmlOutput Stage. One that starts perfectly clean and empty (add a transformer, then a new link to the xmlOutput Stage).

Then import your metadata. There is a whole new metadata importer for the xmlOutput Stage that you are using. Import...Table Defs...xml Table Defs....

Open a "good" (what you expect to get) xml document in there. Expand its entire tree.... click on the lowest level elements and attributes you find there that you want.

...basically, start over, coding this part of the job new and from scratch. Once you learn how the xmlOutput Stage works in a simple case, you will be able to apply changes more dynamically.

Ernie

mandyli · Post by **mandyli** » Tue Sep 13, 2011 3:18 pm

Thanks for your information.

I will try with new job. let see.

Thanks
Man

eostic · Post by **eostic** » Tue Sep 13, 2011 4:01 pm

hmm. Had a chance to look more closely at your metadata. Looking closely, I see that you have, under "claims", "claim-audit-information" and also "service-order" information.

In the final document that you "want", do each of those nodes repeat? ...or perhaps is one static (only appears once for each claim) while the other one repeats?

If they both repeat, then this is a more difficult challenge and you may want to find something simpler as a starter.....btw, are there other xmlWriter Stages upstream in this Job, or Transformers that independently build xml strings?

Ernie