Issue in Reading xml data through DS PX

[Aquilis]

Hello all,
we have xml data file which we need to convert into relational format and load into Database. we need to have multiple repeating elements needs to be considered and in the same time i am unable to build xpath expressions for the same. I have gone through the Kim's XML best practices and unable to relate that into my requirement or i may be missing somewhere. please find the below mentioned xml sample data and the xpath i built accordingly.

xpath expression:

Code: Select all

/subscription/@communication
/subscription/@registration
/subscription/snapshot/cv/@ctry
/subscription/snapshot/cv/@communication
/subscription/snapshot/cv/@registration
/subscription/snapshot/cv/cvId/text()
/subscription/snapshot/cv/ts/text()
/subscription/snapshot/cv/point/seqNum/text()
/subscription/snapshot/cv/point/term/text()
/subscription/snapshot/cv/point/ts/text()
/subscription/snapshot/cv/point/yield/text()
/subscription/snapshot/cv/point/ticket/text()
/subscription/snapshot/cv/point/x2Unique/text()
/subscription/snapshot/cv/point/xyz/text()
/subscription/snapshot/cv/point/desc/text()

sample data:

Code: Select all

 <ns:subscription registration="EUR" communication="CORP">
 <ns:snapshot>
 <ns:cv communication="CORP" registration="EUR" ctry="EU">
  <ns:cvId>f961</ns:cvId> 
  <ns:ts>20081117T10:01:22.00005:00</ns:ts> 
 <ns:point>
  <ns:seqNum>1</ns:seqNum> 
  <ns:term>90</ns:term> 
  <ns:ts>20081117T10:01:22.00005:00</ns:ts> 
  <ns:yield>2.6537</ns:yield> 
  <ns:ticket>C9613M</ns:ticket> 
  <ns:x2Unique>NI7696297</ns:x2Unique> 
  <ns:xyz>BFV</ns:xyz> 
  <ns:desc>3Month</ns:desc> 
  </ns:point>
 <ns:point>
  <ns:seqNum>2</ns:seqNum> 
  <ns:term>180</ns:term> 
  <ns:ts>20081117T10:01:22.00005:00</ns:ts> 
  <ns:yield>2.6163</ns:yield> 
  <ns:ticket>C9616M</ns:ticket> 
  <ns:x2Unique>NI7696299</ns:x2Unique> 
  <ns:xyz>BFV</ns:xyz> 
  <ns:desc>6Month</ns:desc> 
  </ns:point>
 <ns:point>
  <ns:seqNum>3</ns:seqNum> 
  <ns:term>365</ns:term> 
  <ns:ts>20081117T10:01:22.00005:00</ns:ts> 
  <ns:yield>2.5545</ns:yield> 
  <ns:ticket>C9611Y</ns:ticket> 
  <ns:x2Unique>NI7696301</ns:x2Unique> 
  <ns:xyz>BFV</ns:xyz> 
  <ns:desc>1Year</ns:desc> 
  </ns:point>
  

any ideas suggested will be appreciable.

[chulett]

Post an example of the output you are seeing.

[Aquilis]

Code: Select all

xml simple format :

