XML mapping causing issue in modelling in VDB

[sanjay_chaturvedi]

I know how to expose XML file as model in Teiid. Here we are having a SOAP web service exposed and we are left with XML as response, now we need to model it in teiid accordingly. Problem is we are having nested tags inside result xml that I don't know how to map.

Response:

<soap:Envelope xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/">

  <soap:Body>

  <ns3:sensitivityRiskDataResponseWSDL>

       <sensitivityResponse>

       <responseCode>SUCCESS</responseCode>

       <responseMessage>1 rows of sensitivity data in response

       </responseMessage>

       <requestId>33b235b8-b007-4c93-83b8-28ee496493a6</requestId>

       <sensitivityData>

            <aggregationCollection>

                 <aggregationPoint>

                      <key>COLUMN1</key>

                      <value>abc</value>

            </aggregationPoint>

            <aggregationPoint>

                      <key>COLUMN2</key>

                      <value>xyz</value>

             </aggregationPoint>

            </aggregationCollection>

            <amount>757063.0</amount>

       </sensitivityData>

  </sensitivityResponse>

  </ns3:sensitivityRiskDataResponseWSDL>

  </soap:Body>

</soap:Envelope>

As we can get multiple rows of sensitivity data,we should be mapped to sesitivitydata to row like :

Now, under sensitivity data, we have two  problems:

1. We want to show these key tag value as column and its value under that columns:

COLUMN1  |        COLUMN2     |     AMOUNT

=====================================

abc             |            xyz             |       757063.0

2. How can we map, aggregationPoint/Key tag as it can be multiple inside sensitivitydata row, can we define column names dynamically in teiid ?

Please assist.

[rareddy]

Make the "aggregationCollection" as the root element in the XMLTABLE and map the columns from there. See this article Convert XML data into Relational Table data using Teiid how "employee" is being mapped. Another alternative is to use Designer and use its wizard to build the transformation using its UI, then cut and paste the transformation into your Dynamic VDB model.

Ramesh..

[sanjay_chaturvedi]

Thanks for input, but we cannot set aggregationpoint as a root.. because it would seek aggragation point as a row.. right ?

but multi row data wd be like this:

****First row:

  <sensitivityData>

            <aggregationCollection>

                 <aggregationPoint>

                      <key>COLUMN1</key>

                      <value>abc</value>

            </aggregationPoint>

            <aggregationPoint>

                      <key>COLUMN2</key>

                      <value>xyz</value>

             </aggregationPoint>

            </aggregationCollection>

            <amount>757063.0</amount>

       </sensitivityData>

*****Second row

<sensitivityData>

            <aggregationCollection>

                 <aggregationPoint>

                      <key>COLUMN1</key>

                      <value>abc</value>

            </aggregationPoint>

            <aggregationPoint>

                      <key>COLUMN2</key>

                      <value>xyz</value>

             </aggregationPoint>

            </aggregationCollection>

            <amount>757063.0</amount>

       </sensitivityData>

hence we should map like :

EXEC Model_Source.invokeHttp(action => 'GET', request => null, endpoint => 'GetVentureData', stream => 'TRUE', contentType => 'TRUE')) AS f, XMLTABLE('/sensitivityData' PASSING XMLPARSE(DOCUMENT f.result WELLFORMED) COLUMNS KEYDATA string PATH '/aggregationCollection/aggregationPoint/key', VALUEDATA string PATH '/aggregationCollection/aggregationPoint/value', Amount string PATH '/amount')

we have already done this xmltable stuff previously.. only problem is placing column name from tag's value and multi values result within the tag(row). i.e. aggregationpoint/key is coming twice in sensitivitydata...if we take aggragationpoint as root..then will have two rows for two aggragationpoint but where i will display amount data ?

[shawkins]

Are the number of aggregation points fixed under an aggregationCollection?  And do you only want to make a single row from each aggregationCollection?

If that's the case, then you would just use an index to select which aggregation point you want:

EXEC Model_Source.invokeHttp(action => 'GET', request => null, endpoint => 'GetVentureData', stream => 'TRUE', contentType => 'TRUE')) AS f, XMLTABLE('/sensitivityData' PASSING XMLPARSE(DOCUMENT f.result WELLFORMED) COLUMNS KEYDATA string PATH '/aggregationCollection/aggregationPoint[1]/key', VALUEDATA string PATH '/aggregationCollection/aggregationPoint[2]/value', Amount string PATH '/amount')

[sanjay_chaturvedi]

Thanks Steven, But

1. How could we read KEYDATA  i.e. reading "COLUMN1" from     <key>COLUMN1</key>. Showing this COLUMN1 as a column. Basically showing column name dynamically based on the value comes between <key> tag.

2. What if we don't have predefined number of  aggregatPoint's, Can it be handled in teiid.

So all in all, we want to display as many numbers of columns as we get key-value pairs. And could we define column name at run time based on the value of <key> tag.

[shawkins]

> 1. How could we read KEYDATA  i.e. reading "COLUMN1" from     <key>COLUMN1</key>. Showing this COLUMN1 as a column. Basically showing column name dynamically based on the value comes between <key> tag.

No, XMLTABLE currently must project a defined set of columns.

> 2. What if we don't have predefined number of  aggregatPoint's, Can it be handled in teiid.

If you know that you can have up to x, then you could have each row look for x values.  If there are fewer, then you would just get nulls. 

> So all in all, we want to display as many numbers of columns as we get key-value pairs. And could we define column name at run time based on the value of <key> tag.

In general these are not straight-forward as we expect static validation/resolving of metadata - the only workaround (beyond defining a superset of columns) currently there would be to use a procedure and dynamic sql to construct the xmltable but you'd have to process the doc twice.