<?xml version="1.0" encoding="UTF-8"?>
<env:Envelope xmlns:env="http://schemvalues.zzzz.org/llll/envelope/">
<env:Body>
<ns:gevalue7nvaluepshovalue7OneDvaluey xmlns:ns="http://xyz.com/dlws/value8clevaluer/20070930">
<ns:hevalueder>
<ns:webservice>value8 clevaluer Web Service</ns:webservice>
<ns:opervaluetion>Gevalue7nvaluepshotOneDvaluey</ns:opervaluetion>
<ns:timestvaluemp>2008-11-17 16:49:36.000</ns:timestvaluemp>
<ns:dlwsRequestId>6019e8e3-dee7-403b-9376-8036926641fc</ns:dlwsRequestId>
</ns:hevalueder>
<ns:returnCode>
<ns:externvaluelCode>0</ns:externvaluelCode>
<ns:internvaluelCode>0</ns:internvaluelCode>
<ns:description>Success</ns:description>
</ns:returnCode>
 <ns:subscription registration="EUR" communication="CORP"> 
 <ns:snapshot> 
 <ns:cv communication="CORP" registration="EUR" ctry="EU"> 
  <ns:cvId>f961</ns:cvId> 
  <ns:ts>20081117T10:01:22.00005:00</ns:ts> 
 <ns:point> 
  <ns:seqNum>1</ns:seqNum> 
  <ns:term>90</ns:term> 
  <ns:ts>20081117T10:01:22.00005:00</ns:ts> 
  <ns:yield>2.6537</ns:yield> 
  <ns:ticket>C9613M</ns:ticket> 
  <ns:x2Unique>NI7696297</ns:x2Unique> 
  <ns:xyz>BFV</ns:xyz> 
  <ns:desc>3Month</ns:desc> 
  </ns:point> 
 <ns:point> 
  <ns:seqNum>2</ns:seqNum> 
  <ns:term>180</ns:term> 
  <ns:ts>20081117T10:01:22.00005:00</ns:ts> 
  <ns:yield>2.6163</ns:yield> 
  <ns:ticket>C9616M</ns:ticket> 
  <ns:x2Unique>NI7696299</ns:x2Unique> 
  <ns:xyz>BFV</ns:xyz> 
  <ns:desc>6Month</ns:desc> 
  </ns:point> 
 <ns:point> 
  <ns:seqNum>3</ns:seqNum> 
  <ns:term>365</ns:term> 
  <ns:ts>20081117T10:01:22.00005:00</ns:ts> 
  <ns:yield>2.5545</ns:yield> 
  <ns:ticket>C9611Y</ns:ticket> 
  <ns:x2Unique>NI7696301</ns:x2Unique> 
  <ns:xyz>BFV</ns:xyz> 
  <ns:desc>1Year</ns:desc> 
  </ns:point> 

Code: Select all

output expected is like:
i used a pipe operator to distinguish between each columns, which really are exepcted out .
communication   | registration   | ctry    | cvId  | ts  | seqNum | term  | yield  | ticket | x2Unique | xyz  | desc

Code: Select all

my current xpath definitions are like:
/subscription/snapshot/cv/@communication 
/subscription/snapshot/cv/@registration 
/subscription/snapshot/cv/cvId/text() 
/subscription/snapshot/cv/ts/text() 
/subscription/snapshot/cv/point/seqNum/text() 
/subscription/snapshot/cv/point/term/text() 
/subscription/snapshot/cv/point/ts/text() 
/subscription/snapshot/cv/point/yield/text() 
/subscription/snapshot/cv/point/ticket/text() 
/subscription/snapshot/cv/point/x2Unique/text() 
/subscription/snapshot/cv/point/xyz/text() 
/subscription/snapshot/cv/point/desc/text() 

currently problem i am facing is ,Job executes successfully but there will be no output i get as such. it shows zero records loaded.
please suggest.

[Aquilis]

Still I am not able to get any alternative in my design.now i am facing following error:

Code: Select all

XML_Input_0,0: Error: Unable to resolve prefix 'env'.
pattern = '/env:Envelope/env:Body/ns:getSnapshotsIntraDayResponse/ns:subscription/ns:snapshot/ns:curve'(Unknown URI, 12, 147)
Remaining tokens: ('/')

when i remove 'env' element from the x path, i will encounter with:

Code: Select all

XML_Input_0,0: Error: Unable to resolve prefix 'ns'.
pattern = '/ns:getSnapshotsIntraDayResponse/ns:subscription/ns:snapshot/ns:curve'(Unknown URI, 12, 147)
Remaining tokens: ('/')

when I remove both the elements 'env' and 'ns' then I will not get any output (zero records).

[chulett]

... and yet the topic is marked as Resolved. Is it?